SNOW-522645: Passing session_parameters in a url cause an internal error inside the Snowflake connector

[njriasan]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using (python --version)?

3.8.11

2. What operating system and processor architecture are you using (python -c 'import platform; print(platform.platform())')?

macOS-10.16-x86_64-i386-64bit

3. What are the component versions in the environment (pip list)?

I'm using an anaconda environment.

snowflake-connector-python 2.7.2 py38h27c1234_0 conda-forge snowflake-sqlalchemy 1.3.3 pyhd8ed1ab_0 conda-forge sqlalchemy 1.4.23 pypi_0 pypi

4. What did you do? If possible, provide a recipe for reproducing the error. A complete runnable program is good.

I tried to run a simple read_query using a Pandas query with session parameters included. The error is simply a parsing error so I have filled the session parameter with a dummy value.

import os

username = os.environ["SF_USER"]
password = os.environ["SF_PASSWORD"]
account = "my_snowflake_account"
db = "SNOWFLAKE_SAMPLE_DATA"
schema = "TPCH_SF1"
connection_params = {
        "warehouse": "DEMO_WH",
        # Here is the issue 
        "session_parameters": json.dumps({"DUMMY": 0}),
        "paramstyle": "pyformat",
        "insecure_mode": True,
}
conn = f"snowflake://{username}:{password}@{account}/{db}/{schema}?{urllib.parse.urlencode(connection_params)}"

query = "SELECT * FROM LINEITEM ORDER BY L_ORDERKEY, L_PARTKEY, L_SUPPKEY LIMIT 70"

df = pd.read_sql(query, conn)
print(df)

5. What did you expect to see?

The table should be successfully loaded without any issues.

6. What did you see instead?

An internal error inside the snowflake connector.

        if self._autocommit is not None:
>           self._session_parameters[PARAMETER_AUTOCOMMIT] = self._autocommit
E           TypeError: 'str' object does not support item assignment

Earlier in this file the snowflake connector states that this parameter should either be a dictionary or None. I believe this parameter needs to be converted from a json string back to a dictionary before the Snowflake connection is created.

[philippeboyd]

@njriasan I had the same issue where I wanted to use SqlAlchemy and set, for instance, query_tag for my session.

Turns out I had to reverse engineer this library because there's no doc about this.

This issue on snowflake-connector-python helped me to find this workaround:

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine

engine = create_engine(
    url=URL(
        account='my_snowflake_account',
        user='user_1',
        password="something1?",
        database="app_1",
        warehouse="app_1",
        role="dev",
    ),
    connect_args=dict(
        session_parameters=dict(
            timezone='America/Toronto',
            query_tag='test',
        )
    )
)

with engine.connect() as connection:
    print(connection.execute('select current_version()').fetchone()[0])
    print(connection.execute('select current_timestamp()').fetchone()[0])

engine.dispose()

It's a shame that snowflake-connector-python can't understand all URL parameters that are set for Snowflake such as

URL(
    account='my_snowflake_account',
    user='user_1',
    password="something1?",
    database="app_1",
    warehouse="app_1",
    role="dev",
    timezone='America/Toronto',  # Will be set in snowflake-connector-python
    query_tag='test',  # Will NOT be set in snowflake-connector-python
)

Here's what their connect() function understands : https://docs.snowflake.com/en/user-guide/python-connector-api.html#connect

Not only it's missing the session_parameters option in the doc but it'll never recognize a valid Snowflake URL with almost all of the snowflake parameters

There's a missing integration between this repo and snowflake-connector-python

[github-actions[bot]]

To clean up and re-prioritize bugs and feature requests we are closing all issues older than 6 months as of Apr 1, 2023. If there are any issues or feature requests that you would like us to address, please re-create them. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response