Hi, I'm Harlin and welcome to my blog. I write about Python, Alfresco and other cheesy comestibles.

Python - How to Work with SQLite3

(see http://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html)

What we need:

  • Python 3.6.3
  • sqlite3 module (should be included in Python 3.6.3)
  • sqlitebrowser

On Ubuntu to get SQLite Browser you can install it from the command line:

# sudo apt install sqlitebrowser

If you're on Mac OS X or Windows, you can get the installer here.

A lot of tutorials out there will show you how to create tables through Python but to be honest with you, I have never wrote an app that deals with databases where I have to write tables as part of my Python code. So, we'll use the SQLite Browser to create our tables.

Let's open up the SQLite Browser and design a database with a table. If you're using Ubuntu, you can start it by issuing:

# sqlitebrowser

![SQLite3](/static/img/screenshots/sqlite3_images/1.png =200x)

At the top left hand corner, click on New Database. This will open a file dialog.

SQLite3

I've called my database file db.sqlite3. Go ahead and save it. Next, you should see a table dialog.

Have a look at the screenshot below and create your user table just as I've done:

SQLite3

When you've done that, click on the OK button. This will save the database structure to an sqlite3 file called db.sqlite3.

This will leave us with sqlitebrowser showing the database structure we've just created. Click on the Browse Data tab, set the table to user and the New Record Button.

SQLite3

Notice that we've created a primary key called id. This will allow us to make sure each record will have its own unique identifier. For id, we will check the NOT NULL, PRIMARY KEY, AUTOINCREMENT and UNIQUE boxes. Let's go over quickly what each of these mean:

  • NOT NULL - If a record is added, this means that this column value cannot be null or empty. There must be some kind of data for it. Note that I chose integer for its type. This means that the id will be a number.

  • PRIMARY KEY - A primary key id a column or a group of columns that uniquely identifies a row in the table.

  • AUTOINCREMENT - This means that we do not need to add the id value ourselves in our code and that as each record is added, this number will be autoincremented (usually by one).

  • UNIQUE - We won't get into what indexes are but know that the value for this column must be unique compared to the same column for other records or rows in the table.

The first_name and last_name only need to be set as NOT NULL. These do not have to be unique and we're not going to use them as a primary key.

The email column is set up as NOT NULL but we also set up the UNIQUE constraint for it so that each user we're creating must have its own unique email address.

Now, let's enter a few records. Leave the id column empty as SQLite should autocreate and autoincrement the id number when we create the fields.

We will create 3 users and query them in a few minutes with a Python script.

SQLite3

Once you've done these, go ahead and on the menu for the SQLiteBrowser, click on File > Write Changes or you can click on the button next to Open Database called Write Changes. This will save our schema (structure of the database's tables) and our data so we can make use of it in a moment. Feel free to keep the SQLite Browser open if you like for now.

Now, let's create a script that will get the results of our records in our database:

#!/usr/bin/env python

# Importing the sqlite3 module
import sqlite3

# Create a connection to sqlite3 database.
connection = sqlite3.connect('db.sqlite3')

# Create a cursor object for querying.
cursor = connection.cursor()

# Execute an SQL select statement - should give us all
# records from the user table.
cursor.execute("SELECT * FROM `USER`")

# Get the results of the query using fetchall()
results = cursor.fetchall()

# Print the results
print(results)

# Not necessary but good practice.
cursor.close()

# Do this. It's a good idea.
connection.close()

When you run this script you should get the following output:

$ ./demo1.py 
[(1, 'Tom', 'Brown', 'tom.brown@localhost'), (2, 'Bill', 'Blue', 'bill.blue@localhost'), (3, 'Sydney', 'Black', 'sydney.black@localhost')]

As you can see, it will return the list of rows we just created.

Now, let's go through each line of our script.

Of course, we'll need to import the sqlite3 module so that we can use it. Next, we'll create a connection handler (much like you would if you were going to open a file). Once we have the connection handler, we'll get a cursor object so we can run queries.

The cursor.fetchall() will allow us to get all the results of our query. Note that fetchall() will return a list of items (even if there's only one row, it will still come back as a list object).

You can see the list with the print(results) line. With SQLite3, you normally would not need to use cursor.close() but I've found it's always a good idea to do this especially if you are doing batch updates or inserts. It's also a habit I picked up from Java. With Java, if you're not careful, leaving cursors open can cause some cursor overloading with RDBMS systems. So, in my opinion, I believe it's a good habit to get into.

Lastly, we close our connection. Again, with sqlite3, you may not need this with this particular script but I recommend you always use it.

Now, you'll notice with the print(results) we only get a list. What if we wanted to iterate through each record based on column? I would propose that we change our code to this:

...

# Get the results of the query using fetchall()
results = cursor.fetchall()

# Print the results
for row in results:
    user_id, first_name, last_name, email = row
    print(f'User ID: {user_id}')
    print(f'First Name: {first_name}')
    print(f'Last Name: {last_name}')
    print(f'Email Address: {email}')
    print('\n')

# Not necessary but good practice.
cursor.close()

# Do this. It's a good idea.
connection.close()

If you do that, we should see this more readable output:

$ ./demo1.py 
User ID: 1
First Name: Tom
Last Name: Brown
Email Address: tom.brown@localhost


User ID: 2
First Name: Bill
Last Name: Blue
Email Address: bill.blue@localhost


User ID: 3
First Name: Sydney
Last Name: Black
Email Address: sydney.black@localhost

Armed with this knowledge, you should be able to run most any kind of query you want to even dynamic ones. What if we wanted to only get the results of one user called Sydney Black? We'll need to change our query and since we're expecting only one result, we can use fetchone() instead of fetchall(). Also, I'm going to show you how to handle variable substitutions for strings in the query that is done in a safe matter. Here's an example that will do that for us:

...

# Execute an SQL select statement - should give us all
# records from the user table.
cursor.execute("SELECT * FROM `USER` WHERE LAST_NAME=?", ('Black',))

# Get the results of the query using fetchall()
record = cursor.fetchone()

# Print the results
user_id, first_name, last_name, email = record
print(f'User ID: {user_id}')
print(f'First Name: {first_name}')
print(f'Last Name: {last_name}')
print(f'Email Address: {email}')

# Not necessary but good practice.
cursor.close()

# Do this. It's a good idea.
connection.close()

And our output should be:

$ ./demo1.py 
User ID: 3
First Name: Sydney
Last Name: Black
Email Address: sydney.black@localhost

We changed results to be a record because with fetchone() we're only expecting one record to be returned. What happens if there is more than one record that gets returned with fetchone()? That will cause an error letting us know more than one record is being returned from our query.

As I mentioned earlier, you can use most any query with SQLite3 and Python. If you do not have a good grasp of SQL, I would suggest having a look here to learn more.

Any Comments, Always Welcome!