CODEWITHSUNDEEP
pythonpandassnowflake-cloud-data-platform
trey hannam
trey hannam

Reputation: 263

Add and then query temp table from pandas with Snowflake python connector

I am trying to create a temporary table from a pandas df and then use it in a sql statement

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

with snowflake.connector.connect(
account='snoflakewebsite',
user='username',
authenticator='externalbrowser',
database='db',
schema='schema'
) as con:

    success, nchunks, nrows, _ = write_pandas(
        conn=con,                    
        df=df,
        table_name='temp_table',
        auto_create_table = True,
        table_type='temporary',
        overwrite = True,
        database='db',
        schema='schema' 
       )

    cur = con.cursor()
    cur.execute('select * from temp_table')

The error I get:

ProgrammingError: 002003 (42S02): SQL compilation error: Object 'TEMP_TABLE' does not exist or not authorized.

Upvotes: 3

Views: 1732

Answers (1)

Alexander Klimenko
Alexander Klimenko

Reputation: 1695

write_pandas() creates a table using the letter case exactly how it is passed in table_name=, while the query submitted in cur.execute() passes the entire string with the query to Snowflake SQL, and Snowflake SQL capitalizes the object names unless they are written in double quotes.

Therefore, either you create a table using capital letters table_name='TEMP_TABLE', or you query it using double quotes:

  cur.execute('select * from "temp_table"')

In this case, you will get your table created in small letters, and you always need to add double quotes to refer to its name.

Upvotes: 1

Related Questions