1

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://conn.user@conn.account

I get:

snowflake.connector.errors.ProgrammingError) Password is empty

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

Ezer K
  • 3,637
  • 3
  • 18
  • 34

1 Answers1

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.

Sergiu
  • 4,039
  • 1
  • 13
  • 21
  • 1
    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 – Ezer K Nov 09 '21 at 08:54
  • @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? – JayRoar Mar 11 '22 at 15:34
  • 1
    **pre op:** pip install ipython-sql **1st cell:** %load_ext sql **2nd cell:** %sql snowflake://:@/?authenticator=externalbrowser&warehouse= **3rd cell:** %sql select current_time (just an example) – Ezer K Mar 12 '22 at 20:12
  • @Ezer K: Thanks for this. It is working for me now. – JayRoar Mar 14 '22 at 17:37
  • 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. – Ido Michael Mar 18 '23 at 15:13