'SELECT FORMAT("%f", 1.0)' errors with 'must be real number, not dict'

[romainr]

It seems like using FORMAT is not possible: e.g. with SELECT FORMAT("%f", 1.0):

from sqlalchemy.engine import create_engine
engine = create_engine('bigquery://')

engine.execute('SELECT FORMAT("%f", 1.0)')

 File "<stdin>", line 1, in <module>
 File "/home/data/sqlalchemy/engine/base.py", line 2237, in execute
 return connection.execute(statement, *multiparams, **params)
 File "/home/data/sqlalchemy/engine/base.py", line 1003, in execute
 return self._execute_text(object_, multiparams, params)
 File "/home/data/sqlalchemy/engine/base.py", line 1172, in _execute_text
 ret = self._execute_context(
 File "/home/data/sqlalchemy/engine/base.py", line 1316, in _execute_context
 self._handle_dbapi_exception(
 File "/home/data/sqlalchemy/engine/base.py", line 1514, in _handle_dbapi_exception
 util.raise_(exc_info[1], with_traceback=exc_info[2])
 File "/home/data/sqlalchemy/util/compat.py", line 182, in raise_
 raise exception
 File "/home/data/sqlalchemy/engine/base.py", line 1276, in _execute_context
 self.dialect.do_execute(
 File "/home/data/sqlalchemy/engine/default.py", line 593, in do_execute
 cursor.execute(statement, parameters)
 File "/home/data/google/cloud/bigquery/dbapi/_helpers.py", line 255, in with_closed_check
 return method(self, *args, **kwargs)
 File "/home/data/google/cloud/bigquery/dbapi/cursor.py", line 166, in execute
 formatted_operation = _format_operation(operation, parameters=parameters)
 File "/home/data/google/cloud/bigquery/dbapi/cursor.py", line 448, in _format_operation
 return _format_operation_dict(operation, parameters)
 File "/home/data/google/cloud/bigquery/dbapi/cursor.py", line 422, in _format_operation_dict
 return operation % formatted_params
TypeError: must be real number, not dict

[tswast]

The DB-API implementation for BigQuery uses the pyformat parameter style in query strings. You'll need to escape the % symbol by doubling up as %%.

[romainr]

Thanks! I see, even SELECT '%f' fails.

Maybe we should find a way to automatically escape those at some point.

[tswast]

Yeah, the logic here and here doesn't do anything smart.

I filed googleapis/python-bigquery#346 to avoid trying to format the string when no parameters are passed in.

[tswast]

This is fixed by google-cloud-bigquery>=2.3.0 https://github.com/googleapis/python-bigquery/blob/master/CHANGELOG.md#230-2020-11-04