Let's talk to a SQLite database with Python

As I write this, it’s the weekend. My wonderful wife brought home some amazing Jet Fuel XBold Dark Roast Coffee that is sure to keep me wired for the next several hours. Now what should I do? Let’s explore Python a little more by using it to talk to a database.

We’re a long way from my bread and butter. My goto technologies for database interaction are .NET, C# and Entity Framework CodeFirst with SQL Server 2005/2008. These technologies lend themselves to enterprise level web and application development. They require a fair amount of software licenses and infrastructure to get rolling. In contrast, SQLite (as you may have guessed) is a very lightweight implementation of a relational database. It is completely self contained. There is no SQLite server or configuration requirements. You just make a database and boom! You’re in business. Because of this light footprint and its ease of use, it is a very common database to use in mobile applications.

First, let’s create a SQLite database file. I’m going to call my database “Blog.sqlite”. You can do this from the command line with Python, or if you prefer a graphical interface, you can use a snappy little Firefox add-on called SQLite Manager. It runs inside Firefox and offers a simple interface for interacting with SQLite databases. And, since it runs in Firefox, it works on any platform that runs Firefox (E.G. MacOS, Windows, Linux).

Once we have our database created, then we need to make a table. Once again, we can do this using straight SQL and Python from the command line, but I prefer to use the SQLite Manager add-on in Firefox.

Note that the Id field is an auto incremented integer value and a primary key.

Now, let’s dive into code. What will this application do? Well, since this project is in the spirit of exploration, the application we’re going to build won’t be very useful. Instead it will have an academic focus. Let’s just perform basic CRUD operations (Create, Read, Update, Delete) and then exit. We won’t worry about user interaction or GUI or web. This is going to just be a console app.

This should go without saying, but this won’t work without installing Python first. I’m using Python3.2.3. Don’t worry about downloading SQLite. There is no SQLite server, remember? If you have Python, you can use SQLite.

In the same folder as the Blog.sqlite database, create your Python file using your favorite text editor. I called mine sqliteConnect.py. Once you have your file, you can run it from the command line by first browsing to the folder where you saved the file, then just enter the following command:

$ python3 sqliteConnect.py

That will run the Python script. Though, nothing will happen until we put some code in there.

Connecting to Your SQLite Database from Python

To perform CRUD functionality, first we need to establish a connection with the database, and then create what’s known as a cursor which will allow us to execute commands.

import sqlite3

conn = sqlite3.connect('Blog.sqlite')
cursor = conn.cursor()

Creating a Record

Now that we have our open connection and our cursor, we can insert data into the Posts table.

cursor.execute('insert into Posts (Headline, Body) values (?, ?)', ('This is my Headline', 'This is the body of my blog post.'))
firstPostId = cursor.lastrowid

cursor.execute('insert into Posts (Headline, Body) values (?, ?)', ('Jet Fuel XBold Coffee', 'Jet Fuel XBold Dark Roast Coffee will make you code like a madman.'))
conn.commit()

Here, we call cursor.execute() and pass it two parameters. A SQL command with placeholders for the data (the question marks), and a sequence containing the data to be inserted. Since we are hard-coding the insert, we could have just placed our data directly into the SQL. But in the real world, we could be inserting user generated input. In that case, we would want to use this parameterized technique to keep the SQL query safe and keep our database safe from SQL Injection style hacking attempts.

We have explicitly inserted the data for Headline and Body while ignoring the value for the Id field. This is purposeful because when we created the Posts table, we defined Id to be an auto-incrementing integer value. This is good because it saves us from having to generate a unique identifier ourselves. However, we are going to be editing this same record later in our application. In order to be able to get the same record, we need to know the auto generated Id value.

The last inserted row id is stored in cursor.lastrowid. So, when our insert is complete, if we want to grab the new Id value, we just need to assign cursor.lastrowid to our firstPostId variable.

It’s worth noting here, calling cursor.execute() will execute the command on the database. But, we need to commit any changes by calling conn.commit(). If we don’t call conn.commit() before the connection is closed, then our changes will be undone as if they never happened.

Reading Our New Records

Now that we have some data inserted into the database table, let’s read it out.

cursor.execute('select * from Posts')

print('Current records: ')
for row in cursor.fetchall():
    print('\t', row)

We are using the same cursor.execute() method to run a select query on the table. Then we can print each row by using cursor.fetchall() in a for loop.

Update That First Record

Now, let’s make some changes to the record we inserted first. This is when the firstPostId comes in handy.

cursor.execute('update Posts set Headline=?, Body=? where Id=?', ('This is my NEW Headline', 'This is the NEW body of my blog post.', firstPostId))
conn.commit()

print('Records after update: ')
cursor.execute('select * from Posts')
for row in cursor.fetchall():
    print('\t', row)

Now when we print our read results, we can see that the first record has been updated with new data.

Delete Everything

You don’t have to delete everything, but that’s what I’m going to do.

cursor.execute('delete from Posts')
conn.commit()

print('Records after delete: ')
cursor.execute('select * from Posts')
for row in cursor.fetchall():
    print('\t', row)

Now, when we print our query results, we see nothing.

Wrapping Up, Closing Down

cursor.close()
conn.close()

Since we’re done with our cursor and connection, we can close them down.

Complete Code

import sqlite3

conn = sqlite3.connect('Blog.sqlite')
cursor = conn.cursor()

# (C)reate a new blog post
cursor.execute('insert into Posts (Headline, Body) values (?, ?)', ('This is my Headline', 'This is the body of my blog post.'))
firstPostId = cursor.lastrowid

cursor.execute('insert into Posts (Headline, Body) values (?, ?)', ('Jet Fuel XBold Coffee', 'Jet Fuel XBold Dark Roast Coffee will make you code like a madman.'))
conn.commit()

# (R)ead our new posts
cursor.execute('select * from Posts')

print('Current records: ')
for row in cursor.fetchall():
    print('\t', row)

# (U)pdate the first post
cursor.execute('update Posts set Headline=?, Body=? where Id=?', ('This is my NEW Headline', 'This is the NEW body of my blog post.', firstPostId))
conn.commit()

print('Records after update: ')
cursor.execute('select * from Posts')
for row in cursor.fetchall():
    print('\t', row)

# (D)elete all the records
cursor.execute('delete from Posts')
conn.commit()

print('Records after delete: ')
cursor.execute('select * from Posts')
for row in cursor.fetchall():
    print('\t', row)

cursor.close()
conn.close()