Querying BigQuery is failing with Object of type CompiledCode is not JSON serializable

[pep4eto1211]

Describe the bug

I found this bug while I was testing the reladiff library. This appears to happen whenever I try to query BigQuery. I looked at the code and what appears to happen is that in the bigquery.py file in the _query_atom function, the BigQuery python client library's query method receives an instance of CompiledCode rather than a string and this causes the error. I think that a simple:

if isinstance(sql_code, CompiledCode):
    sql_code = sql_code.code

should fix the issue, but I am not sure if this fits with the library's established practices.
For easier debugging and reproduction I am attaching the code that causes the issue to appear:

from sqeleton import connect, table, this

db = connect("bigquery://project_id/dataset_id")
expr = table("TableName").select(this["id"]).limit(100)
result = db.query(expr)

print(result)

And also attaching a stack trace of the error that results from running the above code:

Traceback (most recent call last):
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/sqeleton/databases/bigquery.py", line 164, in _query_atom
    result = self._client.query(sql_code).result()
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/cloud/bigquery/client.py", line 3496, in query
    return _job_helpers.query_jobs_insert(
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/cloud/bigquery/_job_helpers.py", line 159, in query_jobs_insert
    future = do_query()
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/cloud/bigquery/_job_helpers.py", line 136, in do_query
    query_job._begin(retry=retry, timeout=timeout)
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/cloud/bigquery/job/query.py", line 1383, in _begin
    super(QueryJob, self)._begin(client=client, retry=retry, timeout=timeout)
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/cloud/bigquery/job/base.py", line 746, in _begin
    api_response = client._call_api(
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/cloud/bigquery/client.py", line 837, in _call_api
    return call()
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 293, in retry_wrapped_func
    return retry_target(
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 153, in retry_target
    _retry_error_helper(
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/api_core/retry/retry_base.py", line 212, in _retry_error_helper
    raise final_exc from source_exc
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 144, in retry_target
    result = target()
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/google/cloud/_http/__init__.py", line 479, in api_request
    data = json.dumps(data)
  File "/usr/lib/python3.10/json/__init__.py", line 231, in dumps
    return _default_encoder.encode(obj)
  File "/usr/lib/python3.10/json/encoder.py", line 199, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/usr/lib/python3.10/json/encoder.py", line 257, in iterencode
    return _iterencode(o, 0)
  File "/usr/lib/python3.10/json/encoder.py", line 179, in default
    raise TypeError(f'Object of type {o.__class__.__name__} '
TypeError: Object of type CompiledCode is not JSON serializable

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File ".../sqeleton-test/sqeleton_test/main.py", line 8, in <module>
    result = ddb.query(expr)
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/sqeleton/databases/base.py", line 385, in query
    res = self._query(sql_code)
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/sqeleton/databases/bigquery.py", line 176, in _query
    return apply_query(self._query_atom, sql_code)
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/sqeleton/databases/base.py", line 134, in apply_query
    return callback(sql_code)
  File ".../.virtualenvs/sqeleton-test-Mw6yV_ex-py3.10/lib/python3.10/site-packages/sqeleton/databases/bigquery.py", line 169, in _query_atom
    raise ConnectError(msg % (sql_code, e))
sqeleton.databases.base.ConnectError: Exception when trying to execute SQL code:
    CompiledCode(code='SELECT `id` FROM `TableName` LIMIT 100', args=[], type=None)

Got error: Object of type CompiledCode is not JSON serializable

Describe the environment

• OS: Code runs inside WSL environment with Ubuntu 22.04.3 LTS
• Python: 3.10.12
• sqeleton: 0.1.7
• google-cloud-bigquery: 3.27.0

[erezsh]

Thanks for the report!

Looks like the BigQuery code is a little bit out of date.

The signature should be def _query_atom(self, sql_code: CompiledCode):

Yes, you can just use sql_code.code. But I think you'll also need to pass sql_code.args. (or disable support for query args)