variable expansion with sqlite

[Gerhard Häring]

Tim Golden wrote:
> 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. [..]

My code would probably look very similar. Btw you don't need to use 
list() on an iterable to pass to executemany(). pysqlite's executemany() 
accepts anything iterable (so generators work fine, too).

Also, with SQLite you can just skip data type definitions like 
VARCHAR(200). They're ignored anyway.

-- Gerhard