python sqlite question - Insert method

Question

In the code below row is a tuple of 200 elements (numbers) and listOfVars is a tuple of 200 strings that are variable names in the testTable. tupleForm is a list of tuples, 200 elements in each tuple.

The following code does not work. It returns a syntax error:

for row in tupleForm:
    cmd = '''INSERT INTO testTable listOfVars values row'''
    cur.execute(cmd)     

However, the following works fine. Can someone explain why? I am finding sqlite so non-intuitive.

for row in tupleForm:
    cmd = '''INSERT INTO testTable %s values %s'''%(listOfVars, row) 
    cur.execute(cmd)

Thanks for your help.

Answer 1

insert_query = '''INSERT INTO testTable ({0}) VALUES ({1})'''.format(
               (','.join(listOfVars)), ','.join('?'*len(listOfVars)))
cur.executemany(insert_query, tupleForm)

Please do not use normal string interpolation for database queries.
We are fortunate to have the DB-API which explains in detail how to do what you require.

Edit:
A couple of quick reasons why this method is better than your 2nd attempt:

1. by using the built-in string-escaping tools we avoid SQL injection attacks, and
2. executemany accepts a list of tuples as an argument.

Answer 2

The first attempts to use the strings as the values, which is wrong. The second substitutes the string value of the tuples into the string, which results in (...) in both cases.

But both forms are incorrect, since the second doesn't escape values at all. What you should be doing is using a parametrized query.