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

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.

CodePudding user response：

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)