variable expansion with sqlite

[Tim Golden]

marc wyburn wrote:
> Hi I'm using SQlite and the CSV module and trying to create a class
> that converts data from CSV file into a SQLite table.
> 
> My script curently uses functions for everything and I'm trying to
> improve my class programming.  The problem I'm having is with variable
> expansion.
> 
> self.cursor.executemany('INSERT INTO test VALUES (?)', CSVinput)
> 
> If CSVinput is a tuple with only 1 value, everything is fine.  If I
> want to use a tuple with more than 1 value, I need to add more
> question marks.  As I'm writing a class I don't want to hard code a
> specific number of ?s into the INSERT statement.
> 
> The two solutions I can think of are;
> using python subsitution to create a number of question marks, but
> this seems very dirty
>  or
> finding someway to substitue tuples or lists into the statement - I'm
> not sure if this should be done using Python or SQLite substitution
> though.


I do this kind of thing sometimes:

<test.csv>
a,b,c
1,2,3
4,5,6
</test.csv>

<code>
import csv
import sqlite3

reader = csv.reader (open ("test.csv", "rb"))
csv_colnames = reader.next ()

db = sqlite3.connect (":memory:")
coldefs = ", ".join ("%s VARCHAR (200)" % c for c in csv_colnames)
db.execute ("CREATE TABLE test (%s)" % coldefs)

insert_cols = ", ".join (csv_colnames)
insert_qmarks = ", ".join ("?" for _ in csv_colnames)
insert_sql = "INSERT INTO test (%s) VALUES (%s)" % (insert_cols, insert_qmarks)

db.executemany (insert_sql, list (reader))
for row in db.execute ("SELECT * FROM test"):
  print row

</code>

Obviously, this is a proof-of-concept code. I'm (ab)using
the convenience functions at database level, I'm hardcoding
the column definitions, and I'm  making a few other assumptions, 
but I think it serves as an illustration.

Of course, you're only a few steps away from something
like sqlalchemy, but sometimes rolling your own is good.

TJG