List in Parameterized sql cell

Problems and bugs
1

I have a python variable ids as a list of string in python cell. I want to query the database with this variable.

select name from units where id in ({ids})
This does not work. If I check in the query log in db, it shows select name from units where id in ( ? )

I don’t get any results. If I run the query directly in the db with the list filled in, I get results.

2

Hi,
You don’t need to use brackets in this case.

select name from units where id in {ids}

Also please make sure the “ids” variable was calculated before, if you don’t use reactive mode.

3

Hi, In which data format must ids be? for me it works neither as list nor as set nor as str?

4

Hello, Tanja.
Both list and set types works.
Could you give an example of a query and an error message.

5

I found the right formatting here, but it does not work if I use the filter twice, like here:

select name from units1 where id in {ids}
union all
select name from units1 where id in {ids}

Is there a solution for this?

6

Hi Tanja,
I have no problem with such type of query.
Which error message are you getting?

7

Your code cell should define ids as a python list, something like this.

ids = [1,2,3]

SQL cell -

select name from units where id in {ids}

Please give a full example when it doesn’t work for you

8

I have same issue:
Python cell

sap_id_list = canastas_revision['sap_id'].to_list()

SQL cell

select
    id as id_item,
    codigo as sap_id
from
    ventas.ventas.dim_articulos as da
where
    codigo in {sap_id_list}

I got this error:

Exception: [42601] ERROR: syntax error at or near “{”
Position: 112

My settings are as follow:
Captura de pantalla 2024-03-04 a la(s) 12.24.09
I use DataSpell.
Thanks in advance