SQLite

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)

Comment:

sqlite3 -- DB-API 2.0 interface changes the access method a bit:

Usually your SQL operations will need to use values from Python variables. You shouldn't assemble your query using Python's string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack.

Instead, use the DB-API's parameter substitution. Put "?" as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor's execute() method. (Other database modules may use a different placeholder, such as "%s" or ":1".) For example:

# Never do this -- insecure!
symbol = 'IBM'
c.execute("... where symbol = '%s'" % symbol)

# Do this instead
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)

# Larger example
for t in (('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ):
    c.execute('insert into stocks values (?,?,?,?,?)', t)

Posted by Peter Dilley (2007-07-12)

Note that my sample doesn't use string interpolation; sqlite2 uses "%s" for binding instead of "?" /F