Search code examples
pythonsnowflake-cloud-data-platformsnowflake-connector

How to do a PUT and Copy INTO Snowflake with Python

I am needing to know how to execute a PUT file and COPY INTO from Python. I know write_pandas will write data into Snowflake directly, and does a PUT and COPY INTO behind the scenes, but how would I accomplish this myself "manually" in Python?

Just looking for a good example that stages files and copies them into an existing VARIANT column for the XML data. When I tried write_pandas it inserted quotation marks around each row which screws up the casting of columns in Select statements later in Snowflake. To get rid of the quotation marks I tried adding quote_identifiers=False but it still writes the data in wrapped in quotes:

pandasDF = pd.DataFrame(rowData)
pandasDF.columns =['DATACOLUMN']

from snowflake.connector.pandas_tools import write_pandas

success, nchunks, nrows, _ = write_pandas(engine, pandasDF, 'OI_PNET_GET_PERFORMX_UPL2', quote_identifiers=False)

Here is what it looks like after insertion with starting and trailing quotes: enter image description here

Thanks.

Solution

  • Example code below

    with stage_engine.connect() as con:
 con.execute("rm @{}.%{}".format(target_schema,table_name))
 con.execute("put file://{}* @{}.%{}".format(tmp_dir+'/test.csv',target_schema,table_name))
 con.execute("copy into {} file_format = (type = csv FIELD_DELIMITER='~' field_optionally_enclosed_by='\"')".format(full_table_name))