- 3.25.0 (latest)
- 3.24.0
- 3.23.1
- 3.22.0
- 3.21.0
- 3.20.1
- 3.19.0
- 3.18.0
- 3.17.2
- 3.16.0
- 3.15.0
- 3.14.1
- 3.13.0
- 3.12.0
- 3.11.4
- 3.4.0
- 3.3.6
- 3.2.0
- 3.1.0
- 3.0.1
- 2.34.4
- 2.33.0
- 2.32.0
- 2.31.0
- 2.30.1
- 2.29.0
- 2.28.1
- 2.27.1
- 2.26.0
- 2.25.2
- 2.24.1
- 2.23.3
- 2.22.1
- 2.21.0
- 2.20.0
- 2.19.0
- 2.18.0
- 2.17.0
- 2.16.1
- 2.15.0
- 2.14.0
- 2.13.1
- 2.12.0
- 2.11.0
- 2.10.0
- 2.9.0
- 2.8.0
- 2.7.0
- 2.6.2
- 2.5.0
- 2.4.0
- 2.3.1
- 2.2.0
- 2.1.0
- 2.0.0
- 1.28.2
- 1.27.2
- 1.26.1
- 1.25.0
- 1.24.0
- 1.23.1
- 1.22.0
- 1.21.0
- 1.20.0
- 1.19.0
- 1.18.0
- 1.17.0
- 1.16.0
DB-API Reference
Google BigQuery implementation of the Database API Specification v2.0.
This module implements the Python Database API Specification v2.0 (DB-API) for Google BigQuery.
google.cloud.bigquery.dbapi.Binary(data)
Contruct a DB-API binary value.
Parameters
data (bytes-like) – An object containing binary data and that can be converted to bytes with the bytes builtin.
Returns
The binary data as a bytes object.
Return type
class google.cloud.bigquery.dbapi.Connection(client=None, bqstorage_client=None, prefer_bqstorage_client=True)
Bases: object
DB-API Connection to Google BigQuery.
Parameters
client (Optional[google.cloud.bigquery.Client]) – A REST API client used to connect to BigQuery. If not passed, a client is created using default options inferred from the environment.
bqstorage_client (Optional[google.cloud.bigquery_storage_v1.BigQueryReadClient]) – A client that uses the faster BigQuery Storage API to fetch rows from BigQuery. If not passed, it is created using the same credentials as
client
(provided that BigQuery Storage dependencies are installed).prefer_bqstorage_client (Optional[bool]) – Prefer the BigQuery Storage client over the REST client. If Storage client isn’t available, fall back to the REST client. Defaults to
True
.
close()
Close the connection and any cursors created from it.
Any BigQuery clients explicitly passed to the constructor are not closed, only those created by the connection instance itself.
commit()
No-op, but for consistency raise an error if connection is closed.
cursor()
Return a new cursor object.
Returns
A DB-API cursor that uses this connection.
Return type
google.cloud.bigquery.dbapi.Cursor
class google.cloud.bigquery.dbapi.Cursor(connection)
Bases: object
DB-API Cursor to Google BigQuery.
Parameters
connection (google.cloud.bigquery.dbapi.Connection) – A DB-API connection to Google BigQuery.
close()
Mark the cursor as closed, preventing its further use.
execute(operation, parameters=None, job_id=None, job_config=None)
Prepare and execute a database operation.
NOTE: When setting query parameters, values which are “text”
(unicode
in Python2, str
in Python3) will use
the ‘STRING’ BigQuery type. Values which are “bytes” (str
in
Python2, bytes
in Python3), will use using the ‘BYTES’ type.
A ~datetime.datetime parameter without timezone information uses the ‘DATETIME’ BigQuery type (example: Global Pi Day Celebration March 14, 2017 at 1:59pm). A ~datetime.datetime parameter with timezone information uses the ‘TIMESTAMP’ BigQuery type (example: a wedding on April 29, 2011 at 11am, British Summer Time).
For more information about BigQuery data types, see: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types
STRUCT
/RECORD
and REPEATED
query parameters are not
yet supported. See:
https://github.com/GoogleCloudPlatform/google-cloud-python/issues/3524
Parameters
operation (str) – A Google BigQuery query string.
parameters (Union[Mapping[str, **Any], **Sequence[Any]]) – (Optional) dictionary or sequence of parameter values.
job_id (str* | *None) – (Optional and discouraged) The job ID to use when creating the query job. For best performance and reliability, manually setting a job ID is discouraged.
job_config (google.cloud.bigquery.job.QueryJobConfig) – (Optional) Extra configuration options for the query job.
executemany(operation, seq_of_parameters)
Prepare and execute a database operation multiple times.
Parameters
fetchall()
Fetch all remaining results from the last execute\*()
call.
NOTE: If a dry run query was executed, no rows are returned.
Returns
A list of all the rows in the results.
Return type
List[Tuple]
Raises
google.cloud.bigquery.dbapi.InterfaceError – if called before
execute()
.
fetchmany(size=None)
Fetch multiple results from the last execute\*()
call.
NOTE: If a dry run query was executed, no rows are returned.
NOTE: The size parameter is not used for the request/response size.
Set the arraysize
attribute before calling execute()
to
set the batch size.
Parameters
size (int) – (Optional) Maximum number of rows to return. Defaults to the
arraysize
property value. Ifarraysize
is not set, it defaults to1
.Returns
A list of rows.
Return type
List[Tuple]
Raises
google.cloud.bigquery.dbapi.InterfaceError – if called before
execute()
.
fetchone()
Fetch a single row from the results of the last execute\*()
call.
NOTE: If a dry run query was executed, no rows are returned.
Returns
A tuple representing a row or
None
if no more data is available.Return type
Tuple
Raises
google.cloud.bigquery.dbapi.InterfaceError – if called before
execute()
.
property query_job(: Optional[google.cloud.bigquery.job.query.QueryJob )
The query job
created by the last execute\*()
call, if a query job was created.
NOTE: If the last execute\*()
call was executemany()
, this is the
last job created by executemany()
.
Type
setinputsizes(sizes)
No-op, but for consistency raise an error if cursor is closed.
setoutputsize(size, column=None)
No-op, but for consistency raise an error if cursor is closed.
exception google.cloud.bigquery.dbapi.DataError()
Bases: google.cloud.bigquery.dbapi.exceptions.DatabaseError
DB-API error due to problems with the processed data.
with_traceback()
Exception.with_traceback(tb) – set self.traceback to tb and return self.
exception google.cloud.bigquery.dbapi.DatabaseError()
Bases: google.cloud.bigquery.dbapi.exceptions.Error
DB-API error related to the database.
with_traceback()
Exception.with_traceback(tb) – set self.traceback to tb and return self.
google.cloud.bigquery.dbapi.Date()
alias of datetime.date
google.cloud.bigquery.dbapi.DateFromTicks(timestamp, /)
Create a date from a POSIX timestamp.
The timestamp is a number, e.g. created via time.time(), that is interpreted as local time.
exception google.cloud.bigquery.dbapi.Error()
Bases: Exception
Exception representing all non-warning DB-API errors.
with_traceback()
Exception.with_traceback(tb) – set self.traceback to tb and return self.
exception google.cloud.bigquery.dbapi.IntegrityError()
Bases: google.cloud.bigquery.dbapi.exceptions.DatabaseError
DB-API error when integrity of the database is affected.
with_traceback()
Exception.with_traceback(tb) – set self.traceback to tb and return self.
exception google.cloud.bigquery.dbapi.InterfaceError()
Bases: google.cloud.bigquery.dbapi.exceptions.Error
DB-API error related to the database interface.
with_traceback()
Exception.with_traceback(tb) – set self.traceback to tb and return self.
exception google.cloud.bigquery.dbapi.InternalError()
Bases: google.cloud.bigquery.dbapi.exceptions.DatabaseError
DB-API error when the database encounters an internal error.
with_traceback()
Exception.with_traceback(tb) – set self.traceback to tb and return self.
exception google.cloud.bigquery.dbapi.NotSupportedError()
Bases: google.cloud.bigquery.dbapi.exceptions.DatabaseError
DB-API error for operations not supported by the database or API.
with_traceback()
Exception.with_traceback(tb) – set self.traceback to tb and return self.
exception google.cloud.bigquery.dbapi.OperationalError()
Bases: google.cloud.bigquery.dbapi.exceptions.DatabaseError
DB-API error related to the database operation.
These errors are not necessarily under the control of the programmer.
with_traceback()
Exception.with_traceback(tb) – set self.traceback to tb and return self.
exception google.cloud.bigquery.dbapi.ProgrammingError()
Bases: google.cloud.bigquery.dbapi.exceptions.DatabaseError
DB-API exception raised for programming errors.
with_traceback()
Exception.with_traceback(tb) – set self.traceback to tb and return self.
google.cloud.bigquery.dbapi.Time()
alias of datetime.time
google.cloud.bigquery.dbapi.TimeFromTicks(ticks, tz=None)
Construct a DB-API time value from the given ticks value.
Parameters
ticks (float) – a number of seconds since the epoch; see the documentation of the standard Python time module for details.
tz (datetime.tzinfo) – (Optional) time zone to use for conversion
Returns
time represented by ticks.
Return type
google.cloud.bigquery.dbapi.Timestamp()
alias of datetime.datetime
google.cloud.bigquery.dbapi.TimestampFromTicks()
timestamp[, tz] -> tz’s local time from POSIX timestamp.
exception google.cloud.bigquery.dbapi.Warning()
Bases: Exception
Exception raised for important DB-API warnings.
with_traceback()
Exception.with_traceback(tb) – set self.traceback to tb and return self.
google.cloud.bigquery.dbapi.connect(client=None, bqstorage_client=None, prefer_bqstorage_client=True)
Construct a DB-API connection to Google BigQuery.
Parameters
client (Optional[google.cloud.bigquery.Client]) – A REST API client used to connect to BigQuery. If not passed, a client is created using default options inferred from the environment.
bqstorage_client (Optional[google.cloud.bigquery_storage_v1.BigQueryReadClient]) – A client that uses the faster BigQuery Storage API to fetch rows from BigQuery. If not passed, it is created using the same credentials as
client
(provided that BigQuery Storage dependencies are installed).prefer_bqstorage_client (Optional[bool]) – Prefer the BigQuery Storage client over the REST client. If Storage client isn’t available, fall back to the REST client. Defaults to
True
.
Returns
A new DB-API connection to BigQuery.
Return type
google.cloud.bigquery.dbapi.Connection
DB-API Query-Parameter Syntax
The BigQuery DB-API uses the qmark parameter style for unnamed/positional parameters and the pyformat parameter style for named parameters.
An example of a query using unnamed parameters:
insert into people (name, income) values (?, ?)
and using named parameters:
insert into people (name, income) values (%(name)s, %(income)s)
Providing explicit type information
BigQuery requires type information for parameters. The BigQuery DB-API can usually determine parameter types for parameters based on provided values. Sometimes, however, types can’t be determined (for example when None is passed) or are determined incorrectly (for example when passing a floating-point value to a numeric column).
The BigQuery DB-API provides an extended parameter syntax. For named parameters, a BigQuery type is provided after the name separated by a colon, as in:
insert into people (name, income) values (%(name:string)s, %(income:numeric)s)
For unnamed parameters, use the named syntax with a type, but no name, as in:
insert into people (name, income) values (%(:string)s, %(:numeric)s)
Providing type information is the only way to pass struct data:
cursor.execute(
"insert into points (point) values (%(:struct<x float64, y float64>)s)",
[{"x": 10, "y": 20}],
)