Convert string to tuple in SQL-CodePudding

I'm using python to call a SQL script to run in snowflake. The python script passes a tuple as a string to SQL's where clause, but how can I convert the string back to tuple in SQL?

For example, the SQL query looks like: where catalog_item_id in '(1180, 3190)' whereas it suppose to be where catalog_item_id in (1180, 3190).

I tried where catalog_item_id in (select cast('(1180, 3190)' as varchar)) and where catalog_item_id in (select replace('(1180, 3190)', '''', '')), none of them works. Can someone help?

Thanks in advance!

Responding to comments, my codes in python:

file = open(fnm, 'r')
product_list = (1180, 3190)
query = file.read().replace('{item_ids}', str(product_list))
file.close()

The sql query looks like

select 
catalog_item_id,
score
from ITEM_RECOMMENDATIONS
where catalog_item_id in '{item_ids}'

CodePudding user response：

Using SPLIT_TO_TABLE:

select catalog_item_id,score
from ITEM_RECOMMENDATIONS
where catalog_item_id in (select value from split_to_table(input_param, ','));

where input_param should be provided as: '1180,3190'