pysqlite usage guide

(c) 2004-2005 David Rushby
(c) 2005-2007 Gerhard Häring

Last updated for pysqlite 2.4.0

Table Of Contents

0. Introduction

This Usage Guide is not a tutorial on Python, SQL, or SQLite; rather, it is a topical presentation of pysqlite's feature set, with example code to demonstrate basic usage patterns. This guide is meant to be consumed in conjunction with the Python Database API Specification and the SQLite documentation.

It was originally written by David Rushby for kinterbasdb. He kindly gave the permission to adapt it for pysqlite.

1. Python Database API 2.0 Compliance

1.1 Incompatibilities

  • No type information in cursor.description

    cursor.description has a tuple with the fields (name, type_code, display_size, internal_size, precision, scale, null_ok) for each column that a query returns. The DB-API spec requires that at least name and type_code are filled, but at the time cursor.description is built, pysqlite cannot determine any types, yet. So, the only field of cursor.description that pysqlite fills is name. All other fields are set to None.

  • No type objects

    Consequently, there are also no type objects STRING, BINARY, NUMBER, DATETIME, ROWID at module-level. They would be useless.

1.2 Unsupported Optional Features

  • Cursor class

    • nextset method

      This method is not implemented because the database engine does not support opening multiple result sets simultaneously with a single cursor.

1.3 Nominally Supported Optional Features

  • Cursor class

    • arraysize attribute

      As required by the spec, the value of this attribute is observed with respect to the fetchmany method. However, changing the value of this attribute does not make any difference in fetch efficiency because the database engine only supports fetching a single row at a time.

    • setinputsizes method

      Although this method is present, it does nothing, as allowed by the spec.

    • setoutputsize method

      Although this method is present, it does nothing, as allowed by the spec.

1.4 Extensions and Caveats

