如何将pandas数据框插入SQL的IN操作符

Question

我有一个具有唯一用户号的pandas数据框架:

data_frame = pd.DataFrame({'uniq_num' :['1qw3','2wed','3das','4frr','533ew','612w']})

我想把这个列传递给sql查询，我使用IN操作符:

SELECT users FROM database
where users IN ("here I want to pass my dataframe, so it would search in all rows of my dataframe")

我已经试过了

data_frame = ','.join([str(x) for x in data_frame.iloc[:, 0].tolist()])

返回'1qw3,2wed,3das,4frr,533ew,612w'

然后是WHERE users in STRING_SPLIT(data_frame, ',')但是这个显然不行

Answer 1

您可以将列表转换为元组，这将为您提供正确的格式

import pandas as pd
data_frame = pd.DataFrame({'uniq_num' :['1qw3','2wed','3das','4frr','533ew','612w']})
in_statement = tuple(data_frame.iloc[:, 0].tolist())
sql = f"""SELECT users FROM database
where users IN {in_statement}"""

输出SQL变量

SELECT users FROM database
where users IN ('1qw3', '2wed', '3das', '4frr', '533ew', '612w')

Answer 2

如果你正在用python创建SQL查询，那么试试这个

user_ids = "'"+"','".join(data_frame.uniq_num.values)+"'"
query = "SELECT users FROM database "+
"WHERE users IN ("+user_ids+")"