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.

You have a table with 200 columns? It is likely there's a better database design available to you. For example, try putting what you currently have as most of your column names into a column of its own. — Joe Hildebrand
– Joe Hildebrand, Commented Oct 21, 2010 at 18:52
This is not SQLite's fault! SQLite has no way to know what listOfVars or row mean... — rsenna
– rsenna, Commented Oct 21, 2010 at 18:53
Try doing a print(cmd) after your cmd = ... assigments, and you'll see the difference in the 2. This isn't really a sqlite thing, it's simply a Python string formatting question. The best way to accomplish what you're trying to do is to apply Adam Bernier's answer. — snapshoe
– snapshoe, Commented Oct 21, 2010 at 19:04

mechanical_meat · Accepted Answer · 2010-10-22 03:04:26Z

7

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:

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

edited Oct 22, 2010 at 3:04

answered Oct 21, 2010 at 18:50

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Curious2learn Over a year ago

Thanks for your answer. I have not tried it yet, since I do not completely understand it; though I can guess what the different commands are doing. Anyhow, appreciate the opportunity to learn new things. For the sake of understanding, why is this solution better than my second method, which works. I think SilentGhost has explained it, but I do not understand. I will accept the answer after I get a chance to try it out. Thanks!!

Ignacio Vazquez-Abrams · Accepted Answer · 2010-10-21 18:52:15Z

0

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.

answered Oct 21, 2010 at 18:52

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Collectives™ on Stack Overflow

python sqlite question - Insert method

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related