Binding single variable and list of variables in the same statement

[urosdigital]

Hi,

Is there a way to bind a single variable and also a list of variables to the same statement?
element = '0011223344'
ids = [1111111111, 2222222222, 3333333333]

desired sql statements:
select * from table where element = '0011223344' and id in (1111111111, 2222222222, 3333333333)
update table set element = '0011223344' where id in (1111111111, 2222222222, 3333333333)

I tried the following:
bind_names = [":" + str(i + 1) for i in range(len(ids))]
query= "select * from table where element = '%s' and id in (%s)" % (element, ",".join(bind_names ))
cursor.execute(query, [ids])

This returns ORA-01484: arrays can only be bound to PL/SQL statements
I guess im doing it the wrong way.
I tried the select statement as test but in fact i need update statement.

BR,
Uros

[anthony-tuininga]

Yes, you cannot bind an array with cursor.execute(). You could simply do this:

cursor.execute(query, ids)

But better yet would be to do this:

bind_names = ",".join(":" + str(i + 2) for i in range(len(ids)))
bind_values = [element] + ids
query= f"select * from table where element = :1 and id in ({bind_names})"
cursor.execute(query, bind_values)

That way you are using bind variables for everything.

[urosdigital]

Thanks a million @anthony-tuininga

[cjbj]

Also see the documentation Binding Multiple Values to a SQL WHERE IN Clause.

[urosdigital]

Hi @cjbj that was my starting point, it wasn't so clear to me that i could came up with solution as Antony presented in the previous comment. Thank you for your input also. :)