pysqlite offers a large feature set beyond the minimal requirements of the Python DB API. Most of these extensions are documented in the section of this document entitled Native Database Engine Features and Extensions Beyond the Python DB API.

  • connect function

    The parameter database refers to the database file for the SQLite database. It's a normal filesystem path and you can use absolute or relative path names.

    The connect function supports the following optional keyword arguments in addition to those required by the spec:

    • timeout - When a database is accessed by multiple connections, and

      one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).

      Example:
      sqlite.connect(database="mydb", timeout=10.0)
    • isolation_level - pysqlite will by default open transactions with a "BEGIN" statement, when it encounters a DML statement like INSERT/UPDATE/DELETE/REPLACE. Some users don't want pysqlite to implicitly open transactions for them - they want an autocommit mode. Other users want pysqlite to open different kinds of transactions, like with "BEGIN IMMEDIATE". See 5. Controlling Transactions for a more detailed explanation.

      Note that you can also switch to a different isolation level by setting the isolation_level property of connections.

      Example:
      # Turn on autocommit mode
      con = sqlite.connect("mydb", isolation_level=None)

      # Set isolation_level to "IMMEDIATE"
      con.isolation_level = "IMMEDIATE"
    • detect_types - SQLite natively supports only the types TEXT, INTEGER, FLOAT, BLOB and NULL. If you want to use other types, like you have to add support for them yourself. The detect_types parameter and using custom converters registered with the module-level register_converter function allow you to easily do that.

      detect_types defaults to 0 (i. e. off, no type detection), you can set it to any combination of PARSE_DECLTYPES and PARSE_COLNAMES to turn type detection on.

      Consult the section 4. SQLite and Python types of this manual for details.

      • sqlite.PARSE_DECLTYPES - This makes pysqlite parse the declared type for each column it returns. It will parse out the first word of the declared type, i. e. for "integer primary key", it will parse out "integer". Then for that column, it will look into pysqlite's converters dictionary and use the converter function registered for that type there.

      • sqlite.PARSE_COLNAMES - This makes pysqlite parse the column name for each column it returns. It will look for a string formed [mytype] in there, and then decide that 'mytype' is the type of the column. It will try to find an entry of 'mytype' in the converters dictionary and then use the converter function found there to return the value. The column name found in cursor.description is only the first word of the column name, i. e. if you use something like 'as "x [datetime]"' in your SQL, then pysqlite will parse out everything until the first blank for the column name: the column name would simply be "x".

        The following example uses the column name timestamp, which is already registered by default in the converters dictionary with an appropriate converter!

        Example:

        from pysqlite2 import dbapi2 as sqlite
        import datetime

        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
        cur = con.cursor()
        cur.execute('select ? as "x [timestamp]"', (datetime.datetime.now(),))
        dt = cur.fetchone()[0]
        print dt, type(dt)
    • check_same_thread - SQLite connections/cursors can only safely be used in the same thread they were created in. pysqlite checks for this each time it would do a call to the SQLite engine. If you are confident that you are ensuring safety otherwise, you can disable that checks by setting check_same_thread to False.

    • factory - By default, pysqlite uses the Connection class for the connect call. You can, however, subclass the Connection class and make .connect() use your class instead by providing your class for the factory parameter.

      Example:

      from pysqlite2 import dbapi2 as sqlite

      class CountCursorsConnection(sqlite.Connection):
          
      def __init__(self, *args, **kwargs):
              
      sqlite.Connection.__init__(self, *args, **kwargs)
              
      self.numcursors = 0

          
      def cursor(self, *args, **kwargs):
              
      self.numcursors += 1
              
      return sqlite.Connection.cursor(self, *args, **kwargs)

      con = sqlite.connect(":memory:", factory=CountCursorsConnection)
      cur1 = con.cursor()
      cur2 = con.cursor()
      print con.numcursors
    • cached_statements - pysqlite internally uses a statement cache to avoid SQL parsing overhead. If you want to explicitly set the number of statements that are cached for the connection, you can set this parameter. The currently implemented default is to cache 100 statements.



  • register_converter function - register_converter(typename, callable) registers a callable to convert a bytestring from the database into a custom Python type. The converter will be invoked for all database values that are of the type typename. Confer the parameter detect_types of the connect method for how the type detection works.

  • register_adapter function - register_adapter(type, callable) registers a callable to convert the custom Python type into one of SQLite's supported types. The callable accepts as single parameter the Python value, and must return a value of the following types: int, long, float, str (UTF-8 encoded), unicode or buffer.

  • enable_callback_tracebacks function - enable_callback_tracebacks(flag) Can be used to enable displaying tracebacks of exceptions in user-defined functions, aggregates and other callbacks being printed to stderr. methods should never raise any exception. This feature is off by default.

  • Connection class

    • isolation_level attribute (read-write)

      Get or set the current isolation level: None for autocommit mode or one of "DEFERRED", "IMMEDIATE" or "EXLUSIVE". See 5. Controlling Transactions for a more detailed explanation.

    • cursor method - The cursor method accepts a single optional parameter: a custom cursor class extending pysqlite's Cursor class that you can adapt to your needs. Note that it is required that your custom cursor class extends pysqlite's Cursor class.

    • execute method - Nonstandard - this works as a shortcut for not having to create a cursor object and is implemented like this:

      class Connection:
        
      def execute(self, *args):
          
      cur = self.cursor()
          
      cur.execute(*args)
          
      return cur
    • executemany method - Nonstandard - The same shortcut as the nonstandard execute method.

    • executesript method - Nonstandard - The same shortcut as the nonstandard execute method.

    • row_factory attribute (read-write)

      You can change this attribute to a callable that accepts the cursor and the original row as tuple and will return the real result row. This way, you can implement more advanced ways of returning results, like ones that can also access columns by name.

      Example:

      from pysqlite2 import dbapi2 as sqlite

      def dict_factory(cursor, row):
          
      d = {}
          
      for idx, col in enumerate(cursor.description):
              
      d[col[0]] = row[idx]
          
      return d

      con = sqlite.connect(":memory:")
      con.row_factory = dict_factory
      cur = con.cursor()
      cur.execute("select 1 as a")
      print cur.fetchone()["a"]

      If the standard tuple types don't suffice for you, and you want name-based access to columns, you should consider setting row_factory to the highly-optimized pysqlite2.dbapi2.Row type. It provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. Much better than your own custom dictionary-based approach or even a db_row based solution.

    • text_factory attribute (read-write)

      Using this attribute you can control what objects pysqlite returns for the TEXT data type. By default, this attribute is set to unicode and pysqlite will return Unicode objects for TEXT. If you want to return bytestrings instead, you can set it to str.

      For efficiency reasons, there's also a way to return Unicode objects only for non-ASCII data, and bytestrings otherwise. To activate it, set this attribute to pysqlite2.dbapi2.OptimizedUnicode.

      You can also set it to any other callable that accepts a single bytestring parameter and returns the result object.

      See the following example code for illustration:

      from pysqlite2 import dbapi2 as sqlite

      con = sqlite.connect(":memory:")
      cur = con.cursor()

      # Create the table
      con.execute("create table person(lastname, firstname)")

      AUSTRIA = u"\xd6sterreich"

      # by default, rows are returned as Unicode
      cur.execute("select ?", (AUSTRIA,))
      row = cur.fetchone()
      assert row[0] == AUSTRIA

      # but we can make pysqlite always return bytestrings ...
      con.text_factory = str
      cur.execute("select ?", (AUSTRIA,))
      row = cur.fetchone()
      assert type(row[0]) == str
      # the bytestrings will be encoded in UTF-8, unless you stored garbage in the
      # database ...
      assert row[0] == AUSTRIA.encode("utf-8")

      # we can also implement a custom text_factory ...
      # here we implement one that will ignore Unicode characters that cannot be
      # decoded from UTF-8
      con.text_factory = lambda x: unicode(x, "utf-8", "ignore")
      cur.execute("select ?", ("this is latin1 and would normally create errors" + u"\xe4\xf6\xfc".encode("latin1"),))
      row = cur.fetchone()
      assert type(row[0]) == unicode

      # pysqlite offers a builtin optimized text_factory that will return bytestring
      # objects, if the data is in ASCII only, and otherwise return unicode objects
      con.text_factory = sqlite.OptimizedUnicode
      cur.execute("select ?", (AUSTRIA,))
      row = cur.fetchone()
      assert type(row[0]) == unicode

      cur.execute("select ?", ("Germany",))
      row = cur.fetchone()
      assert type(row[0]) == str
    • total_changes attribute (read-only)

      Returns the total number of database rows that have be modified, inserted, or deleted since the database connection was opened.


  • Cursor class

    • execute method

      pysqlite uses paramstyle = "qmark". That means if you use parametrized statements, you use the question mark as placeholder.

      This is a basic example showing the use of question marks as placeholders and a parameter tuple:

      from pysqlite2 import dbapi2 as sqlite

      con = sqlite.connect("mydb")

      cur = con.cursor()

      who = "Yeltsin"
      age = 72

      cur.execute("select name_last, age from people where name_last=? and age=?", (who, age))
      print cur.fetchone()

      pysqlite also supports paramstyle = "named". That means you can use named placeholders in the format ":name", i. e. a colon followed by the parameter name. As parameters, you then supply a mapping instead of a sequence. In the simplest case, a dictionary instead of a tuple.

      from pysqlite2 import dbapi2 as sqlite

      con = sqlite.connect("mydb")

      cur = con.cursor()

      who = "Yeltsin"
      age = 72

      cur.execute("select name_last, age from people where name_last=:who and age=:age",
          
      {"who": who, "age": age})
      print cur.fetchone()

      The following example shows a shortcut that you can often use when using named parameters. It exploits the fact that locals() is a dictionary, too. So you can also use it as parameter for execute:

      from pysqlite2 import dbapi2 as sqlite

      con = sqlite.connect("mydb")

      cur = con.cursor()

      who = "Yeltsin"
      age = 72

      cur.execute("select name_last, age from people where name_last=:who and age=:age",
          
      locals())
      print cur.fetchone()

      execute will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a Warning. Use executescript if want to execute multiple SQL statements with one call.

    • executemany method

      The DB-API specifies the executemany method like this:

      .executemany(operation, seq_of_parameters)

      pysqlite, however, extends executemany so it can be used more efficiently for inserting bulk data. The second parameter to executemany can be a sequence of parameters, but it can also be an iterator returning parameters.

      Example:

      from pysqlite2 import dbapi2 as sqlite

      class IterChars:
          
      def __init__(self):
              
      self.count = ord('a')

          
      def __iter__(self):
              
      return self

          
      def next(self):
              
      if self.count > ord('z'):
                  
      raise StopIteration
              
      self.count += 1
              
      return (chr(self.count - 1),) # this is a 1-tuple

      con = sqlite.connect(":memory:")
      cur = con.cursor()
      cur.execute("create table characters(c)")

      theIter = IterChars()
      cur.executemany("insert into characters(c) values (?)", theIter)

      cur.execute("select c from characters")
      print cur.fetchall()

      As generators are iterators, too, here's a much simpler, equivalent example using a generator:

      from pysqlite2 import dbapi2 as sqlite

      def char_generator():
          
      import string
          
      for c in string.letters[:26]:
              
      yield (c,)

      con = sqlite.connect(":memory:")
      cur = con.cursor()
      cur.execute("create table characters(c)")

      cur.executemany("insert into characters(c) values (?)", char_generator())

      cur.execute("select c from characters")
      print cur.fetchall()

      executemany will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a Warning. Use executescript if want to execute multiple SQL statements with one call.

    • executescript method

      .executemany(sqlscript)

      This is a nonstandard convenience method for executing multiple SQL statements at once. It issues a COMMIT statement before, then executes the SQL script it gets as a parameter.

      The SQL script sqlscript can be a bytestring or a Unicode string.

      Example:

      from pysqlite2 import dbapi2 as sqlite

      con = sqlite.connect(":memory:")
      cur = con.cursor()
      cur.executescript("""
          create table person(
              firstname,
              lastname,
              age
          );

          create table book(
              title,
              author,
              published
          );

          insert into book(title, author, published)
          values (
              'Dirk Gently''s Holistic Detective Agency',
              'Douglas Adams',
              1987
          );
          """
      )
    • interrupt method

      This method has no arguments. You can call it from a different thread to abort any queries that are currently executing on the connection. This can be used to let the user abort runaway queries, for example.

    • rowcount attribute

      Although pysqlite's Cursors implement this attribute, the database engine's own support for the determination of "rows affected"/"rows selected" is quirky.

      For SELECT statements, rowcount is always -1 because pysqlite cannot determine the number of rows a query produced until all rows were fetched.

      For DELETE statements, SQLite reports rowcount as 0 if you make a DELETE FROM table without any condition.

      For executemany statements, pysqlite sums up the number of modifications into rowcount.

      As required by the Python DB API Spec, the rowcount attribute "is -1 in case no executeXX() has been performed on the cursor or the rowcount of the last operation is not determinable by the interface".




