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?

Sergiu · Accepted Answer · 2021-11-09 08:18:45Z

2

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.

answered Nov 9, 2021 at 8:18

Sergiu

4,7661 gold badge16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ezer K Over a year ago

it seems you still need to take the actual url string (output of the URL(...) fumc), but other than that using URL() solves the problem

JayRoar Over a year ago

@Ezer K: I have the same question as you. After establishing the connection as suggested by Sergiu, what was the next step to be able to run a query using %sql rather than pd.read_sql?

Ezer K Over a year ago

pre op: pip install ipython-sql 1st cell: %load_ext sql 2nd cell: %sql snowflake://<user>:@<account>/?authenticator=externalbrowser&warehouse=<warehouse> 3rd cell: %sql select current_time (just an example)

JayRoar Over a year ago

@Ezer K: Thanks for this. It is working for me now.

Ido Michael Over a year ago

This is great! You can try pip install jupysql as it has more features like visualizing the data and in-memory data. It also has bug fixes that are still missing in ipython-sql. Moreover it allows you to set multiple connection engines, and maintain it, so you wouldn't need to hardcode the connection string in your notebook.

Collectives™ on Stack Overflow

Use Ipython-sql with snowflake and externalbrowser authenticator

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related