Converting list to comma separated integers to be substituted in IN clause of Pandas dataframe query

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.

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')