Python3 dynamic sqlite select query

Question

I am struggling to replicate an sqlite SELECT query.

sqlite> select "de-515" from balances where id = '1';
de-515
0.1
sqlite>

works with no issue from sqlite, however when I try building this query into a python script nothing I put together seems to work. I have tried the following with the intent of setting a python variable equal to the 0.1 value that is seen above.

elementBalQuery = 'SELECT "%s" FROM balances WHERE id="%s"'
cursor.execute(elementBalQuery, (elementName,userId))
curBal = cursor.fetchall()
print("curBal = ", curBal)

this results in

Traceback (most recent call last): 
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 2 supplied.

Also this fails

cursor.execute("SELECT %s FROM balances WHERE id=?", % (elementName), (userId))

cursor.execute("SELECT %s FROM balances WHERE id=?", % (elementName), (userId))
SyntaxError: invalid syntax

I have attempted several other ways to craft this query and cannot figure out what I am missing. I come from a bash background where I would be able to easily set a var equal to the output of a sqlite query.

How would, using a dynamic query, I select a row using a variable name?

snakecharmerb · Accepted Answer · 2020-12-03 05:10:09Z

2

You need to combine string formatting and parameter substitution to create a query that can be executed safely.

# Sqlite uses question marks for value placeholders
# and double-quotes for identifiers.  See
# https://www.sqlite.org/lang_keywords.html

sql = 'SELECT "%s" FROM balances WHERE id = ?'
# Use string formatting (%, .format, f-string) to add the column name(s)
sql = sql % column_name
# Use parameter substitution to add the value(s) to ensure correct quoting of values
result = cursor.execute(sql, (1,))

There is an alternative form for value placeholders

sql = 'SELECT "%s" FROM balances WHERE id = :id'
sql = sql % column_name
result = cursor.execute(sql, {'id': 1})

edited Dec 3, 2020 at 5:10

answered Nov 28, 2020 at 11:33

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pouya Esmaeili · Accepted Answer · 2020-11-28 11:41:50Z

1

Define your query like this:

query = f'''SELECT {elementName} FROM {tableName} WHERE id={userId}'''

and then:

cursor.execute(query)

Parameters should be assigned before query construction.

Note that this type of formatting is not supported in python2

edited Nov 28, 2020 at 11:41

answered Nov 28, 2020 at 11:33

Pouya Esmaeili

1,2614 gold badges15 silver badges27 bronze badges

4 Comments

R.martinez Over a year ago

I am using python3. For some reason when I try this I get a syntax error. sqlite3.OperationalError: near ")": syntax error

R.martinez Over a year ago

My exact implementation was: balQuery = f'''SELECT {elementName} FROM balances WHERE id={userId}'''

Pouya Esmaeili Over a year ago

Thank you, Let me investigate more

R.martinez Over a year ago

I actually got it to work with cursor.execute("SELECT (" + elementName + ") FROM balances WHERE id=?", (userId))

R.martinez · Accepted Answer · 2020-12-02 22:50:54Z

1

the below answers also worked, I was able to get it working with cursor.execute("SELECT (" + elementName + ") FROM balances WHERE id=?", (userId)) one of the other issues I found is that some of the elementName entries there was a hyphen that I think the script didn't like. eg. rf-431 I removed all hypens from the DB and got this working, thanks to all for promptly answering.

answered Dec 2, 2020 at 22:50

R.martinez

1611 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

Python3 dynamic sqlite select query

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related