Question

Rows not getting added to Snowflake table using Python connector

I am trying to create and load a table in Snowflake using Python. The data is in a pandas data frame. Below is the code I'm using. The table gets created, but it has 0 rows and there's no error message shown.

with engine.connect() as con:
            data.head(0).to_sql(name=table_name, con=con, if_exists="replace", index=False)
            query="put file://" + file_path + "* @%" + table_name
            con.execute(text(query))
            query2="copy into " + table_name + " ON_ERROR=CONTINUE"
            con.execute(text(query2))

Some notes:

  • I had initially used the f string approach, but for some reason it was showing a "not executable object" error, so I changed that up to use text()
  • When I run the exact same code on a different computer, it works as expected and loads rows in the table, so I don't quite understand what could be happening here
 3  36  3
1 Jan 1970

Solution

 3

Moving the answer from comment to answer for closure:

You need to commit: conn.commit() or use with engine.begin() as conn: to commit automatically.

(@snakecharmerb I can delete this answer if you want to provide one owned by you)

2024-07-18
Felipe Hoffa

Solution

 0

The issue you're facing is likely due to the way you're trying to execute the COPY command. The COPY command in Snowflake is used to load data from a file or files into a table. However, your current code is trying to execute it as a query, which is not the correct way.

import pandas as pd
import snowflake.connector

df = pd.DataFrame({'column1': [1, 2, 3], 'column2': ['a', 'b', 'c']})

cnx = snowflake.connector.connect(
    user='your_username',
    password='your_password',
    account='your_account',
    warehouse='your_warehouse',
    database='your_database',
    schema='your_schema'
)

cursor = cnx.cursor()

cursor.execute("CREATE TABLE IF NOT EXISTS " + table_name + " (column1 VARCHAR, column2 VARCHAR)")

df.to_csv('temp.csv', index=False)

cursor.execute("COPY INTO " + table_name + " FROM '@/temp.csv' FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',')")

cursor.close()
cnx.close()

This code creates a table if it doesn't exist, writes the dataframe to a temporary csv file, and then uses the COPY command to load the csv file into the table. The FILE_FORMAT option specifies that the file format is CSV and that the field delimiter is a comma.

Note that you need to replace 'your_username', 'your_password', 'your_account', 'your_warehouse', 'your_database', and 'your_schema' with your actual Snowflake credentials and parameters.

2024-07-19
user26405163