Dynamically Inserting into a Sqlite Database errors

Question

I have the following code

cur.executemany("INSERT INTO "+tablename+" (name,"+item+") VALUES(?,?)",(data,))

Which dynamically inserts values from data Now i have two problems : If i use the syntax as VALUES(?,?)",(data)) without , after data i would get this error

Error Incorrect number of bindings supplied. The current statement uses 2, and there are 4 supplied.:

which is solved only by using the syntax as VALUES(?,?)",(data,)) with ,

It solves this issue and data gets inserted into the table. But it creates another problem which i can't Query the DB and use something like

cursor = cur.execute("select * from "+tablename+" where name="+name+"")

i would get this error :

Error no such column: deek:

I Don't know how to Query the DB using the above mentioned Syntax.

I got the above syntax from sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied

Complete Code for reference :

import sqlite3
import sys
tablename='asfoor'
table_data=['Name','a','b','c']

try:
    con = sqlite3.connect('dbtrials.db')
    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS "+tablename+"")
    cur.execute("CREATE TABLE "+tablename+" (ID INTEGER PRIMARY KEY AUTOINCREMENT ,Name TEXT, "+table_data[1]+" TEXT, "+table_data[2]+" TEXT, "+table_data[3]+" TEXT )")



    name='deek'
    item='a'
    data=[name,item]
    cur.executemany("INSERT INTO "+tablename+" (name,"+item+") VALUES(?,?)",(data,))
    cursor = cur.execute("select * from "+tablename+" where name="+name+"")
    for row in cursor  :
              print(row)


except sqlite3.Error as e:

    print ("Error %s:" % e.args[0])
    sys.exit(1)

finally:

    if con:
        con.close()

it is the same , if i use VALUES(?,?)",(name,item)) i still get the same error `The current statement uses 2, and there are 4 supplied` — Mohamed Ibrahim, Jun 02 '17 at 21:22

score 1 · Accepted Answer · answered Jun 02 '17 at 21:21

The reason for the error is because you forgot to put quotes around the string. It should be:

cursor = cur.execute("select * from "+tablename+" where name='"+name+"'")

But it would be better to use a parametrized query:

cursor = cur.execute("select * from "+tablename+" where name= %s", (name,))

score 1 · Answer 2 · answered Jun 02 '17 at 21:30

The executemany expects iterator of sequences or mappings as a second argument. Your input should look like this: data = [[name, item]]

So the query you expect:

deek , a (2 args)

Without the inner list it takes the string as a sequence of chars so your queries are:

d, e, e, k (4 args)
a (1 arg)

Dynamically Inserting into a Sqlite Database errors

2 Answers2