2. Brief Tutorial

This brief tutorial aims to get the reader started by demonstrating elementary usage of pysqlite. It is not a comprehensive Python Database API tutorial, nor is it comprehensive in its coverage of anything else.

2.1 Connecting to a Database

Example 1

Connecting to a database file mydb:

from pysqlite2 import dbapi2 as sqlite

con = sqlite.connect("mydb")

Example 2

Creating an in-memory database:

from pysqlite2 import dbapi2 as sqlite

con = sqlite.connect(":memory:")

2.2 Executing SQL statements

For this section, we have a database mydb defined and populated by the following SQL code:

create table people
(
  
name_last      varchar(20),
  
age            integer
);

insert into people (name_last, age) values ('Yeltsin',   72);
insert into people (name_last, age) values ('Putin',     51);

Example 1

This example shows the simplest way to print the entire contents of the people table:

from pysqlite2 import dbapi2 as sqlite

# Create a connection to the database file "mydb":
con = sqlite.connect("mydb")

# Get a Cursor object that operates in the context of Connection con:
cur = con.cursor()

# Execute the SELECT statement:
cur.execute("select * from people order by age")

# Retrieve all rows as a sequence and print that sequence:
print cur.fetchall()

Sample output:

[(u'Putin', 51), (u'Yeltsin', 72)]

