Use Ipython-sql with snowflake and externalbrowser authenticator

Question

in my jupyter notebook I connect to snowflake with an externalbrowser auth like so:

conn = snowflake.connector.connect(
user='<my user>',
authenticator='externalbrowser',
account='<my account>',
warehouse='<the warehouse>')

this opens an external browser to auth and after that works fine with pandas read sql:

pd.read_sql('<a query>', conn)

want to use it with ipython sql, but when I try:

%sql snowflake://[email protected]

I get:

snowflake.connector.errors.ProgrammingError) Password is empty

well I don't have one :) any ideas how to pass this?

Answer 1

IPython-sql connection strings are SQLAlchemy URL standard, therefore you can do the following:

%load_ext sql
from sqlalchemy import create_engine
from snowflake.sqlalchemy import URL

engine = create_engine(URL(
    account = '<account>',
    user = '<user>',
    database = 'testdb',
    schema = 'public',
    warehouse = '<wh>',
    role='public',
    authenticator='externalbrowser'
))
connection = engine.connect()

This would open the external browser for authentication.