How to pass tuple with one element as param in sql query Python?

Question

How can I pass tuple with only one element as param in sql query Python?

I have tried this solution suggested here: imploding a list for use in a python MySQLDB IN clause

ids = [(UUID('1232a4df-5849-46c2-8c76-74fe74168d82'),)] # list of ids
cursor.execute('''
SELECT 
...
WHERE
  id IN %(ids)s
  AND created_at > %(start_dt)s
''', {
  'ids': tuple(ids), 'start_dt': '2019-10-31 00:00:00'
})

but this solution does not work in python 3 and mysql-connector-python 8.0.21. I get this error:

An error "Python tuple cannot be converted to MySQL type" is returned.

Bill Karwin · Accepted Answer · 2022-03-25 20:33:29Z

I don't think what you are trying to do is possible in the Python3 version of the MySQL Connector. The code converts Python dict values into SQL syntax, depending on the Python type. But in the connector code, only scalar types are supported, no list or tuple. See https://github.com/mysql/mysql-connector-python/blob/master/lib/mysql/connector/conversion.py#L221-L372

I'd use the solution in the accepted answer to imploding a list for use in a python MySQLDB IN clause, which is to generate N placeholders for the number of items in your list, plus one more placeholder for the value to compare to created_at. Then merge the list with the date value and pass that.

ids = ['1232a4df-5849-46c2-8c76-74fe74168d82'] # list of ids
id_placeholders = ','.join(['%s'] * len(ids))
query = f'''
SELECT *
FROM mytable
WHERE
  id IN ({id_placeholders})
  AND created_at > %s
'''

params = ids
params.append('2019-10-31 00:00:00')

cursor.execute(query, params)

Collectives™ on Stack Overflow

How to pass tuple with one element as param in sql query Python?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related