def execute(self, operation, parameters=None, job_id=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 :type operation: str :param operation: A Google BigQuery query string. :type parameters: Mapping[str, Any] or Sequence[Any] :param parameters: (Optional) dictionary or sequence of parameter values. :type job_id: str :param job_id: (Optional) The job_id to use. If not set, a job ID is generated at random. """ self._query_data = None self._query_job = None client = self.connection._client # The DB-API uses the pyformat formatting, since the way BigQuery does # query parameters was not one of the standard options. Convert both # the query and the parameters to the format expected by the client # libraries. formatted_operation = _format_operation( operation, parameters=parameters) query_parameters = _helpers.to_query_parameters(parameters) config = job.QueryJobConfig() config.query_parameters = query_parameters config.use_legacy_sql = False self._query_job = client.query( formatted_operation, job_config=config, job_id=job_id) # Wait for the query to finish. try: self._query_job.result() except google.cloud.exceptions.GoogleCloudError as exc: raise exceptions.DatabaseError(exc) query_results = self._query_job._query_results self._set_rowcount(query_results) self._set_description(query_results.schema)
def wait_for_job(job): """Waits for a job to complete by polling until the state is `DONE`. Sleeps 1 second between calls to the BigQuery API. :type job: :class:`~google.cloud.bigquery.job._AsyncJob` :param job: Wait for this job to finish. :raises: :class:`~google.cloud.bigquery.dbapi.exceptions.DatabaseError` if the job fails. """ while True: job.reload() if job.state == 'DONE': if job.error_result: raise exceptions.DatabaseError(job.errors) return time.sleep(1)
def _execute(self, formatted_operation, parameters, job_id, job_config, parameter_types): self._query_data = None self._query_job = None client = self.connection._client # The DB-API uses the pyformat formatting, since the way BigQuery does # query parameters was not one of the standard options. Convert both # the query and the parameters to the format expected by the client # libraries. query_parameters = _helpers.to_query_parameters( parameters, parameter_types) if client._default_query_job_config: if job_config: config = job_config._fill_from_default( client._default_query_job_config) else: config = copy.deepcopy(client._default_query_job_config) else: config = job_config or job.QueryJobConfig(use_legacy_sql=False) config.query_parameters = query_parameters self._query_job = client.query(formatted_operation, job_config=config, job_id=job_id) if self._query_job.dry_run: self._set_description(schema=None) self.rowcount = 0 return # Wait for the query to finish. try: self._query_job.result() except google.cloud.exceptions.GoogleCloudError as exc: raise exceptions.DatabaseError(exc) query_results = self._query_job._query_results self._set_rowcount(query_results) self._set_description(query_results.schema)
def execute(self, operation, parameters=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 :type operation: str :param operation: A Google BigQuery query string. :type parameters: Mapping[str, Any] or Sequence[Any] :param parameters: (Optional) dictionary or sequence of parameter values. """ self._query_results = None self._page_token = None self._has_fetched_all_rows = False client = self.connection._client job_id = str(uuid.uuid4()) # The DB-API uses the pyformat formatting, since the way BigQuery does # query parameters was not one of the standard options. Convert both # the query and the parameters to the format expected by the client # libraries. formatted_operation = _format_operation( operation, parameters=parameters) query_parameters = _helpers.to_query_parameters(parameters) query_job = client.run_async_query( job_id, formatted_operation, query_parameters=query_parameters) query_job.use_legacy_sql = False try: query_results = query_job.result() except google.cloud.exceptions.GoogleCloudError: raise exceptions.DatabaseError(query_job.errors) # Force the iterator to run because the query_results doesn't # have the total_rows populated. See: # https://github.com/GoogleCloudPlatform/google-cloud-python/issues/3506 query_iterator = query_results.fetch_data() try: six.next(iter(query_iterator)) except StopIteration: pass self._query_data = iter( query_results.fetch_data(max_results=self.arraysize)) self._set_rowcount(query_results) self._set_description(query_results.schema)