CODEWITHSUNDEEP
pythonsqlite
urban_man
urban_man

Reputation: 33

Trying to insert a variable into a databse keep getting errors

Trying to insert every second element of a list into a database when a button is pressed but keep getting errors 

testing=connect(database='test.db')
    test_db=testing.cursor()
    test_db.execute('DELETE FROM test_table;')
    for zz in range(0, len(list), 2):
        print(titles[zz])
        query='INSERT INTO test_table (test_name) VALUES('+list[zz]+')'
        query2='INSERT INTO test_table (test_date) VALUES('+list2[zz]+')'
        test_db.execute(query)
        test_db.execute(query2)

This is the error I'm receiving

Exception in Tkinter callback
Traceback (most recent call last):

sqlite3.OperationalError: unrecognized token: "3D"

Upvotes: 0

Views: 59

Answers (2)

Serge Ballesta
Serge Ballesta

Reputation: 148880

First you are directly writing parameter values in the query string which is considered as a bad practice. The correct way here would be to use a parameterized query. Next, as you use 2 insert statements, you are going to split each movie in 2 database rows, with no possibility to relate the title and the date which is weird. Finally, and which is the cause of the error, SQL strings must be enclosed in single quotes, what you have failed to do.

So a direct fix for the error is:

    query="INSERT INTO test_table (test_name) VALUES('"+titles[zz]+"')"
    query2="INSERT INTO test_table (test_date) VALUES('"+dates[zz]+"')"

But IMHO what you should do is, with a simple parameterized query:

    query='INSERT INTO test_table (test_name, test_date) VALUES(?,?)'
    test_db.execute(query, (titles[zz], dates[zz]))

or even better, by preparing the query only once for all the inserts:

test_db.execute('DELETE FROM test_table;')
query='INSERT INTO test_table (test_name, test_date) VALUES(?,?)'
test_db.execute-many(query, [(titles[zz], dates[zz])
                                      for zz in range(0, len(titles), 2)]

Upvotes: 2

Madison Courto
Madison Courto

Reputation: 1277

Unsure what version of python you are using but I think this should work for both 2 & 3;

testing=connect(database='test.db')
    test_db=connection.cursor()
    test_db.execute('DELETE FROM test_table;')
    for zz in range(0, len(titles), 2):
        query='INSERT INTO `test_table`(`test_name`, `test_date`) VALUES ({},{})'.format(titles[zz],dates[zz])
        test_db.execute(query)

There is a bit of context missing, like your table structure so I can't be certain it will work.

Upvotes: 0

Related Questions