Example 2

Here's another trivial example that demonstrates various ways of fetching a single row at a time from a SELECT-cursor:

from pysqlite2 import dbapi2 as sqlite

con = sqlite.connect("mydb")

cur = con.cursor()
SELECT = "select name_last, age from people order by age, name_last"

# 1. Iterate over the rows available from the cursor, unpacking the
# resulting sequences to yield their elements (name_last, age):
cur.execute(SELECT)
for (name_last, age) in cur:
    
print '%s is %d years old.' % (name_last, age)

# 2. Equivalently:
cur.execute(SELECT)
for row in cur:
    
print '%s is %d years old.' % (row[0], row[1])

Sample output:

Putin is 51 years old.
Yeltsin is 72 years old.
Putin is 51 years old.
Yeltsin is 72 years old.

Example 3

The following program is a simplistic table printer (applied in this example to people)

from pysqlite2 import dbapi2 as sqlite

FIELD_MAX_WIDTH = 20
TABLE_NAME = 'people'
SELECT = 'select * from %s order by age, name_last' % TABLE_NAME

con = sqlite.connect("mydb")

cur = con.cursor()
cur.execute(SELECT)

# Print a header.
for fieldDesc in cur.description:
    
print fieldDesc[0].ljust(FIELD_MAX_WIDTH) ,
print # Finish the header with a newline.
print '-' * 78

