SNOW-534004: write_pandas does not use schema provided

[psukys]

1. What version of Python are you using?

Python 3.10.1 (main, Jan 10 2022, 00:00:00) [GCC 11.2.1 20211203 (Red Hat 11.2.1-7)]

2. What operating system and processor architecture are you using?

Linux-5.15.14-200.fc35.x86_64-x86_64-with-glibc2.34

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

asn1crypto==1.4.0
certifi==2021.10.8
cffi==1.15.0
charset-normalizer==2.0.10
cryptography==36.0.1
idna==3.3
numpy==1.22.1
oscrypto==1.2.1
pandas==1.3.5
pycparser==2.21
pycryptodomex==3.12.0
PyJWT==2.3.0
pyOpenSSL==21.0.0
python-dateutil==2.8.2
pytz==2021.3
requests==2.27.1
six==1.16.0
snowflake-connector-python==2.7.3
urllib3==1.26.8

4. What did you do?

Use write_pandas for data insertion:

from snowflake import connector
from snowflake.connector.pandas_tools import write_pandas
import pandas

schema_name = "my_schema"
table_name = "my_table"
data = pandas.DataFrame(data=[
        [1],
    ],
    columns=["my_id"])


with connector.connect(
        user="USERNAME",
        password="PASSWORD",
        account="ACCOUNT",
        warehouse="WAREHOUSE",
        database="DATABASE"
    ) as conn:
    write_pandas(conn=conn, df=data, schema=schema_name, table_name=table_name)

Error message received:

090106 (22000): Cannot perform CREATE TEMPSTAGE. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name.

5. What did you expect to see?

   Data inserted into table

6. Can you set logging to DEBUG and collect the logs?

unrelated.

write_pandas uses cursor without schema specified. Either it has to be specified during cursor creation or schema name should be specified in every call (found 3 within that function)

[psukys]

proposed changes are limited to my use of schema argument (in docs it's stated that it is optional) https://github.com/Vaultspeed/snowflake-connector-python/commit/edd30ca07aa3356033cd73fa2ac266d14850836a

[Demetrio92]

facing this issue. Without a workaround, it renders write_pandas useless

[Demetrio92]

Looked into the PR. Hotfix is indeed trivial.

In write_pandas find the line cursor = conn.cursor(), then add

cursor.execute(f'USE DATABASE {database}')
cursor.execute(f'USE SCHEMA {schema}')

[csetzkorn]

I still get this error despite specifying schema and db ... looking at the code, I think it is fixed? I use version 2.7.12.

[Demetrio92]

@csetzkorn so, the code looks alright, but still doesn't work? Looks like a bug to me.

https://github.com/snowflakedb/snowflake-connector-python/blob/0afbf902b020c99d2db47e24aa181ad2085c421c/src/snowflake/connector/pandas_tools.py#L165

Add a fix I provided here and observe that write_pandas now works as expected.

[jekaterinakletnaja]

From the code it seems that database and schema parameters are not used in the write_pandas code that is related to temporary stage - it refers only to the stage’s name. Therefore, in case the connection provided to write_pandas has no database and/or schema, execution fails, even if database and/or schema are provided as arguments to the write_pandas itself. Thus, indication of database and schema in the queries related to the temporary stage solves the issue.

Proposed changes are reflected in the PR.

UPD on 2022-10-18: The PR is approved and is expected to be in the release coming in January 2023.

[psukys]

Sounds good, I'll close the PR