Querying Snowflake using SQLAlchemy

Question

Trying to query with SQLAlchemy against Snowflake table

from sqlalchemy import create_engine, text, Table, MetaData

engine = create_engine(
    'snowflake://{user}:{password}@{account_identifier}/'.format(
        user="",
        password="",
        account_identifier=""
    )
)
try:
    # Create a metadata object
    metadata = MetaData()

    # Create a session
    session = engine.connect()

    # Set Database for session
    session.execute(text('use database SNOWFLAKE_SAMPLE_DATA'))

    # Reflect the table from the database
    table = Table('ORDERS', metadata, autoload_with=engine, schema='TPCH_SF1')


    # Query the table
    orders = session.query(table).all()

This throws error

snowflake.connector.errors.ProgrammingError: 001059 (22023): SQL compilation error: Must specify the full search path starting from database for TPCH_SF1

Answer 1

This is the code that worked for me.

engine = create_engine(
    'snowflake://{user}:{password}@{account_identifier}/{database}/'.format(
        user="",
        password="",
        account_identifier="",
        database='SNOWFLAKE_SAMPLE_DATA'
    )
)
try:
    # Create a metadata object
    metadata = MetaData()

    # Create a session
    session = engine.connect()

    # Reflect the table from the database
    table = Table('ORDERS', metadata, autoload_with=engine, schema='TPCH_SF1')

    # Query the table
    stmt = select(table).where(table.c.o_orderdate == "1996-05-05")

    for row in session.execute(stmt):
        print("o_orderkey: ", row.o_orderkey)
        print("o_orderstatus: ", row.o_orderstatus)
        print("---------")

finally:
    session.close()
    engine.dispose()