SQLite Basics

The SQLite library is a light-weight embedded SQL engine, with a nice DB API compliant Python binding, originally developed by Michael Owens.

A newer version, called sqlite3, was added to Python’s standard library in Python 2.5.

import sqlite3

db = sqlite3.connect("database.db")

c = db.cursor()
c.execute("create table mytable (timestamp, size, file)")

for file in os.listdir("."):
    c.execute(
        "insert into mytable values (?, ?, ?)",
        os.path.getmtime(file), os.path.getsize(file), file
    )

c.execute("select file, size from mytable where size > 1000000")

for file, size in c.fetchall():
    print file, size

“fatal python error: deallocating None”

There’s a bug in pysqlite 0.5.0 (and possibly also in other releases) that may cause your application to halt, with a fatal python error: deallocating None message. This only happens if you’re connecting to databases repeatedly over the lifetime of your program, but if you do, it may take hours or days before the bug reveals itself.

The problem is that connect loses one reference to None for every call, so if you connect enough times, Python will run out of references to the None singleton object. When the None destructor is run, the Python interpreter prints an error message and shuts down.

Here’s a patch:

--- src/pysqlite-0.5.0/_sqlite.c
+++ src/pysqlite-0.5.0/_sqlite.c
@@ -755,7 +755,7 @@

     if (logfile == Py_None)
     {
-        Py_DECREF(logfile);
+        /* Py_DECREF(logfile); */

         Py_INCREF(Py_None);
         return Py_None;

If you cannot rebuild, you can modify the sqlite/main.py module, changing the set_command_logfile call around line 474 to look like this:

        if command_logfile is not None:
            self.db.set_command_logfile(command_logfile)