Populating sqlite3 database in Python loop

Question

I have constructed a database but when I loop through my data to populate it, I get the following error:

OperationalError: no such column: tmp1

Code:

with con:
    cur = con.cursor()
    cur.execute("CREATE TABLE TESTTABLE(X REAL, Y REAL)")

for i in xrange(0,5):
   tmp1 =  array[i,0]
   tmp2 =  array[i,1]
   with con:
        cur.execute("""INSERT INTO TESTTABLE VALUES(tmp1,tmp2)""")

Basically I have a big array that I want to transfer into a database. This probably isn't the most efficient way of going about it. Suggestions?

Martijn Pieters · Accepted Answer · 2013-05-09 14:42:50Z

2

If you want to insert values into a row, you need to pass those values along as SQL parameters to the .execute() call:

with con:
    for i in xrange(0,5):
        tmp1 = array[i, 0]
        tmp2 = array[i, 1]
        cur.execute("""INSERT INTO TESTTABLE VALUES(?, ?)""", (tmp1, tmp2))

The ? characters are parameters, and they are filled, in order, by values takes from the second argument to .execute(), a tuple. The above code will insert the numbers 0 through to 4 as pairs into the database.

Names in the SQL code have no correlation to names you define in Python, values can only be passed in explicitly.

edited May 9, 2013 at 14:42

answered May 9, 2013 at 14:24

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

3 Comments

Griff Over a year ago

So do I replace the ? with the variable names I assigned, (X,Y) as well? Could you edit your answer to show how cur.execute("""INSERT INTO TESTTABLE VALUES(tmp1,tmp2)""") should be changed. cur.execute("""INSERT INTO TESTTABLE VALUES(?,?)""",(tmp1,tmp2)) doesn't work.

Griff Over a year ago

I'm inserting the value of an array element, not the entire array.

Martijn Pieters Over a year ago

@Griff: Can you elaborato on why my answer "doesn't work" for you? What types are tmp1 and tmp2 here? What is the error that you receive?

Collectives™ on Stack Overflow

Populating sqlite3 database in Python loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related