Python sqlite3

Some notes on using sqlite3 from python. First, lets create a table in an in-memory database:

import sqlite3

DB_TABLE_NAME = 'cache'
DB_TABLE_COLUMN_DEFS = 'event blob'

conn = sqlite3.connect(':memory:')
cur = conn.cursor()

sql = 'CREATE TABLE %(table_name)s (%(column_defs)s)' % {
      'table_name': DB_TABLE_NAME,
      'column_defs': DB_TABLE_COLUMN_DEFS}

cur.execute(sql)

Next we want to insert multiple values at once. You can use executemany to avoid needing a ? for every single value, although the format it requires is quite clumsy. A list of values for insertion would look like this:

[('test',), ('test',), ('test',), ('test',), ('test',)]

Neither the questionmark or named parameter substitution seems to work for table names, so you'll need to stick those in using normal string interpolation. The actual insert statement is:

VALUES = [('test',)] * 58
sql = 'INSERT INTO %(table_name)s VALUES (?)' % {'table_name':
                                                  DB_TABLE_NAME}
cur.executemany(sql, VALUES)

This function will delete a list of ids by converting the list to the necessary array of tuples first:

def DeleteRows(id_list):
  """Deletes row from database.

  Args:
    id_list: list of row ids.
  """
  sql = 'DELETE FROM %s WHERE rowid IN (?)' % DB_TABLE_NAME

  # sqlite needs a sequence like [(34,), (38,)]
  id_tuples = []
  for rowid in id_list:
    id_tuples.append((rowid,))

  cur.executemany(sql, id_tuples)

Test code for this function looks like the following. Using executemany for select will get you an error, so I just spell out the necessary ?'s.

id_list = [8, 51, 52, 58]
select_list = [8, 51, 34, 38, 52, 58]
DeleteRows(id_list)
sql = 'SELECT rowid FROM %(table_name)s WHERE rowid IN (?,?,?,?,?,?)' % {
    'table_name': DB_TABLE_NAME}
result = cur.execute(sql, tuple(select_list))
assert(result.fetchall() == [(34,), (38,)])