BigQuery: Support WHERE IN or WHERE ANY clause in DB-API

[haibin]

BigQuery DB-API throws the following error when I pass parameters to the Cursor.execute() for WHERE IN or WHERE ANY clause. Any idea if it will be supported?

  File "/home/haibin/.local/share/virtualenvs/python-6nCS1ipk/lib/python3.6/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 159, in execute
    query_parameters = _helpers.to_query_parameters(parameters)
  File "/home/haibin/.local/share/virtualenvs/python-6nCS1ipk/lib/python3.6/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 117, in to_query_parameters
    return to_query_parameters_list(parameters)
  File "/home/haibin/.local/share/virtualenvs/python-6nCS1ipk/lib/python3.6/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 84, in to_query_parameters_list
    return [scalar_to_query_parameter(value) for value in parameters]
  File "/home/haibin/.local/share/virtualenvs/python-6nCS1ipk/lib/python3.6/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 84, in <listcomp>
    return [scalar_to_query_parameter(value) for value in parameters]
  File "/home/haibin/.local/share/virtualenvs/python-6nCS1ipk/lib/python3.6/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 69, in scalar_to_query_parameter
    name, value
google.cloud.bigquery.dbapi.exceptions.ProgrammingError: encountered parameter None with value ['Apple.com', 'Coles'] of unexpected type

[plamut]

@haibin Thanks for the report!

Would you mind providing more details, such as the BigQuery client version, and a reproducible code sample, if possible? That and any other info would be really helpful with investigating the cause, thanks!

[haibin]

google-cloud-bigquery version: 1.19.0

from google.cloud import bigquery

from google.cloud.bigquery import dbapi

client = bigquery.Client()
conn = dbapi.Connection(client)
curr = conn.cursor()

query = """
  SELECT name, state
  FROM `bigquery-public-data.usa_names.usa_1910_2013`
  WHERE state = %s
  LIMIT 2
"""
curr.execute(query, ('NY', ))
result = curr.fetchall()
print(result)

query = """
  SELECT name, state
  FROM `bigquery-public-data.usa_names.usa_1910_2013`
  WHERE state IN %s
  LIMIT 2
"""
curr.execute(query, (('NY', 'TX'), ))
result = curr.fetchall()
print(result)

Output

[Row(('Mildred', 'NY'), {'name': 0, 'state': 1}), Row(('Irene', 'NY'), {'name': 0, 'state': 1})]
Traceback (most recent call last):
  File "hello_bq.py", line 25, in <module>
    curr.execute(query, (('NY', 'TX'), ))
  File "/home/haibin/.local/share/virtualenvs/python-6nCS1ipk/lib/python3.6/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 159, in execute
    query_parameters = _helpers.to_query_parameters(parameters)
  File "/home/haibin/.local/share/virtualenvs/python-6nCS1ipk/lib/python3.6/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 117, in to_query_parameters
    return to_query_parameters_list(parameters)
  File "/home/haibin/.local/share/virtualenvs/python-6nCS1ipk/lib/python3.6/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 84, in to_query_parameters_list
    return [scalar_to_query_parameter(value) for value in parameters]
  File "/home/haibin/.local/share/virtualenvs/python-6nCS1ipk/lib/python3.6/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 84, in <listcomp>
    return [scalar_to_query_parameter(value) for value in parameters]
  File "/home/haibin/.local/share/virtualenvs/python-6nCS1ipk/lib/python3.6/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 69, in scalar_to_query_parameter
    name, value
google.cloud.bigquery.dbapi.exceptions.ProgrammingError: encountered parameter None with value ('NY', 'TX') of unexpected type

[fhoffa]

Alternative approach in the meantime:

query = """
  SELECT name, state
  FROM `bigquery-public-data.usa_names.usa_1910_2013`
  WHERE state IN UNNEST(SPLIT(%s))
  LIMIT 2
"""
curr.execute(query, ('NY,TX', ))

• https://stackoverflow.com/a/57816326/132438

[plamut]

I can confirm that the issue is reproducible.