在 Python 代码中参数化 MySQL IN 子句 [英] Parameterize an MySQL IN clause in Python code

问题描述

我正在看这个类似的问题:参数化 SQL IN 子句但是解决方案不是使用Python，所以我不得不提出一个新问题:如何参数化包含 IN 子句的查询，字符串 'ruby','rails','scruffy','rubyonrails' 来自数据框的列

I was looking at this similar question: Parameterize an SQL IN clause But the solution is not using Python, so I had to raise a new question: How do I parameterize a query containing an IN clause, the strings 'ruby','rails','scruffy','rubyonrails' comes from a column of a dataframe

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

数据帧 df 可能如下所示:

The dataframe df might look like:

column1  column2...
ruby     .
rails    . 
scruffy  .
xxx
xxxx

这是我尝试过的:我将第一列转换为列表并将其命名为 list，然后更新查询中的第二行:

Here's what I've tried: I converted the first column to a list and name it list, then update the second line in the query:

WHERE Name IN %(list)s

但是这给了我一个错误:sqlalchemy.exc.ProgrammingError: (MySQLdb._exceptions.ProgrammingError) (1064, "您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册为了在'where Name IN (('ruby','rails','xxx','xxxx','xxx','' at line 2')附近使用正确的语法

But this gave me an error: sqlalchemy.exc.ProgrammingError: (MySQLdb._exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where Name IN (('ruby','rails','xxx','xxxx','xxx','' at line 2")

我也试过 list = str(list)[1:-1] 删除方括号，但后来我得到了错误:MySQLdb._exceptions.ProgrammingError: (1064, "您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册，以了解在 'where Name IN ('\\'ruby\\', \\'rails\\', \\'xxxxx\\', \\'xxx\\',' 在第 2 行")

I also tried list = str(list)[1:-1] to remove the square bracket, but then I got error: MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where Name IN ('\\'ruby\\', \\'rails\\', \\'xxxxx\\', \\'xxx\\',' at line 2")

我的问题是我应该为 df[column1] 使用什么格式/数据类型才能使其正常工作?有人可以帮忙吗?谢谢.

My question is what format/datatype I should use for df[column1] to get this working? Can someone help please? Thanks.

推荐答案

也许你可以将列列表转换为元组:

Maybe you can convert the column list to a tuple:

col_tuple = tuple(list)

然后在查询中使用 python f-string:

Then use a python f-string in your query:

f"""SELECT * FROM Tags 
WHERE Name IN {col_tuple}
ORDER BY Count DESC"""

这篇关于在 Python 代码中参数化 MySQL IN 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！