snowflake query works with cli but not python snowflake connector

Question

#!/bin/bash

query="""
select table_name,table_schema
from CDP.information_schema.tables
where table_schema != 'INFORMATION_SCHEMA';
"""

snowsql -c latchsnowflake -d ${dbname} -s ${schema} -r ${role} -w ${warehouse} -q "${query}"

that runs.

The equivalent command using snowflake-connector-python

with the same credentials does not find the database. When I inspect the query in the history through the UI I can see that the warehouse is not being used.

I create the connection and run the query like this:

#!/usr/bin/env python
conn = snowflake.connector.connect(
                user=self.SNOWFLAKE_USER,
                password=self.SNOWFLAKE_PASSWORD,
                account=self.SNOWFLAKE_ACCOUNT,
                warehouse=self.SNOWFLAKE_WAREHOUSE,
                database=self.SNOWFLAKE_DATABASE,
                schema=self.SNOWFLAKE_SCHEMA,
                )        
query = """
    select table_name,table_schema
    from CDP.information_schema.tables
    where table_schema != 'INFORMATION_SCHEMA';
    """
cur = conn.cursor()
cur.execute(query)
print(cur.fetchall())
cur.close()

The error, however, is the following:

snowflake.connector.errors.ProgrammingError: 002003 (02000): SQL compilation error:
Database 'CDP' does not exist or not authorized.

Answer 1

In your SnowSQL version, you are specifying a role. However, in your python you are not. Make sure the user's default role is the same as the one that you are using in your SnowSQL connection.