# For each row, print the value of each field left-justified within
# the maximum possible width of that field.
fieldIndices = range(len(cur.description))
for row in cur:
    
for fieldIndex in fieldIndices:
        
fieldValue = str(row[fieldIndex])
        
print fieldValue.ljust(FIELD_MAX_WIDTH) ,

    
print # Finish the row with a newline.

Sample output:

name_last            age
------------------------------------------------------------------------------
Putin                51
Yeltsin              72

Example 4

Let's insert more people into the people table:

from pysqlite2 import dbapi2 as sqlite

con = sqlite.connect("mydb")

cur = con.cursor()

newPeople = (
    
('Lebed'       , 53),
    
('Zhirinovsky' , 57),
  
)

for person in newPeople:
    
cur.execute("insert into people (name_last, age) values (?, ?)", person)

# The changes will not be saved unless the transaction is committed explicitly:
con.commit()

Note the use of a parameterized SQL statement above. When dealing with repetitive statements, this is much faster and less error-prone than assembling each SQL statement manually.

It's also worth noting that in the example above, the code:

It's also worth noting that in the example above, the code:

for person in newPeople:
    
cur.execute("insert into people (name_last, age) values (?, ?)", person)

could be rewritten as:

cur.executemany("insert into people (name_last, age) values (?, ?)", newPeople)

After running Example 4, the table printer from Example 3 would print:

name_last            age
------------------------------------------------------------------------------
Putin                51
Lebed                53
Zhirinovsky          57
Yeltsin              72



3. Native Database Engine Features and Extensions Beyond the Python DB API

3.1 Creating user-defined functions

SQLite supports user-defined functions. Using pysqlite, you can create new functions with the connection's create_function method:

def create_function(self, name, numparams, func)
name
the name of your function in SQL
numparams
the number of parameters your function accepts, -1 if it accepts any number of parameters
func
the Python function

The function can return any of pysqlite's supported SQLite types: unicode, str, int, long, float, buffer and None. Any exception in the user-defined function leads to the SQL statement executed being aborted.

Example:

from pysqlite2 import dbapi2 as sqlite
import md5

def md5sum(t):
    
return md5.md5(t).hexdigest()

con = sqlite.connect(":memory:")
con.create_function("md5", 1, md5sum)
cur = con.cursor()
cur.execute("select md5(?)", ("foo",))
print cur.fetchone()[0]

3.2 Creating user-defined aggregates

SQLite supports user-defined aggregate functions. Using pysqlite, you can create new aggregate functions with the connection's create_aggregate method.

def create_aggregate(self, name, numparams, aggregate_class)

The aggregate class must implement a step method, which accepts the number of parameters defined in create_aggregate, and a finalize method which will return the final result of the aggregate.

The finalize method can return any of pysqlite's supported SQLite types: unicode, str, int, long, float, buffer and None. Any exception in the aggregate's __init__, step or finalize methods lead to the SQL statement executed being aborted.

Example:

from pysqlite2 import dbapi2 as sqlite

class MySum:
    
def __init__(self):
        
self.count = 0

    
def step(self, value):
        
self.count += value

    
def finalize(self):
        
return self.count

con = sqlite.connect(":memory:")
con.create_aggregate("mysum", 1, MySum)
cur = con.cursor()
cur.execute("create table test(i)")
cur.execute("insert into test(i) values (1)")
cur.execute("insert into test(i) values (2)")
cur.execute("select mysum(i) from test")
print cur.fetchone()[0]

3.3 Creating and using collations

def create_collation(name, callable)

Creates a collation with the specified name and callable. The callable will be passed two string arguments. It should return -1 if the first is less than the second, 0 if they are equal and 1 and if the first is greater than the second. Note that this controls sorting (ORDER BY in SQL) so your comparisons don't affect other SQL operations. Read more about SQLite's handling of collations. (This calls sqlite3_create_collation.) If there is an error in your Python code then 0 (ie items are equal) is returned.

Note that the callable will get its parameters as Python bytestrings, which will normally be encoded in UTF-8.

The following example shows a custom collation that sorts "the wrong way":

from pysqlite2 import dbapi2 as sqlite

def collate_reverse(string1, string2):
    
return -cmp(string1, string2)

con = sqlite.connect(":memory:")
con.create_collation("reverse", collate_reverse)

cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])
cur.execute("select x from test order by x collate reverse")
for row in cur:
    
