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

Activity

1. plamut

   added

   api: bigqueryIssues related to the BigQuery API.Issues related to the BigQuery API.

   type: questionRequest for information or clarification. Not an issue.Request for information or clarification. Not an issue.

   on Sep 5, 2019
2. plamut commented on Sep 5, 2019

   

   plamut

   on Sep 5, 2019

   Contributor

   More actions

   @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!
3. haibin commented on Sep 5, 2019

   

   haibin

   on Sep 5, 2019

   Author

   More actions

   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
   
   
4. fhoffa commented on Sep 6, 2019

   

   fhoffa

   on Sep 6, 2019

   More actions

   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

   👍React with 👍1Reacted by Liu Haibin
5. plamut commented on Sep 8, 2019

   

   plamut

   on Sep 8, 2019

   Contributor

   More actions

   I can confirm that the issue is reproducible.
6. plamut

   added

   priority: p2Moderately-important priority. Fix may not be included in next release.Moderately-important priority. Fix may not be included in next release.

   type: bugError or flaw in code with unintended results or allowing sub-optimal usage patterns.Error or flaw in code with unintended results or allowing sub-optimal usage patterns.

   and removed

   type: questionRequest for information or clarification. Not an issue.Request for information or clarification. Not an issue.

   on Sep 8, 2019
7. plamut

   self-assigned this

   on Sep 8, 2019
8. plamut

   closed this as completedin #9189on Sep 10, 2019