考虑以下SQL语句:

q="SELECT H_ID, CREATION_DATE FROM HOTEL WHERE H_ID IN (700,701,702...)"


括号中的值应来自数据框:

df=pd.DataFrame({"H_ID":[701,702,703,704,705,706,707,708,709,710],"B":[0,0,0,0,0,0,1,1,2,2], "C":[4,3,3,3,2,2,2,1,1,2]})


此刻,我该怎么办?

#convert H_ID column to a string separated by comma
my_ids = str(list(df["H_ID"].apply(lambda x : str(x)))).replace("[","").replace("]","")


然后将其添加到我的查询中:

q="SELECT H_ID, CREATION_DATE FROM HOTEL WHERE H_ID IN ("+my_ids+")"


问题:是否有更好的方法来避免转换为字符串?

最佳答案

码:

import pandas as pd
import numpy

df=pd.DataFrame({"H_ID":[701,702,703,704,705,706,707,708,709,710],"B":[0,0,0,0,0,0,1,1,2,2], "C":[4,3,3,3,2,2,2,1,1,2]})
s = ', '.join(map(str, df['H_ID'].as_matrix()))
q = 'SELECT H_ID, CREATION_DATE FROM HOTEL WHERE H_ID IN ({})'.format(s)
print(q)


输出:

SELECT H_ID, CREATION_DATE FROM HOTEL WHERE H_ID IN (701, 702, 703, 704, 705, 706, 707, 708, 709, 710)

10-07 19:11
查看更多