print row
con.close()

To remove a collation, call create_collation with None as callable:

con.create_collation("reverse", None)

3.4 Checking for complete statements

The module-level function complete_statement(sql) can be used to check if a string contains a complete SQL statement or is still incomplete. The given string could still contain invalid SQL, but be parsable as a "complete" statement!

This can be used to build a shell for SQLite, like in the following example:

# A minimal SQLite shell for experiments

from pysqlite2 import dbapi2 as sqlite

con = sqlite.connect(":memory:")
con.isolation_level = None
cur = con.cursor()

buffer = ""

print "Enter your SQL commands to execute in SQLite."
print "Enter a blank line to exit."

while True:
    
line = raw_input()
    
if line == "":
        
break
    
buffer += line
    
if sqlite.complete_statement(buffer):
        
try:
            
buffer = buffer.strip()
            
cur.execute(buffer)

            
if buffer.lstrip().upper().startswith("SELECT"):
                
print cur.fetchall()
        
except sqlite.Error, e:
            
print "An error occurred:", e.args[0]
        
buffer = ""

con.close()

3.5 Enabling SQLite's shared cache

To enable SQLite's shared cache for the calling thread, call the function enable_shared_cache.

from pysqlite2 import dbapi2 as sqlite

# The shared cache is only available in SQLite versions 3.3.3 or later
# See the SQLite documentaton for details.

sqlite.enable_shared_cache(True)

3.6 Setting an authorizer callback

You can set an authorizer callback if you want to restrict what your users can do with the database. This is mostly useful if you accept arbitrary SQL from users and want to execute it safely. See the relevant section in the SQL documentation for details: http://sqlite.org/capi3ref.html#sqlite3_set_authorizer

All necessary constants like SQLITE_OK, SQLITE_DENY, SQLITE_IGNORE, SQLITE_SELECT, SQLITE_CREATE_INDEX and all other authorizer-related constants are available through the dbapi2 module.

Here's an example that demonstrates the usage of this function:

from pysqlite2 import dbapi2 as sqlite

def authorizer_callback(action, arg1, arg2, dbname, source):
    
if action != sqlite.SQLITE_SELECT:
        
return sqlite.SQLITE_DENY
    
if arg1 == "private_table":
        
return sqlite.SQLITE_DENY
    
return sqlite.SQLITE_OK

con = sqlite.connect(":memory:")
con.executescript("""
    create table public_table(c1, c2);
    create table private_table(c1, c2);
    """
)
con.set_authorizer(authorizer_callback)

try:
    
con.execute("select * from private_table")
except sqlite.DatabaseError, e:
    
print "SELECT FROM private_table =>", e.args[0]     # access ... prohibited

try:
    
con.execute("insert into public_table(c1, c2) values (1, 2)")
except sqlite.DatabaseError, e:
    
print "DML command =>", e.args[0]     # access ... prohibited

3.7 Setting a progress handler

If you want to get called by SQLite during long-running operations, you can set a progress handler. An example use for this is to keep a GUI updated during a long-running query.

def set_progress_handler(self, handler, n)

The progress handler will be called every n SQLite virtual machine opcodes. If handler returns a nonzero value, the query is aborted with an OperationalError.

Here's an example that demonstrates the usage of this function:

from pysqlite2 import dbapi2 as sqlite

def progress():
    
print "Query still executing. Please wait ..."

con = sqlite.connect(":memory:")
con.execute("create table test(x)")

# Let's create some data
con.executemany("insert into test(x) values (?)", [(x,) for x in xrange(300)])

# A progress handler, executed every 10 million opcodes
con.set_progress_handler(progress, 10000000)

# A particularly long-running query
killer_stament = """
    select count(*) from (
        select t1.x from test t1, test t2, test t3
    )
    """


con.execute(killer_stament)
print "-" * 50

# Clear the progress handler
con.set_progress_handler(None, 0)

con.execute(killer_stament)

3.8 Using the connection as a context manager

With Python 2.5 or higher, pysqlite's connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

from __future__ import with_statement
from pysqlite2 import dbapi2 as sqlite

con = sqlite.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    
con.execute("insert into person(firstname) values (?)", ("Joe",))

# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be catched
try:
    
with con:
        
con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite.IntegrityError:
    
print "couldn't add Joe twice"

4. SQLite and Python types

4.1 Introduction

http://sqlite.org/datatype3.html

