[Python] SQLAlchemy engine.connect() fails for Snowflake using keypair authentication

My goal is to be able to write a pandas dataframe to Snowflake using the to_sql() function. I am able to successfuly query from Snowflake using read_sql() with no problems. It appears that to_sql() requires a slightly different configuration I can't figure out. I've tried two different approaches that I've outlined below.

I'm guessing, but could be wrong that the keypair authentication seems to be the problem. When I remove the private key parameter in both approaches, I get an error that the password is missing, rather than the particularly unclear errors I get otherwise. Perhaps not having the password specified is short circuiting the attempt and I really have another problem.

Alternately, if there is a better way to write an entire pandas dataframe to Snowflake I'm happy to entertain other options.

Method 1 Snowflake Connector


edit: it appears that sqllite is the only database supported if the connection isn't a SQLAlchemy connection.

This approach fails with DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting

import snowflake

conn = snowflake.connector.connect(
account=acct,
warehouse=wh,
database=db,
schema=sc,
user=usr,
private_key=pkb,
)

df.to_sql('TABLENAME', con=conn, if_exists='replace', index=False)

Method 2 - SQLAlchemy Engine


I've found documentation on how to use a private key with SQLAlchemy. However, it fails with ValueError: Invalid IPv6 URL on the call to engine.connect()...

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine

engine = create_engine(URL(
account=acct,
warehouse=wh,
database=db,
schema=sc,
user=usr,
connect_args={
'private_key': pkb,
}
))

with engine.connect() as conn:
df.to_sql('TABLENAME', con=conn, if_exists='replace', index=False)


The traceback for both these errors are very long, but I'm happy to post them if it would help troubleshoot.

Continue reading...