Skip to content

Running a parameterized query via a SQLAlchemy Selectable #478

Open
@rjrudin

Description

@rjrudin

Is your feature request related to a problem? Please describe.

I'm back again, after submitting #474 and #476, as we're trying to shift from using pd.read_sql_query to using pandas_gbq.read_gbq .

Current issue is that when we construct our SQLAlchemy Selectable, we may toss a row limit onto it based on user input, e.g:

sql: Select = select(literal_column("*")).select_from(...)
sql = sql.limit(50)

pands_gbq.read_gbq wants a str as its query argument. So we're trying to compile the Selectable and serialize it as a string:

query = str(sql.compile(self.engine))

However, SQLAlchemy serializes its named parameters in a way that I can't get to work with pandas_gbq (and more generally, that won't work with BigQuery):

SELECT * 
FROM `some_dataset.some_table`
LIMIT %(param_1:INT64)s

I found an example in the pandas_gbq tests of how to configure query/queryParameters in the "configuration" dict that can be passed to read_gbq, but that of course doesn't help because BigQuery has its own way of naming parameters.

Describe the solution you'd like

Generally, I need a way (hopefully it exists already and I'm just missing it!) of serializing a SQLAlchemy Selectable into a string query that can be the input to read_gbq. For now, just being able to handle the limit() parameter above would suffice. I of course am trying to avoid string concatenation and am hoping there's some way I can configure the "configuration" dict to make this work.

Describe alternatives you've considered

N/A

Additional context

N/A

Metadata

Metadata

Assignees

No one assigned

    Labels

    api: bigqueryIssues related to the googleapis/python-bigquery-pandas API.type: feature request‘Nice-to-have’ improvement, new feature or different behavior or design.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions