SQLite

import sqlite3

# Connection object represents the database.
cnxn = sqlite3.connect('foo.db')

curs = cnxn.cursor()

curs.execute('create table ...')
curs.execute(...)
curs.executemany(...)

cnxn.commit() # Saves changes to db.

curs.execute('select ...')
a_row = curs.fetchone()
all_rows = curs.fetchall()

# Or, instead of fetchone() or fetchall(),
for row in curs.execute('select ...'):
    print(row)

curs.close()
cnxn.close()

Calling cnxn.cursor() just gets you a cursor for that connection. Each time you call cnxn.cursor() you get another (different) cursor to the same db connection.

If you want to skip cursors, you can just do cnxn.execute(...) or cnxn.executemany(...) and it will make an implicit temporary cursor to use.

Transactions

By default, the sqlite3 module implicitly opens transactions before a insert/update/delete, and also commits transactions implicitly before any create/alter/drop.

See also the Official SQLite docs.

Last-inserted Row

You can always follow a

curs.execute("insert into...")

with

curs.lastrowid

to get the id of that last-inserted row.

Note that even though you do need to run cnxn.commit() to commit your changes to the db, you can still get the lastrowid without it. Just know that if you fail to commit your db change(s), then that lastrowid will be meaningless.

In-Memory Database

You can create a sqlite db in memory:

cnxn = sqlite3.connect(':memory:')
curs = cnxn.cursor()

Null String

You can set how sqlite3 displays null using:

.nullvalue '¤'

or whatever character you like. Note that empty string is not the same as null.

If you do an insert but omit a column, or if you insert None, that col’s value will be null.

Python Types

When you pull values from a sqlite db, Python chooses types for the values based on the storage class of the db value (which is usually but not necessarily the type affinity of the col from whence it came). So, you get:

And, of course, if you pull a null value from sqlite, it shows up in Python as None.

Parameter Substitution

Use the DB-API’s parameter substitution:

curs.execute('select ... where foo = ?', (some_val,))

Yes, that 2nd arg must always be a tuple.