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.

Answer

The problem with #2 is that you missed a closure. The connect_args is part of the sqlalchemy.crate_engine call, not the snowflake.sqlalchemy.URL one.

Corrected code (as documented at the snowflake-sqlalchemy GitHub README):

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