Import data from SQL Server into pandas dataframe using a list to filter desired values-CodePudding

I'm trying to import certain data from a SQL server into a new pandas dataframe using a list of values generated from a previous pandas dataframe. After my initial attempts, the best I can get for my desired output is an empty dataframe. Any help is greatly appreciated! My code is below:

    test_list = previous_df["StaffName"].tolist()

    cnxn_str = ("Driver={XXXXXXXX};"\
                "Server=XXXXXXXXXXXX;"\
               "Database=XXXXXXXXX;"\
        "UID=XXXXXXXXXX;"\
        "PWD=XXXXXXXXXX;")
   cnxn = pyodbc.connect(XXXXX)
    new_df = pd.read_sql("SELECT * \
                  FROM XXXXXXXXXXXXXX\
                  WHERE (Variable_1 in ('X1','X2','X3', 'X4') AND \
                          Varible_2= 'XXXXXXX' AND\
                          Staff_Name_in_new_df_variable in ('test_list'))",
                  cnxn)

CodePudding user response：

use string format to inject a sql_string composed of the list of staff names

previous_df=pd.DataFrame({'StaffName':['Bob','Jim','Don']})
test_list = previous_df["StaffName"].tolist()

sql_string = "("
for i in test_list:
    sql_string = sql_string   "'"   i   "',"
sql_string = sql_string[:-1]   ")"

sql="select * from table where field in{test_list}".format(test_list=sql_string)
print(sql)

output

select * from table where field in('Bob','Jim','Don')