Database Access with Python II – MySQL & PostgreSQL

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:

BLOG_0035_PIC01_Example_MySQL

It’s absolutely the same process as with SQLite:

  1. Connect()
  2. Creation of a cursor
  3. Execution of your SQL
  4. Fetching the data & output or whatever
  5. 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.

 

Download

BLOG_0035_Python_Access_Databases II_Examples.zip

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.