Queries working in snowflake web UI but not consistently through the python sqlalchemy connector

0
votes

I have a sqlalchemy connection setup to snowflake which works, as I can run some queries and get results out. The attempts to query are also logged in my user_query history.

My connection:

engine = create_engine(URL(
  user, password, account, database, warehouse, role
))

connection = engine.connect()

However, most of the time my queries fail returning Operational Error (i.e. its a snowflake error) https://docs.sqlalchemy.org/en/13/errors.html#error-e3q8. But these same queries will run fine in the snowflake web UI.

For example if I run

test_query = 'SELECT * FROM TABLE DB1.SCHEMA1.TABLE1'
test = pd.read_sql(test_query, connection)

When I look at my query_history it shows the sqlalchemy query failing, then a second later the base query itself being run successfully. However I'm not sure where this output goes in the snowflake setup, and why its not transferring through my sqlalchemy connection. What I'm seeing...

  • Query = 'DESC TABLE /* sqlalchemy:_has_object */ "SELECT * FROM DB1"."SCHEMA1"."TABLE1"

    Error code = 2003 Error message = SQL compilation error: Database '"SELECT * FROM DB1" does not exist.

Then 1 second later, the query itself will run successfully, but not clear where this goes as it doesn't get sent over the connection.

  • Query = SELECT * FROM TABLE DB1.SCHEMA1.TABLE1

Any help much appreciated! Thanks

3
pythonmysqlsqlalchemysnowflake-cloud-data-platform
Can you try using only "test_query = 'SELECT * FROM TABLE1'" since you have already mentioned the database and schema in the connection? – Abhi Reddy
I gave that a try and still facing the same problem unfortunately – Bex

3 Answers

0
votes

You can try adding schema also here 

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

connection = engine.connect()
0
votes

It is very unlikely that the query is running in WebUI and fails with syntax error when connected via CLI or other modes.

Suggest you print the query which is via CLI or via a connector, run the same to WebUI and also note that from which role you're running the query.

Please share what is your finding.

0
votes

The mentioned query (SELECT * FROM TABLE DB1.SCHEMA1.TABLE1) is not a snowflake supported SQL syntax.

Link here will help you more with details. Hope this helps!