Python3 dynamic sqlite select query

user_7941925 0
Viewed 697

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?

sqlite python
3 Answers

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})
user_53209060

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

user_131183270

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.

user_79419250
Related