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

Question

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.

The easiest way to troubleshoot is to use Activity/Query History and compare the CREATE TABLE ... vs SELECT *..... Possible options: 1) Different sessions for create and read(temporary table lives as long as active session) 2) quoted identifier ", the table name should match exactly the same as during CREATE TABLE 3) lack of schema, the select statement tries to read from different schema — Lukasz Szozda
– Lukasz Szozda, Commented Jan 20, 2023 at 22:13

Alexander Klimenko · Accepted Answer · 2023-01-21 09:11:38Z

1

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.

answered Jan 21, 2023 at 9:11

Alexander Klimenko

1,7252 gold badges13 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related