Python SQLite Tutorial

Python SQLiteDid you know that you can use Python for database operations? Versions of Python since 2.5 have the SQLite3 module built in to the standard install. SQLite allows you to have a database in a single file with no database server needed. It is a fast, compact system that is becoming widely used in popular applications. This tutorial will get you started on your way to managing data with Python and SQLite. Get a start with Python by taking this course on programming.

Suppose that you see that Udemy courses are such a great resource, and you will be taking many of them. You would like to keep track of which ones you have taken, and record some information about them. This can all be done using Python with the SQLite3 module. The first step is to fire up the Python shell and import the module:

>>> import sqlite3

Next, we can check on the version of SQLite3, which binds the Python language to SQLite, and the SQLite database library itself:

>>> sqlite3.version

‘2.6.0’

>>> sqlite3.sqlite_version

‘3.7.12’

Our database will be a table called Courses. We will have fields for the course name, the course author, the course cost, and the date that the course was completed. We have records for 2 courses so far. It would look like this in a spreadsheet table:

Course                                           Author                   Cost     Date_Completed

Algebra I                                       Math Fortress     10        2014-01-14

Persuasive Presentations      Sharon Kroes      43        2014-02-07

Learn some basics of databases with this course on database design.

Now we will create the database:

>>> connection = sqlite3.connect(‘mycourses.db’)

The sqlite3.connect() method connects to the named database if it already exists. It will create a database with that name if it does not already exist. This is done in the current working directory. You can check where this is by importing the os module and calling the os.getcwd() method. Learn more about the Python os module in this article.

>>> import os

>>> os.getcwd()

(the directory on your computer will be returned here)

Next we will create the table and set up the fields:

>>> cursor = connection.cursor()

>>> sql = “””CREATE TABLE Courses (id INTEGER PRIMARY KEY, AUTOINCREMENT,

course TEXT,

author TEXT,

Cost INTEGER,

Date TEXT)

“””

>>> cursor.execute(sql)

<sqlite3.Cursor object at 0x0000000003EB5D50>

In this code the first thing was to set up a cursor object that interacts with SQLite. The name “cursor” is what we chose for clarity in this tutorial. You might want to name it something shorter since you will be typing it a lot.

Next came the actual SQL (Structured Query Language) command to create the table. The SQL commands in SQLite are virtually the same as in other forms of SQL. Get a quick start in SQL with this 1 hour course. We named the table and set up the fields. We added an id field so that we can give each record a unique identifier. The SQL command was set up in an sql object that is then executed with the cursor.execute() method. The system responds with the memory location of the cursor object.

This does not give much reassurance that the command actually worked – other than the lack of an actual error message. If you want to check on this, one option is to use the SQLite Manager add-on for Firefox. You can use it to browse the database and see that the fields are there.

From within Python, we can do the following:

>>> cursor.execute(‘select * from Courses’)

>>> print (cursor.description)

((‘id’, None, None, None, None, None, None), (‘course’, None, None, None, None, None, None), (‘author’, None, None, None, None, None, None), (‘Cost’, None, None, None, None, None, None), (‘Date’, None, None, None, None, None, None))

Here, we queried the database for all the data and printed a description. The first item in each list returned is the field name, so we can see that they got in there.

Now let’s add the records:

>>> sql = “INSERT INTO Courses (course, author, Cost, Date) VALUES (‘Algebra I’,’Math Fortress’,10,’2014-01-14′)”

>>> cursor.execute(sql)

>>> connection.commit()

>>> sql = “INSERT INTO Courses (course, author, Cost, Date) VALUES (‘Persuasive Presentations’,’Sharon Kroes’,43,’2014-02-07′)”

>>> cursor.execute(sql)

>>> connection.commit()

We set up an sql object with the INSERT INTO command for each record. We specified all of the fields and then provided values for them. The sql object was then executed and committed for each record. You might notice that we did not insert a value for the id field. That is not needed because we declared it to auto-increment whe we set up the table.

We can query and print the data to check on how well we did:
>>> cursor.execute(‘select * from Courses’)

>>> for records in cursor.fetchall():

print(records)

(1, ‘Algebra I’, ‘Math Fortress’, 10, ‘2014-01-14’)

(2, ‘Persuasive Presentations’, ‘Sharon Kroes’, 43, ‘2014-02-07’)

Let’s do a couple of simple edits. Suppose that you check back in your diary and find that you really finished the second course on February 8, so you need to fix the entry. You do this with the SQL UPDATE command, inserted into an sql object, executed, and committed:

>>> sql = “UPDATE Courses SET Date=’2014-02-08′ WHERE course=’Persuasive Presentations'”

>>> cursor.execute(sql)

>>> connection.commit()

Let’s print the data again to see the change:

>>> cursor.execute(‘select * from Courses’)

>>> for records in cursor.fetchall():

print(records)

(1, ‘Algebra I’, ‘Math Fortress’, 10, ‘2014-01-14’)

(2, ‘Persuasive Presentations’, ‘Sharon Kroes’, 43, ‘2014-02-08’)

The WHERE clause was very important here. It specified to change the date value for the record where the course was Persuasive Presentations. If the WHERE clause was omitted, that value would have been inserted into all of the records.

Now suppose that you check your diary again, and you find that you meant to take the Algebra I course, but you never got around to it. So you need to take that record out. That will be done with the SQL DELETE command. The WHERE clause is critical here as well. Without it, all of the data will be deleted!

>>> sql = “DELETE FROM Courses WHERE id=1”

>>> cursor.execute(sql)

>>> connection.commit()

>>> cursor.execute(‘select * from Courses’)

>>> for records in cursor.fetchall():

print(records)

(2, ‘Persuasive Presentations’, ‘Sharon Kroes’, 43, ‘2014-02-08’)

This should get you started with using the Python SQLite module. You can find more SQL commands with this course on SQL databases.