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.
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.)
column or goal to be controlled by an attacker.
Try this: c.execute("SELECT {idf} FROM Data WHERE {goal}".\
format(idf=column, goal=constrain))
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.
, at the end is needed only for tuples with one element, to differentiate them from a plain expression.
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)
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")
