Hi,
this is the second part of my article series dealing with the database access with Python. In the first part I showed how to generally connect and query databases. I explained all the stuff based on the SQLite database which is automatically delivered with each Python installation above version 2.5. In this part I want to show how to work with MySQL and PostgreSQL databases.
In both cases you have to install some kind of database driver first. After that you can directly connect and query the databases.
How to access MySQL databases?
First step is to install the database library MySQLdb. You can easily do this with the tool pip. Just type the following code in the linux or windows command line:
| pip install MySQLdb |
Normally this should download and install what is needed in order to use MySQLdb.
Now you only have to import this module and then you can work with a MySQL database the same way you did with SQLite. Check out the following code:
| import MySQLdb db = MySQLdb.connect(host=“localhost“, # your host, usually localhost user=“root“, # your username passwd=“test“, # your password db=“test“) # name of the data base # Creation of a cursor object in order to run SQLs cur = db.cursor() # Your SQL statements cur.execute(„SELECT * FROM TEST“) # Output result=cur.fetchone() print(“) print(‚Results:‘) print(‚===========‘) print(result[0]) db.close() |
The result looks like this:
It’s absolutely the same process as with SQLite:
- Connect()
- Creation of a cursor
- Execution of your SQL
- Fetching the data & output or whatever
- Close()
For your database you can just place your SQL in the execute() function.
We will see the same process with a PostgreSQL database.
How to access PostgreSQL databases?
If you want to access PostgreSQL database you need the module psycopg2. Again you can install it with pip (pip = “pip installs packages”):
| pip install psycopg2 |
This again downloads and installs what is needed for access to PostgreSQL databases. Now you can connect and query the database:
| import psycopg2 con=psycopg2.connect(host=“localhost“, database=“test“, user=“postgres“, password=“postgres“) cur=con.cursor() cur.execute(„SELECT * FROM TEST“) result=cur.fetchone() print(“) print(‚Results:‘) print(‚===========‘) print(result[0]) con.close() |
The result looks the same as for MySQL. You can just place your SQL in the execute() function.
I hope you find this article helpful and interesting. I will continue this with an article on Oracle & SQL Server.
If you want to learn more on SQL, databases and Python you can read my BLOG or my books. Furthermore I am offering classroom trainings (only in Germany) and also online training (in English). Just check it out!
Sign up my Newsletter and get informed on all my new articles. You receive an eBook “Analytical SQL” for free.
