SNOW-1943408: Unexpected behaviour when using mixed case table names with write_pandas

[jeremycg]

Python version

Python 3.11.9 (main, Aug 14 2024, 04:17:21) [Clang 18.1.8 ]

Operating system and processor architecture

macOS-14.7.3-arm64-arm-64bit

Installed packages

anyio==4.8.0
appnope==0.1.4
argon2-cffi==23.1.0
argon2-cffi-bindings==21.2.0
arrow==1.3.0
asttokens==3.0.0
async-lru==2.0.4
attrs==25.1.0
babel==2.17.0
beautifulsoup4==4.13.3
bleach==6.2.0
certifi==2025.1.31
cffi==1.17.1
charset-normalizer==3.4.1
comm==0.2.2
debugpy==1.8.12
decorator==5.1.1
defusedxml==0.7.1
executing==2.2.0
fastjsonschema==2.21.1
fqdn==1.5.1
h11==0.14.0
httpcore==1.0.7
httpx==0.28.1
idna==3.10
ipykernel==6.29.5
ipython==8.32.0
ipywidgets==8.1.5
isoduration==20.11.0
jedi==0.19.2
jinja2==3.1.5
json5==0.10.0
jsonpointer==3.0.0
jsonschema==4.23.0
jsonschema-specifications==2024.10.1
jupyter==1.1.1
jupyter-client==8.6.3
jupyter-console==6.6.3
jupyter-core==5.7.2
jupyter-events==0.12.0
jupyter-lsp==2.2.5
jupyter-server==2.15.0
jupyter-server-terminals==0.5.3
jupyterlab==4.3.5
jupyterlab-pygments==0.3.0
jupyterlab-server==2.27.3
jupyterlab-widgets==3.0.13
markupsafe==3.0.2
matplotlib-inline==0.1.7
mistune==3.1.2
nbclient==0.10.2
nbconvert==7.16.6
nbformat==5.10.4
nest-asyncio==1.6.0
notebook==7.3.2
notebook-shim==0.2.4
overrides==7.7.0
packaging==24.2
pandocfilters==1.5.1
parso==0.8.4
pexpect==4.9.0
platformdirs==4.3.6
prometheus-client==0.21.1
prompt-toolkit==3.0.50
psutil==7.0.0
ptyprocess==0.7.0
pure-eval==0.2.3
pycparser==2.22
pygments==2.19.1
python-dateutil==2.9.0.post0
python-json-logger==3.2.1
pyyaml==6.0.2
pyzmq==26.2.1
referencing==0.36.2
requests==2.32.3
rfc3339-validator==0.1.4
rfc3986-validator==0.1.1
rpds-py==0.23.0
send2trash==1.8.3
setuptools==75.8.0
six==1.17.0
sniffio==1.3.1
soupsieve==2.6
stack-data==0.6.3
terminado==0.18.1
tinycss2==1.4.0
tornado==6.4.2
traitlets==5.14.3
types-python-dateutil==2.9.0.20241206
typing-extensions==4.12.2
uri-template==1.3.0
urllib3==2.3.0
wcwidth==0.2.13
webcolors==24.11.1
webencodings==0.5.1
websocket-client==1.8.0
widgetsnbextension==4.0.13

What did you do?

The write_pandas function does not behave as expected. When trying to write to a previous created table, the table name fails if the table was given in mixed case.

The table create works in mixed case, as do other queries, but the write_pandas() fails.

What did you expect to see?

Here's the runnable code.
this fails, with a "table does not exist error".

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


conn_params = {
    "user": "XXX",
    "password": "***",
    "account": "xxx",
    "role": "xxx",
    "database": "xxx",
    "warehouse": "xxx",
    "authenticator": "externalbrowser",
    "schema": "xx",
    "insecure_mode": True
}

conn = snowflake.connector.connect(**conn_params)
df = pd.DataFrame({"USER_ID":['abcd', 'efgh']})

table_id =  "TEMP_USER_IDS_"+ '123abcd' #i was using +uuid.uuid4().hex
conn.execute_string(f"CREATE OR REPLACE TEMPORARY TABLE {table_id} (USER_ID VARCHAR)")
success, nchunks, nrows, _ = write_pandas(conn, df[['USER_ID']], f'{table_id}', auto_create_table=False)

If we use consistent cases, we are fine:

table_id =  "TEMP_USER_IDS_"+ '123ABCD'
conn.execute_string(f"CREATE OR REPLACE TEMPORARY TABLE {table_id} (USER_ID VARCHAR)")
success, nchunks, nrows, _ = write_pandas(conn, df[['USER_ID']], f'{table_id}', auto_create_table=False)

I expect to see consistent behaviour:
If we are not going to allow mixed case table names, we should error on creation and/or query, not only on the write_pandas call.

Can you set logging to DEBUG and collect the logs?

import logging
import os

for logger_name in ('snowflake.connector',):
    logger = logging.getLogger(logger_name)
    logger.setLevel(logging.DEBUG)
    ch = logging.StreamHandler()
    ch.setLevel(logging.DEBUG)
    ch.setFormatter(logging.Formatter('%(asctime)s - %(threadName)s %(filename)s:%(lineno)d - %(funcName)s() - %(levelname)s - %(message)s'))
    logger.addHandler(ch)

[sfc-gh-sghosh]

Hello @jeremycg ,

Thanks for raising the issue.
It seems this is an application usage issue. For any object in Snowflake if its in mixed case, you need to use quotes to preserve it else by default it will be in capital letter and thats why the 2nd scenario is working for you when you used all capital letters for the object name.

table_id = "TEMP_USER_IDS_"+ '123ABCD'

Also, you creating temporary table, these doesnt exist beyond session, so you have to use auto_create_table=True
Temporary table can't be existing table.

I am able to create the table and insert the data via the dataframe successfully in mixed table name.

`conn = snowflake.connector.connect(**conn_params)
print("Got python connection")

df = pd.DataFrame({"USER_ID":['abcd', 'efgh']})

table_id = "TEMP_USER_IDS_"+ '123abcd1' #i was using +uuid.uuid4().hex
print("table_id :",table_id)
conn.execute_string(f"CREATE OR REPLACE TEMPORARY TABLE {table_id} (USER_ID VARCHAR)")
success, nchunks, nrows, _ = write_pandas(conn, df[['USER_ID']], f'{table_id}', auto_create_table=True)
print("Written")

output:
Got python connection
table_id : TEMP_USER_IDS_123abcd1
Written
`

If you will use regular existing table then you can use auto_create_table=False.

Regards,
Sujan