SYNTAX ERROR FOR SQLITE VARIABLE PASSING COMMAND?

Question

i am using sqlite3 with python and want the data of my colomn "Pcode" and the situation is that "Pcode" is stored in a variable say "b"

        b= "Pcode"
        b = str(b)
        c.execute('''SELECT ? FROM AddStock''',(b,))
        results = c.fetchall()
        print(results)

the expected results are as follows

[('***data from database***',), ('***data from database***',)]

but getting the result as :

[('Pcode',), ('Pcode',)]

and i dont know why iam getting this type of results?

Just trying to rule out the obvious have you tried hard coding it I.E. c.execute('SELECT Pcode FROM AddStock')? — Shane, Sep 11 '19 at 13:32
That query returns the parameter value for every row in the table. You can't pass table or column names as parameters. In a database, the query is *not* executed as is. It's compiled into an execution plan based on the table statistics, the column types, indexes etc. Different tables, columns, statistics, indexes result in *very* different execution plans. — Panagiotis Kanavos, Sep 11 '19 at 13:46
Duplicate of https://stackoverflow.com/questions/35345378/substituting-column-names-in-python-sqlite3-query — MePsyDuck, Sep 11 '19 at 13:51

score 0 · Answer 1 · answered Sep 11 '19 at 13:37

0

use the python script methord to add the variable in list

c.execute("SELECT "+b+" FROM AddStock")

this is the syntax for the same.

answered Sep 11 '19 at 13:37

nalayak

score 0 · Answer 2 · answered Sep 11 '19 at 13:44

0

if you are using sqlite3, this code gives me the correct rows:

conn = sqlite3.connect(db_file)
b= "Pcode"
b = str(b)

cur = conn.cursor()
cur.execute('''SELECT ? FROM AddStock''',(b,))

print(cur.fetchall())

answered Sep 11 '19 at 13:44

nexi

no bro its not working it will also give the same unexpected result – nalayak Sep 11 '19 at 14:02
Did you check the DB file using SQLITE browser? Maybe the data is not there? – nexi Sep 12 '19 at 07:30
i got the result and the output for your code is like this contain [((result1,),) ,((result2,),)] in which if i store in a var say 'a' and call for a[0] resul are "(result1,)," even paranthese and commas are included. – nalayak Sep 14 '19 at 18:17
I can't seem to reproduce your issue, can you provide the db file and the code of the function? – nexi Sep 16 '19 at 22:37
i got the answer you can see below Answer :-) – nalayak Sep 17 '19 at 17:07

2 Answers2