Is it possible to add a list as prepared statement parameter in the fdb library in Python?
Example:
cur = con.cursor()
list = [1,2,3]
cur.execute("""SELECT * FROM data d WHERE d.field IN ?""", (list,))
Result:
"Error while preparing SQL statement:")\nDatabaseError: (\'Error while preparing SQL statement:\\n- SQLCODE: -104\\n- Dynamic SQL Error\\n- SQL error code = -104\\n- Token unknown - line 4, column 33\\n- ?\', -104, 335544569)\n'
Are there any known solutions? Thanks in advance
Lists can't be used as a value to a parametrized query, you need to construct it yourself, by dynamically creating a query with either sufficient placeholders for all your list items, or with the literal values from your list.
Try like this.
cur.execute("""SELECT * FROM data d WHERE d.field IN %s """, (tuple(list), ))
Understanding this is an old question, future visitors should know that the answers above and in comments could present some risk of SQL injection if used on strings in a list rather than only integers. I've not created a table to test the code below specifically, but used similar code in other queries.
FYI - other SQL drivers like pyodbc and psycopg2 use ' %s ' as placeholders, but only a ' ? ' works for me using fdb.
cur = con.cursor()
list = [1,2,3]
# Create a placeholder list containing a '?' for each element
placeholders = []
for i in list:
placeholders.append('?')
# Change placeholder list to string of question marks separated by commas
ph_text = ', '.split(placeholders)
# Create sql statement
# Can use format here without risk of SQL injection because it is only ', ' and '?'
sql = """SELECT * FROM data d WHERE d.field IN ({0})""".format(ph_text)
# Execute the statement, passing list items in tuple for fdb to escape (avoid SQL-injection)
# Note that the list is converted to a tuple,
# whereas the SQL in the question had the list as the first (and only) tuple element
cur.execute(sql, tuple(list))
yes but your syntax is wrong, Firebird needs to receive
SELECT * FROM data d WHERE d.field IN (1,2,3) so I've done this in the past by (from memory)
stmt="SELECT * FROM data d WHERE d.field IN (" + list + ")"
cur.execute(stmt)
