Python and SQLite Part 1

You’re coding like mad creating the next major photo sharing app for pizza lovers when it hits you, if only I could save all this amazing pizza goodness, I’d really have something. You’ve heard about this SQLite business and it sounds like what you need, and hey, Android uses it so it must be good. However, you don’t know where to begin in order to realize your cheesy dreams. Fear not, we’ve got you covered.

Prerequisites

To follow along with the examples, we need to have Python version 2.7.x and SQLite 3.7.13 or higher.

From the shell, we get the Python version by passing ‘–version’ as an argument to python, which in our case is 2.7.5. Running sqlite3 starts an interactive session with sqlite and displays the version, 3.7.13. As long as your versions are fairly close to these, you should be set.

If you don’t have SQLite installed, you can grab it here: http://sqlite.org/download.html

If you’re on OSX, like me, you can install SQLite via home brew: brew install sqlite

Creating A Database

There are two ways to create a new database. We can use the sqlite3 command line or programmatically create one from our Python code. Let’s try both.
From the command line:

The parameter, 'my_data.db' , is the database name. If the file is present, sqlite just opens it, otherwise it is created. To exit the interactive session, type ‘.exit’.

sqlite> .exit

Programmatically:

We create a connection to the database with sqlite3.connect('database name') , if the file exists it will open it, otherwise it is created. Now that we have a connection to the database, let’s execute our first query.

Here we get the cursor object from our connection and execute a query to retrieve the SQLite version and display it. Don’t worry if cursors and fetching mean nothing to you at this point.

Tables And Data

Let’s start by creating a new table and inserting some records.

The above code creates a table, Pizza_Users, and adds three records to the table. The final call to commit() saves the changes.

Here the table is created with four columns, id, first_name, last_name and pizza_karma.

Here we insert three new records into our Pizza_Users table.

Finally, we commit, or save, our changes to the database. Without this line, all changes we’ve made would be lost. We can quickly verify the changes via the sqlite3 command line tool.

Now let’s select data we’ve saved to our database.

In the above code we select all records in the Pizza_Users table and print each row to the console. In order to retrieve data after executing the SELECT statement, we need to call  fetchall()  in order to get a list of matching rows (actually a tuple of tuples). The output should look like the following:

Now let’s modify one of our records.

In the above, we call update on the pizza_users table and set our pizza_karma value equal to 650 where the id is equal to 1. Again, we call commit to save our changes. Validating via the command line we see:

Finally, we can delete records from our database.

Here we remove a record from our table with an id of 2. Our table now looks like:

Finally, once you’re finished working with a database, you should close your connection.

One thing to note, make sure you’ve called  commit()  before closing your database connection, otherwise you will lose your changes.

That’s it for the Python SQlite tutorial. Next time we’ll cover parameterized queries and importing/exporting data.

Don’t forget to check out the full code example on github:
https://github.com/VulgarPython/BasicPythonSQLite

Chris Guthrie

Security Architect. Coder. Coffee Connoisseur.

One thought on “Python and SQLite Part 1

Leave a Reply