Fabian's Business Intelligence BLOG

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:

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:

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:

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:

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