Tag Archiv für Python

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

Database Access with Python I – SQLite

Installation SQLite

If you’re using Python 2.5 or higher the SQLite database is automatically delivered with it. If you want to work with SQLite you should install the SQLite3 command line tools as well. You can download them on the SQLite Download site: https://www.sqlite.org/download.html

Search for the SQLite3 Tools. They are available for several operating systems. The following explanation is based on Windows systems:

  1. Download the zip file
  2. Extract it to a location you prefer
  3. Run SQLite3.exe

The command line then just looks like this:

BLOG_0034 - PIC01 - SQLite3 CL

Now you can type in SQL commands. If you are not familiar with SQL just check out my BLOG, my online training or my books. Or you could start with the free excerpt of my book which explains a lot of basic stuff.

Otherwise you can create an example table now. We type in the following commands:

CREATE TABLE customer (Customer_no NUMERIC,
Firstname VARCHAR(20),
Lastname VARCHAR(20)
);
 
INSERT INTO customer VALUES(1, ‘Thomas’, ‘Smith’);
 
SELECT * FROM customer;
 

 

That just creates a table and inserts one row into it. The last command shows all rows and all columns from that table. The result should look like this:

BLOG_0034 - PIC02 - SQLite Table Creation

This tool is basically used to create tables and so on. The database only exists in memory right now. If you want to save it to disk permanently you have to save it. Type in:

.save test.db

 

Now the data is stored in the database test.db. You could of course use different names. Another way you could achieve that result is when you start SQLite3 and passing a name of a database. If the database already exists it’s opened. If it doesn’t exist then the database is created. Not in memory but on disk. Then you don’t have to save it.

 

Access database in Python

Now we have our database and now we want to access it in our Python program. Depending on the database we are using we need the corresponding python database module. For our SQLite database it is already available in Python (as from version 2.5 on). So we don’t have to do anything now. I’ll explain now how to access the SQLite database in Python. I will explain in a following article how to access other databases (Oracle, MySQL, MS SQL, Postgres). Now let’s start.

Our first example shows how to insert data into a table:

import sqlite3
con=sqlite3.connect(“C:/Daten/sqlite-tools-win32-x86-3210000/test.db”)
 
con.execute(“INSERT INTO customer VALUES(2, ‘Bill’, ‘Meyers’)”)
con.execute(“INSERT INTO customer VALUES(3, ‘Anne’, ‘Meyers’)”)
 
con.commit()
 
con.close()

 

If we run our select in SQLite3 command line we get the following result:

BLOG_0034 - PIC03 - SQLite Result After Insert

As you can see we have three rows in the table now. Let’s walk through the code line by line. First we have to import the SQLite Connector. We do that with the import line.

Now we are connecting to our database. We use connect(…) for that and have to pass the path and name of our database. You have to change it to your path and name. We get a connection object in con.

The next step is to insert two lines into the table. We want to run the SQL INSERT INTO statement as we did before in the SQLite3 command line. To run SQL commands you can use the method execute(…). We do that twice and in both cases we passed the INSERT INTO statement as a parameter.

After we inserted the two rows we have to commit() the data. That is needed in order to save it permanently to the database. If you leave that the rows are removed when the connection is closed.

Closing the connection is then the last step and you do that with close().

 

Now we want to query data in Python. That’s our second example:

import sqlite3
con=sqlite3.connect(“C:/Daten/sqlite-tools-win32-x86-3210000/test.db”)
 
print (“\nRead data with FOR .. IN ..”)
cur=con.execute(“SELECT * FROM customer”)
 
for x in cur:
print(x)
 
print (“\nRead data with WHILE and .fetchone()”)
cur=con.execute(“SELECT * FROM customer”)
 
while True:
y=cur.fetchone()
if y==None:
break
print (y[0], ‘-’, y[1])
 
print (“\nRead data with .fetchall()”)
cur=con.execute(“SELECT * FROM customer”)
x=cur.fetchall()
print (x)
 
print (“\nRead data with .fetchmany(..)”)
cur=con.execute(“SELECT * FROM customer”)
 
x=cur.fetchmany(2)
print (x)
con.close()
 

 

The results looks like that:

BLOG_0034 - PIC04 - Example 2 results

In this example we firstly connect to the database again. Then we use .execute() to run a SELECT statement. Now I provide four different ways to fetch and show the data:

  1. Iteration with for … in ….
  2. While loop with .fetchone()
  3. .fetchall()
  4. .fetchmany(…)

The iteration is probably the most elegant way to fetch the data. We are just iterating through the cursor picking one row with each iteration. Then we just print it.

Working with the while loop is a little more complicated. We define a indefinite loop. Then we fetch exactly one row with the method .fetchone(). Then we check if we really fetched another row. If not x is empty and that’s why we check for None. If so we break the loop. Otherwise we print the first two columns. You can access the columns with the [..] operator.

With the method .fetchall() we fetch all rows and put them into an array of tuples. You can directly print that.

The last option is .fetchmany(numRows). In comparision with .fetchall() you fetch only the number of rows you are passing to this method. In our case the first 2 rows. Again they are put into an array of tuples.

 

I hope you find this article helpful and interesting. I will continue this with an article on other databases. A lot of the stuff we did today is the same with other databases. But the connection is always a little bit different depending on the database.

If you want to learn more on SQL, databases and Python you can read my BLOG or my books. Or just start with the free excerpt of my book. 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_0034_Python_Access_Databases_Examples.zip