sqlalchemy bindparam support for delete works only for one parameter

[sairamkrish]

Hello there.,

Version details :

clickhouse-sqlalchemy==0.3.2
SQLAlchemy==2.0.38

When we try to use SqlAlchemy delete using clickhouse-sqlalchemy as the dialect driver, we are facing issue while deleting. When we use bindparam and pass values as an array., this fails. It should be fairly easy to reproduce this issue

# let's assume users_table is the table on which we like to delete multiple users based on id

# Create a delete statement with bind parameters
delete_stmt = users_table.delete().where(users_table.c.id == bindparam("user_id"))

# List of user IDs to delete
user_ids_to_delete = [{"user_id": 1}, {"user_id": 2}, {"user_id": 3}]

# Execute the delete statement with the list of parameters
with engine.connect() as conn:
    conn.execute(delete_stmt, user_ids_to_delete)

The above execute fails with following error :

clickhouse_sqlalchemy.exceptions.DatabaseException: Orig exception: Code: 62.
DB::Exception: Syntax error: failed at position ** ('%'): %(user_id)s. 
Expected one of: CAST operator, ANY, ALL, NOT, INTERVAL, CASE, DATE, TIMESTAMP, tuple, 
collection of literals, array, number, literal, NULL, NULL, Bool, TRUE, FALSE, string literal, 
asterisk, qualified asterisk, compound identifier, identifier, COLUMNS matcher, COLUMNS, 
qualified COLUMNS matcher, function name, substitution, MySQL-style global variable. 

While the exact the same passes when we try to delete users one by one like this :

# Create a delete statement with bind parameters
delete_stmt = users_table.delete().where(users_table.c.id == bindparam("user_id"))

# Pass only one user IDs to delete
user_ids_to_delete = [{"user_id": 1}]

# Execute the delete statement with the list of parameters
with engine.connect() as conn:
    conn.execute(delete_stmt, user_ids_to_delete)

If we have hundreds of rows, It is not a performant solution to send individual deletes to the clickhouse server one by one. Is there a SqlAlchemy based solution for this ? Am I doing anything wrong ?

The exact solution works with sqlite database. This validates there is no SqlAlchemy specific issue. It feels like a clickhouse dialect issue.

[Totorokrut]

Hello,

You need IN operator, I think: https://docs.sqlalchemy.org/en/20/core/operators.html#in-comparisons Example:

keys = ["20313030", "20313170"]
session.execute(table.delete().where(table.c.session_id.in_(keys)))