考虑以下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)