This repository was archived by the owner on Mar 6, 2026. It is now read-only.
Repository navigation
This repository was archived by the owner on Mar 6, 2026. It is now read-only.
None or empty array parameter values aren't handled in the dbapi #609
Description
>>> import google.cloud.bigquery.dbapi
>>> conn = google.cloud.bigquery.dbapi.connect()
/home/jim/p/g/python-bigquery/google/cloud/bigquery/client.py:444: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.
warnings.warn(
>>> cursor = conn.cursor()
>>> cursor.execute("select %(x)s", dict(x=None))
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/jim/p/g/python-bigquery/google/cloud/bigquery/dbapi/_helpers.py", line 270, in with_closed_check
return method(self, *args, **kwargs)
File "/home/jim/p/g/python-bigquery/google/cloud/bigquery/dbapi/cursor.py", line 173, in execute
query_parameters = _helpers.to_query_parameters(parameters)
File "/home/jim/p/g/python-bigquery/google/cloud/bigquery/dbapi/_helpers.py", line 167, in to_query_parameters
return to_query_parameters_dict(parameters)
File "/home/jim/p/g/python-bigquery/google/cloud/bigquery/dbapi/_helpers.py", line 146, in to_query_parameters_dict
param = scalar_to_query_parameter(value, name=name)
File "/home/jim/p/g/python-bigquery/google/cloud/bigquery/dbapi/_helpers.py", line 53, in scalar_to_query_parameter
raise exceptions.ProgrammingError(
google.cloud.bigquery.dbapi.exceptions.ProgrammingError: encountered parameter x with value None of unexpected type
The same issue applies to empty array ([]) parameters.
Activity
[-]None parameter values aren't handled in the dbapi[/-][+]None or empty array parameter values aren't handled in the dbapi[/+]jimfulton commentedon Apr 16, 2021
FTR, I have a local fix that I'll finalize after I finish getting sqlalchemy compliance tests to pass.
plamut commentedon Apr 16, 2021
@jimfulton If the fix touches the Cursor code and might cause conflicts, I suggest reviewing and merging two existing DB API PRs first, and then rebasing on top of that.
jimfulton commentedon Apr 16, 2021
But wait, there's more!
The dry-run trick fails with:
when the value passed for
foois None.The server side guesses type of
footwice, guessed differently, and then complains that the guesses were different:Undeclared parameter 'foo' is used assuming different types (TIME vs INT64) at [3:18]Arguably, a sophisticated analysis would decide the type of
date_data(TIME in this case).In the case of SQLAlchemy, it happens that we know the type of the parameter. If we could somehow get to BQ, we could avoid the dry-run call.
Or maybe this should be viewed as a BQ time-inference bug. I think I'll skip the relevant SQLAlchemy-dialect tests until I get a read on that. :)
jimfulton commentedon Apr 26, 2021
It seems, according to https://issuetracker.google.com/issues/185823434, that the dry-run feature isn't meant for determining parameter types.
jimfulton commentedon Apr 26, 2021
Because:
We decided to extend the parameter syntax to provide type information.