MariaDB Python连接器-如何执行“where in”查询?

7nbnzgx9  于 2023-04-06  发布在  Python
关注(0)|答案(1)|浏览(162)

如何使用MariaDBs的Python 3连接器执行where in类型的SQL查询?
示例代码:

data = [] # Python 3 list, containing tuples of int, see below for datatype

cursor.execute('select col1, col2, col3 from tableName where col4 in %s', (data, ))

data类型:

print(type(data))       # <class 'list'>
print(type(data[0]))    # <class 'tuple'>
print(type(data[0][0))  # <class 'int'>

元组中的数据是从另一个SQL查询中获得的:

STR = 'some string'
cursor.execute('select some_id from some_table where some_column = %s', (STR, ))

产生的实际错误:

mariadb.DataError: Data type 'list' in column 0 not supported in MariaDB Connector/Python
w8ntj3qf

w8ntj3qf1#

您可以为每个参数生成一串占位符...

placeholders = ", ".join(["%s"] * len(data))

然后将其嵌入到SQL字符串中...

sql = f"select col1, col2, col3 from tableName where col4 in ({placeholders})"

现在将data转换为整数列表...

values = [x[0] for x in data]

准备执行。。

cursor.execute(sql, values)

相关问题