Writing Dataframe to SnowFlake error: Binding data in type (timestamp) is not supported

Question

I have a Pandas' dataframe that I'm trying to write back to Snowflake. However, when trying to do so, I'm getting the following error:

(snowflake.connector.errors.ProgrammingError) 252004: Failed processing pyformat-parameters: 255001: Binding data in type (timestamp) is not supported.

I've tried to convert my date columns using .dt.to_pydatetime() but still no luck.

Here is what my code looks like

#write data back into table
# Fill in your SFlake details here
engine = create_engine(URL(
    account = '######',
    user = 'mike',
    authenticator = 'externalbrowser',
    database = 'DatbaseName',
    schema = 'SchemaName',
    warehouse = 'WarehouseName',
    role='RoleName',
))
 
connection = engine.connect()


#FIX ATTEMPT: Binding data in type (timestamp) is not supported in Snowflake, here is the work around: 
df['month_end'] = df['month_end'].dt.to_pydatetime()
df['NEW_CUST_DT'] = df['NEW_CUST_DT'].dt.to_pydatetime()

df.to_sql('engagement', con=engine, index=False, if_exists='append') #make sure index is False, Snowflake doesnt accept indexes
 
connection.close()
engine.dispose()

Dataframe details for reference:

Answer 1

Here is the code in downloadable format.

I am using csv stock data that you can get from yahoo api, given that you didnt provide any data or code for easily reproducing your issue.

month_end and NEW_CUST_DT are added to the data to match conversion you do.

I suspect you may have a type mismatch on your table, or something is wrong with your source data.

try sending the data to a new table to see if that works. if it does, either drop and recreate your table, or modify your dataframe to match up. Which strategy you take will depend on how mature your code base is and your use case .

The types from my dataframe are:

date           datetime64[ns]
open                  float64
high                  float64
low                   float64
close                 float64
adjclose              float64
volume                  int64
ticker                 object
month_end      datetime64[ns]
NEW_CUST_DT    datetime64[ns]

The code to test the upload, which works for me is:

import pandas as pd
import sqlalchemy as sa
import pandas as pd
from sqlalchemy import *
import snowflake.connector


def savedfToSnowflake(df):
    engine = sa.create_engine(
        'snowflake://{user}:{password}@{account}/'.format(
            user=cred['u'],
            password=cred['p'],
            account=cred['a'],
        )
    )
    try:
        connection = engine.connect()
        print("Connected to Snowflake ")
        df.to_sql('stockprice', con=engine, index=False,
                  if_exists='append')  # make sure index is False, Snowflake doesnt accept indexes
        print("Successfully saved data to snowflake")
    except Exception as ex:
        print("Exception occurred {}".format(ex))
    finally:
        # close connection
        if connection:
            connection.close()
        if engine:
            engine.dispose()


def getData():
    df = pd.read_csv('/sampledata/AA.csv')
    df = df.tail(20)
    df['date'] = pd.to_datetime(df['date'])
    df['month_end'] = df['date'].dt.to_pydatetime()
    df['NEW_CUST_DT'] = df['date'].dt.to_pydatetime()
    print(df.dtypes)
    return df


def main():
    df = getData()
    savedfToSnowflake(df)


if __name__ == '__main__':
    main()

My resulting table is

create or replace TABLE ENGAGEMENT (
DATE TIMESTAMP_NTZ(9),
OPEN FLOAT,
HIGH FLOAT,
LOW FLOAT,
CLOSE FLOAT,
ADJCLOSE FLOAT,
VOLUME NUMBER(38,0),
TICKER VARCHAR(16777216),
MONTH_END TIMESTAMP_NTZ(9),
NEW_CUST_DT TIMESTAMP_NTZ(9));

Results Validated are: