Skip to content

Something seems to be mutating sqlalchemy expressions #127

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
machow opened this issue Apr 16, 2021 · 1 comment
Closed

Something seems to be mutating sqlalchemy expressions #127

machow opened this issue Apr 16, 2021 · 1 comment
Labels
api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. 🚨 This issue needs some love. triage me I really want to be triaged.

Comments

@machow
Copy link

machow commented Apr 16, 2021

Hey, thanks for all your work on this library! I'm looking into adding bigquery support to siuba--which makes heavy use of sqlalchemy--and ran into a small hitch with expressions producing different SQL when run multiple times. AFAIK these expressions should be immutable at this level of use, so I wonder if pybigquery is doing some kind of mutation under the hood?

Environment details

pip --version
pip 18.1 from /Users/machow/.virtualenvs/siuba-bigquery/lib/python3.6/site-packages/pip (python 3.6)

python --version
Python 3.6.8

pip show pybigquery
Name: pybigquery
Version: 0.6.1
Summary: SQLAlchemy dialect for BigQuery
Home-page: https://github.com/googleapis/python-bigquery-sqlalchemy
Author: The PyBigQuery Authors
Author-email: googleapis-packages@google.com
License: UNKNOWN
Location: /Users/machow/.virtualenvs/siuba-bigquery/lib/python3.6/site-packages
Requires: sqlalchemy, google-cloud-bigquery, google-auth, future
Required-by: 

Steps to reproduce

Run the following code to setup:

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, String, MetaData
from sqlalchemy import sql

# note: I'm not sure how to mock a sqlalchemy engine, but we won't need
# to actually execute a query
engine = create_engine("bigquery://any-project-id-you-can-connect-to")

tbl = Table("my_dataset.my_table", MetaData(bind = engine), Column("some_col", String))

Note that running the following code returns a valid SQL query:

sel = tbl.select()

sel2 = sel.with_only_columns([{c.key: c for c in sel.inner_columns}["some_col"]])
print(.select([sel2.alias()]).limit(5))

Output:

SELECT `anon_1`.`some_col` AS `anon_1_some_col` 
FROM (SELECT `my_dataset.my_table`.`some_col` AS `some_col` 
FROM `my_dataset.my_table`) AS `anon_1`
 LIMIT %(param_1)s

However, if we re-run only the last two lines, it produces a new (invalid) query, which prefixes column names with `anon_1`.`my_dataset`:

sel2 = sel.with_only_columns([{c.key: c for c in sel.inner_columns}["some_col"]])
print(.select([sel2.alias()]).limit(5))

output

SELECT `anon_1`.`my_dataset`.`my_table_some_col` AS `anon_1_my_dataset_my_table_some_col` 
FROM (SELECT `my_dataset.my_table`.`some_col` AS `my_dataset_my_table_some_col` 
FROM `my_dataset.my_table`) AS `anon_1`
 LIMIT %(param_1)s

I wonder if it's related to the use_label attribute being set (#78), since it's mutating an element attribute.

@product-auto-label product-auto-label bot added the api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. label Apr 16, 2021
@yoshi-automation yoshi-automation added triage me I really want to be triaged. 🚨 This issue needs some love. labels Apr 19, 2021
@machow
Copy link
Author

machow commented Apr 23, 2021

Closing, since it looks like this was resolved when #78 was fixed ✨

@machow machow closed this as completed Apr 23, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. 🚨 This issue needs some love. triage me I really want to be triaged.
Projects
None yet
Development

No branches or pull requests

2 participants