SNOW-638842: "WHERE xxx IN :param" does not work as expected

[shusson]

trafficstars

1. What version of Python are you using? 3.8.13

2. What operating system and processor architecture are you using? macOS-12.4-x86_64-i386-64bit

3. What are the component versions in the environment (pip freeze)? snowflake-sqlalchemy==1.2.4 sqlalchemy==1.3.23

4. What did you do?

schemas = ('a', 'b') # same issue when schemas is a list
query = """
    SELECT schema_name
    FROM information_schema.schemata AS s
    WHERE s.schema_name IN :schemas
"""
results = conn.execute(sqlalchemy.sql.text(query), schemas=schemas)

Expected

SELECT schema_name
FROM information_schema.schemata AS s
WHERE s.schema_name IN ('a','b')

Actual

SELECT schema_name
FROM information_schema.schemata AS s
WHERE s.schema_name IN 'a','b'

There is a workaround which is to add the parens in the query string e.g

WHERE s.schema_name IN (:schemas)

[sfc-gh-mkeller]

recreate jira

[sfc-gh-aling]

hey @shusson, this is the expected behavior of how the underlying snowflake-connector-python works.

I understand that it is tempting to pass the tuple itself which has the parentheses and want the parentheses to be part of the query. However, the input tuple is treated by the connector as a container for multiple parameters instead of simply of string form such that parentheses won't be taken part of the query and they have to be added manually.

In the connector, each element in the tuple will be handled separately.

the workaround you mentioned WHERE s.schema_name IN (:schemas) is what I would also recommend to do.

[sfc-gh-aling]

I'm closing the issue since we didn't hear back from you, but please feel free to reach out if you have any other questions.

[shusson]

Hey @sfc-gh-aling thanks for the update. The reason I expected it this way is because the postgres sqlalchemy dialect only works this way.

i.e with postgres this works

WHERE s.schema_name IN :schemas

But this does not

WHERE s.schema_name IN (:schemas)

I'm not sure what repo should fix this, but as it is, I cannot write db agnostic SQL.

[shusson]

@sfc-gh-aling any updates on this? I'm guessing that snowflake should be following the same pattern that the postgres dialect uses in sqlalchemy.

[shusson]

This is still an issue for us @sfc-gh-aling @sfc-gh-mkeller. Can we re-open this ticket?

[sfc-gh-aling]

@shusson , apologize for the late response, I was working on some other prioritized stuff. yes, I will check how postgres implements it

[markfickett]

We use the WHERE x IN :list_from_params style with MySQL SQLAlchemy as well, so it would require a lot of combing through SQL to change to IN (:list_from_params) in a switch to Snowflake for us.

[markfickett]

Another case, even with the IN (:list_from_params) form, I'm getting this same error when using pd.read_sql. For example:

sql_query = "SELECT * FROM my_table WHERE my_col IN (:list_from_params)"
params = {"list_from_params": ["a", "b", "c"]}
pd.read_sql(sql_query, self._session.get_bind(), params=params)

[markfickett]

Using SqlAlchemy's TextClause.bindparams and sqlalchemy.bindparam as described in this SO post works with both MySQL and Snowflake SqlAlchemy drivers, so it's a feasible workaround:

import pandas as pd
from sqlalchemy import bindparam, text

with open_my_db() as session:
    sql_query = """
        SELECT name, date, id
        FROM my_table
        WHERE id IN :id_list
    """
    params = {"id_list": ["ABC", "DEF", "GHI"]}
    sqla_text = text(sql_query).bindparams(bindparam("id_list", expanding=True)).params(params)
    result = session.execute(sqla_text)
    df = pd.read_sql(sqla_text, session.get_bind())

[sfc-gh-dszmolka]

thanks for describing the workaround ! i see that at this moment this enhancement is not a priority, so please if anyone feels submitting a PR for it, you're more than welcome. Thank you in advance !