CODEWITHSUNDEEP
pythonsqlite
Griff
Griff

Reputation: 2124

Populating sqlite3 database in Python loop

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?

Upvotes: 0

Views: 3295

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121834

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.

Upvotes: 2

Related Questions