Python Notes and Examples


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.


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


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:

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 sqlite, 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:

SQLite to Python
integer int
real float
text str

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:

That 2nd arg must be a tuple.


sqlite3 is developed externally under the name “pysqlite” at