SNOW-977384: `write_pandas` does not seem to re-use the provided open connection.

Python version

3.8.17

Operating system and processor architecture

macOS-13.5-arm64-arm-64bit

Installed packages

asn1crypto==1.5.1
attrs==23.1.0
certifi==2023.11.17
cffi==1.16.0
charset-normalizer==3.3.2
cryptography==41.0.5
fancycompleter==0.9.1
filelock==3.13.1
idna==3.4
numpy==1.24.4
packaging==23.2
pandas==2.0.3
pdbpp==0.10.3
platformdirs==3.11.0
pyarrow==14.0.1
pycparser==2.21
Pygments==2.17.2
PyJWT==2.8.0
pyOpenSSL==23.3.0
pyrepl==0.9.0
python-dateutil==2.8.2
pytz==2023.3.post1
requests==2.31.0
six==1.16.0
snowflake-connector-python==3.5.0
sortedcontainers==2.4.0
tomlkit==0.12.3
typing_extensions==4.8.0
tzdata==2023.3
urllib3==1.26.18
wmctrl==0.5

What did you do?

$ cat good.py 
import os

import snowflake.connector

conn = snowflake.connector.connect(
    user=os.environ.get("SF_USERNAME"),
    password=os.environ.get("SF_PASSWORD"),
    account=os.environ.get("SF_ACCOUNT"),
    warehouse=os.environ.get("SF_WAREHOUSE"),
    database=os.environ.get("SF_DATABASE"),
    schema=os.environ.get("SF_SCHEMA"),
    role=os.environ.get("SF_ROLE"),
)

conn.cursor().execute(f"USE DATABASE {os.environ.get('SF_DATABASE')}")
conn.cursor().execute(f"USE SCHEMA {os.environ.get('SF_SCHEMA')}")
conn.cursor().execute("CREATE TEMPORARY TABLE test (a int, b int, c int)")
conn.cursor().execute("INSERT INTO test VALUES (1, 2, 3), (4, 5, 6)")
print(conn.cursor().execute("SELECT * FROM test").fetchall())

the simple script above behaves as expected, i.e. it is able to:

1. create the temporary table named test.
2. insert the dummy data into the aforementioned temporary table.
3. and pull the data from it.

however, when the write portion of this script has been switched out with write_pandas it fails with this:

Traceback (most recent call last):
  File "bad.py", line 34, in <module>
    write_pandas(conn, df, "test", table_type="temp")
  File "/Users/chan.kang/test/snowflake-connector/venv/lib/python3.8/site-packages/snowflake/connector/pandas_tools.py", line 433, in write_pandas
    copy_results = cursor.execute(copy_into_sql, _is_internal=True).fetchall()
  File "/Users/chan.kang/test/snowflake-connector/venv/lib/python3.8/site-packages/snowflake/connector/cursor.py", line 920, in execute
    Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
  File "/Users/chan.kang/test/snowflake-connector/venv/lib/python3.8/site-packages/snowflake/connector/errors.py", line 290, in errorhandler_wrapper
    handed_over = Error.hand_to_other_handler(
  File "/Users/chan.kang/test/snowflake-connector/venv/lib/python3.8/site-packages/snowflake/connector/errors.py", line 345, in hand_to_other_handler
    cursor.errorhandler(connection, cursor, error_class, error_value)
  File "/Users/chan.kang/test/snowflake-connector/venv/lib/python3.8/site-packages/snowflake/connector/errors.py", line 221, in default_errorhandler
    raise error_class(
snowflake.connector.errors.ProgrammingError: 001757 (42601): 01b0876d-0403-d123-0030-1f830aa032c6: SQL compilation error:
Table '"test"' does not exist

it should be reproducible via:

$ cat bad.py
import os

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

conn = snowflake.connector.connect(
    user=os.environ.get("SF_USERNAME"),
    password=os.environ.get("SF_PASSWORD"),
    account=os.environ.get("SF_ACCOUNT"),
    warehouse=os.environ.get("SF_WAREHOUSE"),
    database=os.environ.get("SF_DATABASE"),
    schema=os.environ.get("SF_SCHEMA"),
    role=os.environ.get("SF_ROLE"),
)

conn.cursor().execute(f"USE DATABASE {os.environ.get('SF_DATABASE')}")
conn.cursor().execute(f"USE SCHEMA {os.environ.get('SF_SCHEMA')}")
conn.cursor().execute("CREATE TEMPORARY TABLE test (a int, b int, c int)")
df = pd.DataFrame(columns=["a", "b", "c"], data=[[1, 2, 3], [4, 5, 6]])
write_pandas(conn, df, "test")
print(conn.cursor().execute("SELECT * FROM test").fetchall())

write_pandas call fails to find the temporary table that was created right before.

What did you expect to see?

included in the "what did you do"