SQLite natively supports the following types: NULL, INTEGER, REAL, TEXT, BLOB.

The following Python types can thus be sent to SQLite without any problem:

Python type SQLite type
NoneType NULL
int INTEGER
long INTEGER
float REAL
str (utf-8 encoded) TEXT
unicode TEXT
buffer BLOB

This is how SQLite types are converted to Python types by default:

SQLite type Python type
NULL NoneType
INTEGER int or long, depending on size
REAL float
TEXT unicode
BLOB buffer

pysqlite's type system is extensible in both ways: you can store additional Python types in a SQLite database via object adaptation, and you can let pysqlite convert SQLite types to different Python types via pysqlite's converters.

4.2 Using adapters to store additional Python types in SQLite databases

Like described before, SQLite supports only a limited set of types natively. To use other Python types with SQLite, you must adapt them to one of pysqlite's supported types for SQLite. So, one of NoneType, int, long, float, str, unicode, buffer.

pysqlite uses the Python object adaptation, like described in PEP 246 for this. The protocol to use is PrepareProtocol.

There are two ways to enable pysqlite to adapt a custom Python type to one of the supported ones.

4.2.1 Letting your object adapt itself

This is a good approach if you write the class yourself. Let's suppose you have a class like this:

class Point(object):
    
def __init__(self, x, y):
        
self.x, self.y = x, y

Now you want to store the point in a single SQLite column. You'll have to choose one of the supported types first that you use to represent the point in. Let's just use str and separate the coordinates using a semicolon. Then you need to give your class a method __conform__(self, protocol) which must return the converted value. The parameter protocol will be PrepareProtocol.

from pysqlite2 import dbapi2 as sqlite

class Point(object):
    
def __init__(self, x, y):
        
self.x, self.y = x, y

    
def __conform__(self, protocol):
        
if protocol is sqlite.PrepareProtocol:
            
return "%f;%f" % (self.x, self.y)

con = sqlite.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print cur.fetchone()[0]

4.2.2 Registering an adapter callable

The other possibility is to create a function that converts the type to the string representation and register the function with register_adapter.

from pysqlite2 import dbapi2 as sqlite

class Point(object):
    
def __init__(self, x, y):
        
self.x, self.y = x, y

def adapt_point(point):
    
return "%f;%f" % (point.x, point.y)

sqlite.register_adapter(Point, adapt_point)

con = sqlite.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print cur.fetchone()[0]

The type/class to adapt must be a new-style class, i. e. it must have object as one of its bases!!!

pysqlite has two default adapters for Python's builtin date and datetime types. Now let's suppose we want to store datetime objects not in ISO representation, but as Unix timestamp.

from pysqlite2 import dbapi2 as sqlite
import datetime, time

def adapt_datetime(ts):
    
return time.mktime(ts.timetuple())

sqlite.register_adapter(datetime.datetime, adapt_datetime)

con = sqlite.connect(":memory:")
cur = con.cursor()

now = datetime.datetime.now()
cur.execute("select ?", (now,))
print cur.fetchone()[0]

4.3 Converting SQLite values to custom Python types

Now that's all nice and dandy that you can send custom Python types to SQLite. But to make it really useful we need to make the Python to SQLite to Python roundtrip work.

Enter pysqlite converters.

Let's go back to the Point class. We stored the x and y coordinates separated via semicolons as strings in SQLite.

Let's first define a converter function that accepts the string as a parameter and constructs a Point object from it.

!!! Note that converter functions always get called with a string, no matter under which data type you sent the value to SQLite !!!

def convert_point(s):
    
x, y = map(float, s.split(";"))
    
return Point(x, y)

Now you need to make pysqlite know that what you select from the database is actually a point. There are two ways of doing this:

  • Implicitly via the declared type
  • Explicitly via the column name

Both ways are described in section 1.4 Extensions and Caveats in the paragraphs describing the connect function, and specifically the meaning of the detect_types parameter.

The following example illustrates both ways.

from pysqlite2 import dbapi2 as sqlite

class Point(object):
    
def __init__(self, x, y):
        
self.x, self.y = x, y

    
def __repr__(self):
        
return "(%f;%f)" % (self.x, self.y)

def adapt_point(point):
    
return "%f;%f" % (point.x, point.y)

def convert_point(s):
    
x, y = map(float, s.split(";"))
    
return Point(x, y)

# Register the adapter
sqlite.register_adapter(Point, adapt_point)

