Unable to specify database when declaring tables in sqlalchemy

[akravetz]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using (python --version)? Python 3.7.3

2. What operating system and processor architecture are you using (python -c 'import platform; print(platform.platform())')? Linux-4.15.0-1047-aws-x86_64-with-debian-9.11

3. What are the component versions in the environment (pip list)? relevant list:

snowflake-connector-python 2.0.3
snowflake-sqlalchemy       1.1.16
SQLAlchemy                 1.3.10

4. What did you do? When using snowflake, our org often join tables across databases. For example, each user may have their own dev_${USERNAME} database instance where they test their ETL jobs, but for efficiency reasons, we keep a single copy of our prod data in the single dev database. In this case, a user may join dev_akravetz.etl_schema.etl_table to dev.prod_replica_schema.prod_table.

In order to use sqlalchemy's join function, we must define the Table object for the two tables above. The snowflake-sqlalchemy documentation does not show any examples of defining tables WITH the database. In all cases, a default database is specified. It is unclear how to specify two tables from different databases.

When defining the table objects in sqlalchemy, there are two options:

1. Full table specification (ie create a Table object, specifying all of the Columns). Here there is no way to specify the database where the table is stored. One can use the hack of specifying Table(..., schema="database.schema", ...), but this seems like a bad idea?

2. Table reflection using autoload=True. Here there is no way to specify a database given the way the connector queries the information_schema.columns table (see here).

What is the preferred way to join tables across databases?

5. What did you expect to see? N/A

6. What did you see instead? N/A

7. Can you set logging to DEBUG and collect the logs? N/A

[dusty-campanella-maxa]

Hi @akravetz,

If you are looking to use multiple databases, here is a way to configure it with sqlalchemy : https://flask-sqlalchemy.palletsprojects.com/en/2.x/binds/.

__bind_key__ = 'MY_DATABASE' specifies the database you are using for your model

Dusty

[akravetz]

Hi @akravetz,

If you are looking to use multiple databases, here is a way to configure it with sqlalchemy : https://flask-sqlalchemy.palletsprojects.com/en/2.x/binds/.

__bind_key__ = 'MY_DATABASE' specifies the database you are using for your model

Dusty

Hi Dusty, thank you for the quick reply! From reading through the document you linked to and the underlying source code, it looks like flask-sqlalchemy uses some custom code to create a new engine for each database. Since tables from different schemas are actually bound to different engines, you can't actually use tables created with different __bind_key__'s together. Let me know if I'm missing something!

[machow]

Hey all--it seems like sqlalchemy-bigquery resolved this issue, by allowing a "fully qualified" schema name, like <database>.<schema>.

See these docs: https://github.com/googleapis/python-bigquery-sqlalchemy#table-names

Here's the relevant code:

# If neither dataset nor project are the default
sample_table_1 = Table('natality', schema='bigquery-public-data.samples')

It appears the snowflake dialect just prepends the default database to the specified schema, so it's impossible to point at the non-default database...

[yassernadeem]

@akravetz @machow Were either of you ever able to resolve this or find a workaround? I'd love to be able to access multiple DBs within one query using sqlalchemy to define the query.

[akravetz]

@akravetz @machow Were either of you ever able to resolve this or find a workaround? I'd love to be able to access multiple DBs within one query using sqlalchemy to define the query.

I was not, we opted to avoid sqlalchemy since migrating to snowflake

[github-actions[bot]]

To clean up and re-prioritize bugs and feature requests we are closing all issues older than 6 months as of Apr 1, 2023. If there are any issues or feature requests that you would like us to address, please re-create them. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response