how to input a tuple inside the python sql IN operator

Asked Nov 18 '22 at 05:26

Active Nov 18 '22 at 05:26

Viewed 41 times

I have a query which takes some input which will be used in IN operator of sql. Since IN operator takes a () which is a tuple in python, I am unable to give the input to the query as a tuple because my input tuple is like -> (296,). In this, I am not sure how to deal with the , inside the tuple because it causing an error.

my data for reference: https://www.db-fiddle.com/f/m4RoYpdKQXY5KcVgrfmApq/0

My query with python is as follow:

    query = '''
      SELECT timestamp as timestamp,
      id ,
      users
      FROM rc 
      WHERE  timestamp='{}' and id IN {}
     
    '''.format(timestamp, id_tuple)

    df = pd.read_sql(query, con=sql.engine)

asked Nov 18 '22 at 05:26

sam

1

`'''.format(timestamp, '(' + ','.join(id_tuple) + ')')` – Epsi95 Nov 18 '22 at 05:28
1

better to use parameters https://stackoverflow.com/questions/24408557/pandas-read-sql-with-parameters – Epsi95 Nov 18 '22 at 05:30
my input tuple has`int`. – sam Nov 18 '22 at 05:35
`'''.format(timestamp, '(' + ','.join(id_tuple) + ')')` -> this gives `TypeError: sequence item 0: expected str instance, int found` – sam Nov 18 '22 at 05:41
1

`'''.format(timestamp, '(' + ','.join(map(str, id_tuple)) + ')')` – Epsi95 Nov 18 '22 at 07:33
As a generic approach, use `= any` instead of `in`. `and id = any(string_to_array('{}', ',')::integer[])` and pass the argument as a comma-separated list. – Stefanov.sm Nov 18 '22 at 08:35

how to input a tuple inside the python sql IN operator

0 Answers0