# Register the converter
sqlite.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################
# 1) Using declared types
con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(p point)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
print "with declared types:", cur.fetchone()[0]
cur.close()
con.close()

#######################
# 1) Using column names
con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(p)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
print "with column names:", cur.fetchone()[0]
cur.close()
con.close()

4.4 Default pysqlite adapters and converters

pysqlite has default adapters for the date and datetime types in the datetime module. They will be sent as ISO dates/ISO timestamps to SQLite.

pysqlite has default converters registered under the name "date" for datetime.date and under the name "timestamp" for datetime.datetime.

This way, you can use date/timestamps from pysqlite without any additional fiddling in most cases. The format of the adapters is also compatible with the experimental SQLite date/time functions.

The following example demonstrates this.

from pysqlite2 import dbapi2 as sqlite
import datetime

con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")

today = datetime.date.today()
now = datetime.datetime.now()

cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur.execute("select d, ts from test")
row = cur.fetchone()
print today, "=>", row[0], type(row[0])
print now, "=>", row[1], type(row[1])

cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')
row = cur.fetchone()
print "current_date", row[0], type(row[0])
print "current_timestamp", row[1], type(row[1])

5. Controlling Transactions

By default, pysqlite opens transactions implicitly before a DML statement (INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-DQL statement (i. e. anything other than SELECT/INSERT/UPDATE/DELETE/REPLACE).

So if you are within a transaction, and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, pysqlite will commit implicitly before executing that command. There are two reasons for doing that. The first is that most of these commands don't work within transactions. The other reason is that pysqlite needs to keep track of the transaction state (if a transaction is active or not).

You can control which kind of "BEGIN" statements pysqlite implicitly executes (or none at all) via the isolation_level parameter to the connect call, or via the isolation_level property of connections.

If you want autocommit mode, then set isolation_level to None.

Otherwise leave it at it's default, which will result in a plain "BEGIN" statement, or set it to one of SQLite's supported isolation levels: DEFERRED, IMMEDIATE or EXCLUSIVE.

6.1 Using shortcut methods

Using the nonstandard execute(), executemany() and executescript() methods of the Connection object, your code can be written more concisely, because you don't have to create the - often superfluous Cursor objects explicitly. Instead, the Cursor objects are created implicitly and these shortcut methods return the cursor objects. This way, you can for example execute a SELECT statement and iterate over it directly using only a single call on the Connection object.

from pysqlite2 import dbapi2 as sqlite

persons = [
    
("Hugo", "Boss"),
    
("Calvin", "Klein")
    
]

con = sqlite.connect(":memory:")

# Create the table
con.execute("create table person(firstname, lastname)")

# Fill the table
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

# Print the table contents
for row in con.execute("select firstname, lastname from person"):
    
print row

# Using a dummy WHERE clause to not let SQLite take the shortcut table deletes.
print "I just deleted", con.execute("delete from person where 1=1").rowcount, "rows"

6.2 Accessing columns by name instead of by index

A cool new feature of pysqlite 2.1.0 is the new builtin sqlite.Row class designed to be used as a row factory.

Rows wrapped with this class can be accessed both by index (like tuples) and case-insensitively by name:

from pysqlite2 import dbapi2 as sqlite

con = sqlite.connect("mydb")
con.row_factory = sqlite.Row

cur = con.cursor()
cur.execute("select name_last, age from people")
for row in cur:
    
assert row[0] == row["name_last"]
    
assert row["name_last"] == row["nAmE_lAsT"]
    
assert row[1] == row["age"]
    
assert row[1] == row["AgE"]

7. Combining APSW and pysqlite

APSW is "Another Python SQLite Wrapper". Its goal is to directly wrap the SQLite API for Python. If there's SQLite functionality that is only wrapped via APSW, but not (yet) via pysqlite, then you can still use the APSW functionality in pysqlite.

Just use the APSW Connection as a parameter to the connect function and reuse an existing APSW connection like this.

from pysqlite2 import dbapi2 as sqlite
import apsw

apsw_con = apsw.Connection(":memory:")
apsw_con.createscalarfunction("times_two", lambda x: 2*x, 1)

# Create pysqlite connection from APSW connection
con = sqlite.connect(apsw_con)
result = con.execute("select times_two(15)").fetchone()[0]
assert result == 30
con.close()

This feature only works if both APSW and pysqlite are dynamically linked against the same SQLite shared library. I. e. it will not work on Windows without a custom built pysqlite and APSW.