I have a list of integers that contains EMPI_ID
emp_list = [1,2]
I have a variable that defines the SQL query
emp_sql = ''' select emp_id , emp_name from emp where emp in (%s) '''
Columns for the dataframe:
emp_columns = [emp_id, emp_name]
When I try to convert them to comma separated integer values, the sql_string hold Str values and is failing to fetch the data from database.
emp = ','.join(emp_list) sql_string = emp_sql%(emp) data = connection.fetchall(sql_string) df = pd.DataFrame.from_records(data, columns=emp_columns)
Please advise how i can change the query to substitute the IN clause of SQL with list of integers. from_records does not have param as parameter to pass the joined sql string.
Advertisement
Answer
list comprehension is you friend
emp_sql = f''' select emp_id , emp_name from emp where emp in (%s) '''%(", ".join([str(i) for i in emp_list])) print(emp_sql)
output:
select emp_id , emp_name from emp where emp in (1, 2)
join only works with str
, so you have to convert the elements in the list, thats where you can use list comprehension. With the join, you can enter them into the query.
EDIT: if you want the numbers quoted, try this
emp_sql = f''' select emp_id , emp_name from emp where emp in (%s) '''%(", ".join([f"'{i}'" for i in emp_list])) print(emp_sql)
giving this output:
select emp_id , emp_name from emp where emp in ('1', '2')