Open shusson opened 2 years ago
recreate jira
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.
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.
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.
@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.
This is still an issue for us @sfc-gh-aling @sfc-gh-mkeller. Can we re-open this ticket?
@shusson , apologize for the late response, I was working on some other prioritized stuff. yes, I will check how postgres implements it
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.
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)
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())
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 !
What version of Python are you using? 3.8.13
What operating system and processor architecture are you using? macOS-12.4-x86_64-i386-64bit
What are the component versions in the environment (
pip freeze
)? snowflake-sqlalchemy==1.2.4 sqlalchemy==1.3.23What did you do?
Expected
Actual
There is a workaround which is to add the parens in the query string e.g