SQLite execute statement for variable-length rows

Question

I've read the advice here about using parametrized execute call to do all the SQL escaping for you, but this seems to work only when you know the number of columns in advance.

I'm looping over CSV files, one for each table, and populating a local DB for testing purposes. Each table has different numbers of columns, so I can't simply use:

sql = "INSERT INTO TABLE_A VALUES (%s, %s)"
cursor.execute(sql, (val1, val2))

I can build up an sql statement as a string quite flexibly, but this doesn't give me the use of cursor.execute's SQL-escaping facilities, so if the input contains apostrophes or similar, it fails.

It seems like there should be a simple way to do this. Is there?

Can you provide an example of the case when escaping doesn't work for you? — Vader, Jun 01 '15 at 09:19
An alternate way is to [escape](http://www.sqlite.org/lang_expr.html) the apostrophes at the source (CSV files) itself by replacing all single apostrophes with double apostrophes. This is just a workaround to save your time. — bprasanna, Jun 01 '15 at 10:46

score 3 · Accepted Answer · answered Jun 01 '15 at 11:40

3

If you know the number of parameters, you can create a list of them:

count = ...
sql = "INSERT INTO ... VALUES(" + ",".join(count * ["?"]) + ")"
params = []
for i in ...:
    params += ['whatever']
cursor.execute(sql, params)

answered Jun 01 '15 at 11:40

CL.

173,858
17
217
259

Hmm good idea. Although each table has a different number of columns, it's easy to find out for each table at run time. – James Bradbury Jun 01 '15 at 16:49

SQLite execute statement for variable-length rows

1 Answers1