CODEWITHSUNDEEP
pythonsqlite
Steffen
Steffen

Reputation: 733

Python sqlite3 string variable in execute

I try to execute this sqlite3 query in Python. I reduced the code to the minimum, sqlite.connect, etc works.

column = 'Pron_1_Pers_Sg'
goal = 'gender' 
constrain = 'Mann'


with con:
    cur = con.cursor()

    cur.execute("SELECT ? FROM Data where ?=?", (column, goal, constrain))
    con.commit()

    rows = cur.fetchall()

    for element in rows:
        values.append(element)

This returns an empty list. If I hardcode the strings, it works and returns values.

Upvotes: 25

Views: 82128

Answers (5)

Alphard
Alphard

Reputation: 25

for the fast sql;

//if variable "a" comes from another file (.py or .csv etc.)

try:
con = sqlite3.connect('xyz.db')

sql1=" SELECT * FROM table_name where column_name="+ a +" "
with con:
   cur = con.cursor()
   cur.execute(sql1)
        
   con.commit()

   rows = cur.fetchall()

   for element in rows:
       print(element)   

   cur.close() 

except sqlite3.Error as error:
    print("Failed to read data from sqlite table", error)
finally:
    if con:
        con.close()
        print("The SQLite connection is closed")

Upvotes: 0

Gloryson
Gloryson

Reputation: 84

I just decided to drop this here in case anyone finds it helpful:
You can use Python 3's f-strings as below:

Use this syntax if the variable to be passed on to the SQL query is numeric such as an integer
cur.execute(f"SELECT * FROM table_name WHERE column_name={integer_variable}")

As for strings you can edit the format string as below:
cur.execute(f"SELECT * FROM table_name WHERE column_name='{string_variable}'")
Please take note of the '' when passing variables that are strings.

You can also use the f-strings with multi-line strings, for example:

sql_query = f"""SELECT * FROM table_name WHERE column_name={your_variable} ... SOME MORE SQL"""

Then finally pass the sql_query to the cursor as follows: cur.execute(sql_query)

Upvotes: 0

Rajendra
Rajendra

Reputation: 29

Try this: c.execute("SELECT {idf} FROM Data WHERE {goal}".\ format(idf=column, goal=constrain))

Upvotes: 2

CL.
CL.

Reputation: 180060

Parameter markers can be used only for expressions, i.e., values. You cannot use them for identifiers like table and column names.

Use this:

cur.execute("SELECT "+column+" FROM Data where "+goal+"=?", (constrain,))

or this:

cur.execute("SELECT %s FROM Data where %s=?" % (column, goal), (constrain,))

(And don't commit before you have actually finished accessing the data.)

Upvotes: 46

sdoering
sdoering

Reputation: 63

I was having quite a similar problem today. I am not sure, if this might solve your problem:

cur.execute("SELECT ? FROM Data where ?=?", (column, goal, constrain,))

Important is the last ,

Give it a try, this was the problem with my code - so maybe it helps you too. Sorry, for not being able to really explain why, as I am just learning myself and am into python/sqlite for some weeks.

Upvotes: 1

Related Questions