Inserting multiple types into an SQLite database with Python
Question: I'm trying to create an [SQLite](http://en.wikipedia.org/wiki/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: 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](http://sqlite.org/datatype3.html#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](http://docs.python.org/library/sqlite3.html) 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](http://docs.python.org/library/ast.html#ast.literal_eval)
(or eval, under special conditions) will convert back into a Python object.

