Inserting multiple types into an SQLite database with Python

Question

I'm trying to create an SQLite 3 database from Python. I have a few types I'd like to insert into each record: A float, and then 3 groups of n floats, currently a tuple but could be an array or list.. I'm not well-enough versed in Python to understand all the differences. My problem is the INSERT statement.

DAS = 12345
lats = (42,43,44,45)
lons = (10,11,12,13)
times = (1,2,3,4,5,6,7,8,9)

import sqlite3
connection = sqlite3.connect("test.db")
cursor = connection.cursor()
cursor.execute( "create table foo(DAS LONG PRIMARY KEY,lats real(4),lons real(4), times real(9) )" )

I'm not sure what comes next. Something along the lines of:

cmd = 'INSERT into foo values (?,?,?,?), ..."
cursor.execute(cmd)

How should I best build the SQL insert command given this data?

Answer 1

Something like this:

db = sqlite3.connect("test.db")
cursor = db.cursor()
cursor.execute("insert into foo values (?,?,?,?)", (val1, val2, val3, val4))
db.commit() # Autocommit is off by default (and rightfully so)!

Please note, that I am not using string formatting to inject actual data into the query, but instead make the library do this work for me. That way the data is quoted and escaped correctly.

EDIT: Obviously, considering your database schema, it doesn't work. It is impractical to attempt to store a collection-type value in a single field of a sqlite database. If I understand you correctly, you should just create a separate column for every value you are storing in the single row. That will be a lot of columns, sure, but that's the most natural way to do it.

Answer 2

The type real(4) does not mean an array/list/tuple of 4 reals; the 4 alters the 'real' type. However, SQLite mostly ignores column types due to its manifest typing, but they can still affect column affinity.

You have a few options, such as storing the text representation (from repr) or using four columns, one for each.

You can modify this with various hooks provided by the Python's SQLite library to handle some of the transformation for you, but separate columns (with functions to localize and handle various statements, so you don't repeat yourself) is probably the easiest to work with if you need to search/etc. in SQL on each value.

If you do store a text representation, ast.literal_eval (or eval, under special conditions) will convert back into a Python object.

Answer 3

(A month later), two steps:
1. flatten e.g. DAS lats lons times to one long list, say 18 long
2. generate "Insert into tablename xx (?,?,... 18 question marks )" and execute that.

Test = 1

def flatten( *args ):
    """ 1, (2,3), [4,5] -> [1 2 3 4 5] """
    # 1 level only -- SO [python] [flatten] zzz
    all = []
    for a in args:
        all.extend( a if hasattr( a, "__iter__" )  else [a] )
     if Test:  print "test flatten:", all
    return all

def sqlinsert( db, tablename, *args ):
    flatargs = flatten( *args )  # one long list
    ncol = len(flatargs)
    qmarks = "?" + (ncol-1) * ",?"
    insert = "Insert into tablename %s values (%s)" % (tablename, qmarks)
    if Test:  print "test sqlinsert:", insert
    if db:
        db.execute( insert, flatargs )
        # db.executemany( insert, map( flatargs, rows ))
    return insert

#...............................................................................
if __name__ == "__main__":
    print sqlinsert( None, "Table", "hidiho", (4,5), [6] )