stackoverflow-68323279-writing-dataframe-to-snowflake-error-binding.py

# https://stackoverflow.com/questions/68323279/writing-dataframe-to-snowflake-error-binding-data-in-type-timestamp-is-not-su
# https://community.snowflake.com/s/question/0D50Z00009UB5aPSAT/how-to-connect-using-sqlalchemy-with-okta-idp

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

# pip install --upgrade snowflake-sqlalchemy pandas 
# pip install --upgrade snowflake-connector-python


cred = {
      'a': '######'
    , 'u' : 'username'
    , 'p': 'password'
    , 'd':'DatbaseName'
    , 'w': 'WarehouseName'
    , 'r': 'RoleName'
}

# you will need to work through the idp / externalbrowser authentication

# https://community.snowflake.com/s/question/0D50Z00009UB5aPSAT/how-to-connect-using-sqlalchemy-with-okta-idp
# engine = create_engine(URL(
#     account='ACCOUNT',
#     user='OKTA_USER',
#     password='OKTA_PASSWORD',
#     ),
#     connect_args={
#         'authenticator': 'https://something.okta.com/',
#         },
#     )

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('/home/username/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()