Python and snowflake cursor is adding extra text to alter table foreign key query which leads to not find the table itself

Question

I am building a pipeline to upload data from local folders into snowflake cloud.

The last step after uploading each table is to alter the table itself named after the CSV file name, to add a foreign key to the master table. I am doing the following:

query = """ALTER TABLE """+filename+""" ADD FOREIGN KEY (SURVEY_ID) REFERENCES SURVEYS(SURVEY_ID)"""
if(cursor.execute(query)):
    print(filename+ " FOREIGN KEY added successfully")
else:
    print(filename+" FOREIGN KEY was not added")

I have got the following error:

Table 'MY_DB.PUBLIC.SURVEY_AST' does not exist or not authorized.

I know the table name should be like above in order to be found and the query will be executed:

"MY_DB"."PUBLIC"."SURVEY_AST"

If I print the filename I will get:

SURVEY_AST

I tried to use %s but it is not possible to bind the table name itself using snowflake cursor:

query = """ALTER TABLE %s ADD FOREIGN KEY (SURVEY_ID) REFERENCES SURVEYS(SURVEY_ID)"""
if(cursor.execute(query, (filename)):
    print(filename+ " FOREIGN KEY added successfully")
else:
    print(filename+" FOREIGN KEY was not added")

How can I make the engine stop binding extra text to the table itself in order to find it and execute the query?

Take note that the previous queries are working normally (INSERT, SELECT...).

EDIT

conn = snowCtx.connect(
    user=user,
    password=password,
    account=account,
    database='MY_DB',
    schema='PUBLIC',
    warehouse='COMPUTE_WH',
    role='SYSADMIN'
)
if(success):
    print(filename+' columns uploaded')
    # UPDATE now variable
    now = date.today().strftime("%Y-%m-%d")
    # To insert the data files into the new table
    data = data.applymap(str)
    data['_xform_id'] = data['_xform_id'].apply(lambda x: int(float(x)))
    data['SURVEY_ID'] = data['SURVEY_ID'].apply(lambda x: int(str(x)))
    try:
        data.to_sql(name=filename, 
              con=engine.connect(), 
              if_exists="replace", 
              index=False)
        print(filename+' to_sql() done.')
        print("--- %s seconds ---" % (time.time() - start_time))
        log_frame.append({'Survey Name': filename, 'Survey ID': nextval}, ignore_index=True)
    except sqlalchemy.exc.Error as e:
        print("Error adding the table: "+ e)
    # Add a foreign key
    finally:
        # query = "select count (*) from information_schema.tables WHERE table_type = 'BASE TABLE' AND table_name="+filename

        print(filename)
        query = """ALTER TABLE {0}  ADD FOREIGN KEY (SURVEY_ID) REFERENCES SURVEYS(SURVEY_ID)""".format(filename.replace('\'', '\'\''))
        print(query)
        if(cursor.execute(query)):
            print(filename+ " FOREIGN KEY added successfully")
        else:
            print(filename+" FOREIGN KEY was not added")

Gokhan Atil Gokhan Atil · Accepted Answer · 2020-11-26T12:34:55

I don't think that it's adding any extra text. As I see, The error message includes the database and schema name (which is expected). For example:

create table DENEME ( i integer );

alter table DENEME2 add column z varchar;

SQL compilation error: Table 'GOKHAN_DB.PUBLIC.DENEME2' does not exist or not authorized.

Please check the query history to see the SQL text:

https://docs.snowflake.com/en/sql-reference/functions/query_history.html

Do you think that the table name should be like below?

"MY_DB"."PUBLIC"."SURVEY_AST"

It's not different than MY_DB.PUBLIC.SURVEY_AST (because all characters are uppercase). So it should work. Please check the previous commands to see how you create table (maybe you create it in a different schema).

Python and snowflake cursor is adding extra text to alter table foreign key query which leads to not find the table itself

2 Answers