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

Question

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 1

I don't know what is wrong with #2, but I found out that combining #1 and #2 will work...

import snowflake.connector
from sqlalchemy import create_engine

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

engine = create_engine(f"snowflake://{acct}.snowflakecomputing.com", creator=lambda: conn)

try:
    with engine.connect() as c:
        # Note: the table name MUST be all lowercase for some reason, even though Snowflake uses all caps
        df.to_sql('TABLENAME'.lower(), con=c, if_exists='replace', index=False)
finally:
    engine.dispose()
    conn.close()