Using a list or tuple as a bind variable in Where statement

[import-fola]

I'm trying to query a table where the ID's are in a list of select id's. I've tried passing this bind variable as both a list and tuple but nothing works. This issue also duplicates https://github.com/catherinedevlin/ipython-sql/issues/98 but his solution doesn't work for me.

Here's what I tried doing: cust_ids = list(abc_ids.customerId.unique()) also tried: cust_ids = tuple(list(abc_ids.customerId.unique()))

%%sql SELECT * FROM [DM].[dbo].[Vw_Application] va WHERE va.CustomerId IN :cust_ids

I get the error: (pyodbc.ProgrammingError) ("A TVP's rows must be Sequence objects.", 'HY000') [SQL: SELECT * FROM [DM].[dbo].[Vw_Application] va WHERE va.CustomerId in ?] [parameters: ([1056991, 1060609, 1009983, 1010940, 1024749, 1042552, 1043334, 1045169, 1049227, 1050065, 1055142, 1055702, 1056315, 1056459, 1056739, 1056921, 10570 ... (99236 characters truncated) ... 95450, 995908, 996126, 996263, 996669, 996903, 996980, 997027, 997170, 997326, 997481, 997821, 997903, 998114, 998148, 998643, 998736, 999379, 999987],)] (Background on this error at: http://sqlalche.me/e/13/f405)

[aempinheiro]

I solved it as such, tried to apply your example:

def _resolve_location(self, *cust_ids):
        with self.pool.connect() as conn:
            sql = text(
                "SELECT * FROM dbo.CUSTOMERS where CUSTOMER_ID in :cust_ids"
            )
            sql = sql.bindparams(
                bindparam('cust_ids', expanding=True),
            )
            cursor_obj = conn.execute(sql, cust_ids=cust_ids)
            result = list(cursor_obj.fetchall())