Tutorial on Using Sqlite3 Database with Python

Posted in Tutorials

Python comes with SQLite3 database.   It is self-contained and doesn’t require any additional installation.  Here is an example tutorial of how to use it.

import sqlite3
db = sqlite3.connect('names.db')
db.execute('create table names (firstname text, age int)')
db.execute('insert into names (firstname, age) values (?, ?)', ('Joe', 14))
db.execute('insert into names (firstname, age) values (?, ?)', ('Jane', 41))
db.execute('insert into names (firstname, age) values (?, ?)', ('John', 11))
db.commit()
cursor = db.execute('select * from names')
for row in cursor:
    print(row)

First you have to import the sqlite3 module.  Then calling connect with a database name will connect to it.  If the database does not exist, it will create it, giving you a file names.db on your file system.  Note that no username and password was provided.  That means you have to think about security as mentioned here.

Then simply call SQL statements as needed.  Here we called various insert SQL statement with a couple of placeholders (?) for values to insert.  The second parameter to the execute statement is a tuple that contains the values for these placeholders.

Because SQLite3 is transactional, the statements are queued up until you call .commit().

Next, we query from the database also using execute().  This returns an iterator (which we put into the cursor object) in which we can iterate through all the rows of the result set.

This will print out tuples …

(‘Joe’, 14)
(‘Jane’, 41)
(‘John’, 11)

You can use a row_factory like this …

db = sqlite3.connect('names.db')
db.row_factory = sqlite3.Row
db.execute('create table names (firstname text, age int)')
db.execute('insert into names (firstname, age) values (?, ?)', ('Joe', 14))
db.execute('insert into names (firstname, age) values (?, ?)', ('Jane', 41))
db.execute('insert into names (firstname, age) values (?, ?)', ('John', 11))
db.commit()
cursor = db.execute('select * from names')
for row in cursor:
    print(dict(row))
    print(row['firstname'], row['age'])

To re-run, you need to write SQL to drop the table before creating it again.   With row_factory as shown, the row in cursor is now objects.  When we put these into dictionary form, we can access them like …

row[‘firstname’]

row[‘age’]

and so on.

 

---