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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.)

Method 2

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

Method 3

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0