jh1111111
jh1111111

Reputation: 23

SQLAlchemy Connection to Snowflake with Azure AD Authentication

I'm trying to use the below code to connect to my snowflake account using SQL alchemy. It's not working and my guess is that this is because we log in using Azure AD authentication. Any help appreciated.

from sqlalchemy import create_engine
    
    engine = create_engine(
        'snowflake://{user}:{password}@{account}/'.format(
            user='xxx',
            password='xxx',
            account='xxx'
            
        )
    )
    try:
        connection = engine.connect()
        results = connection.execute('select current_version()').fetchone()
        print(results[0])
    finally:
        connection.close()
        engine.dispose()

The error message:

DatabaseError: (snowflake.connector.errors.DatabaseError) 250001 (08001): Failed to connect to DB: QB67303.eu-west-1.snowflakecomputing.com:443. Incorrect username or password was specified.
(Background on this error at: http://sqlalche.me/e/13/4xp6)

Upvotes: 0

Views: 1681

Answers (2)

jh1111111
jh1111111

Reputation: 23

This is what I went with in the end. Thanks to Mike Walton for getting me on the right track.

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine

engine = create_engine(URL(
    account = 'x',
    user = 'x',
    password = 'x',
    database = 'x',
    schema = 'x',
    warehouse = 'x',
    role='x',
    authenticator='externalbrowser'
))

try:
        connection = engine.connect()
        sql_df = pd.read_sql(
                                "Select top 1 * from a.b",
                                con=engine
                                )
        
finally:
        connection.close()
        engine.dispose()

Upvotes: 1

user1369384
user1369384

Reputation: 111

Though this is not with sqlalchemy but with the native snowflake sql connector, this works for me:

import snowflake.connector

ctx = snowflake.connector.connect(
    user='<YOUR_USERNAME>',
    account='<YOUR_ACCOUNT ; not always needed>',
    host='<YOUR SF host, ex: xxxxxx.snowflakecomputing.com / xxxxxx.a0.satoricyber.net>',
    authenticator='externalbrowser',
    warehouse='<optional>'
    )
cs = ctx.cursor()
try:
    cs.execute("SELECT * FROM ORGDATA.PUBLIC.CUSTOMERS")
    rows = cs.fetchall()
    for row in rows:
        print(row)
finally:
    cs.close()
ctx.close()

Upvotes: 2

Related Questions