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
--
http://mail.python.org/mailman/listinfo/python-list
