Archiv für Januar 31, 2018

Database Access with Python II – MySQL & PostgreSQL


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

The result looks like this:


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
cur.execute(„SELECT * FROM TEST“)

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.




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:

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.execute(„INSERT INTO customer VALUES(2, ‚Bill‘, ‚Meyers‘)“)
con.execute(„INSERT INTO customer VALUES(3, ‚Anne‘, ‚Meyers‘)“)


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
print („\nRead data with FOR .. IN ..“)
cur=con.execute(„SELECT * FROM customer“)
for x in cur:
print („\nRead data with WHILE and .fetchone()“)
cur=con.execute(„SELECT * FROM customer“)
while True:
if y==None:
print (y[0], ‚-‚, y[1])
print („\nRead data with .fetchall()“)
cur=con.execute(„SELECT * FROM customer“)
print (x)
print („\nRead data with .fetchmany(..)“)
cur=con.execute(„SELECT * FROM customer“)
print (x)


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.



5 Tips to simply work with SQL Developer

Today I just want to give you 6 tips on how to use the Oracle SQL Deveoper more efficient. If you dive into the functions and menus you’ll find a lot of small functions which can really ease your developer life and makes coding much fast.


1) Show/Hide line numeration

For testing and error correction purpose you sometimes want to know the line numbers. You can easily show and hide them by just right clicking on the vertical white line on the very left hand side of the editor (better take a look at the screenshot). You then just click on the second entry of the context menu to switch it on or off.

BLOG_0033 - PIC01 - Line numeration


2) The Model Tab

A useful new function from version 4.1 on is the model tab. This is available on all tables and shows the selected tables with all tables that have a foreign key relation with that table.

BLOG_0033 - PIC02 - Model Tab


3) Resolve a SELECT * to all its columns

Sometimes it’s helpful if you could easily resolve a SELECT * to the column names of a table (also works for multiple tables). All you have to do is hover with the cursor on the * and wait for the popup. You then see all the columns that will be used instead of *. If you click on the popup the * is replaced by these columns.

BLOG_0033 - PIC03 - Expand Select


4) Multi cursor editing

If you want to save time when copy and pasting the same stuff at several places you could use the feature multi cursor editing. You just press Ctrl+Shift and then you click where you need cursors. If you multiple tables starting with TBL_ and you want to remove that you would just place the cursor at the first TBL_ and then press Ctrl+Shift and place new cursors before all TBL_ Then you can just press delete to remove it and the action is done at all curors.


5) commenting on several lines at the same time

You can use comments to just deactivate single lines. You would normally do that with double minus and this comments the whole line. If you want to do that on multiple lines you can do it by marking the lines, then right click and then click comment rows on/off in the context menu. This automatically adds a double minus to each line. The standard shortcut is Ctrl + /


I hope you find these tips helpful. If you are interested in regular updates of my blog just subscribe my newsletter. All Subscribers receive my Analytical SQL Guide which helps you to use analytical SQL in your queries.