def _execute_sql(stub): """Probes to test ExecuteSql and ExecuteStreamingSql call from Spanner stub. Args: stub: An object of SpannerStub. Raises: ValueError: An error occurred when sql result is not as expected. """ _execute_sql_tracer = initialize_tracer() with _execute_sql_tracer.span(name='_execute_sql') as root_span: root_span.add_annotation('endpoint info available', endpoint=_SPANNER_TARGET) session = None try: # Create session with _execute_sql_tracer.span(name='stub.CreateSession'): session = stub.CreateSession( spanner_pb2.CreateSessionRequest(database=_DATABASE)) # Probing ExecuteSql call with _execute_sql_tracer.span(name='stub.ExecuteSql'): result_set = stub.ExecuteSql( spanner_pb2.ExecuteSqlRequest(session=session.name, sql='select * FROM users')) if result_set is None: raise ValueError('result_set is None') if len(result_set.rows) != 1: raise ValueError('incorrect result_set rows %d' % len(result_set.rows)) if result_set.rows[0].values[0].string_value != _TEST_USERNAME: raise ValueError('incorrect sql result %s' % result_set.rows[0].values[0].string_value) # Probing ExecuteStreamingSql call with _execute_sql_tracer.span(name='stub.ExecuteStreamingSql'): partial_result_set = stub.ExecuteStreamingSql( spanner_pb2.ExecuteSqlRequest(session=session.name, sql='select * FROM users')) if partial_result_set is None: raise ValueError('streaming_result_set is None') with _execute_sql_tracer.span(name='partial_result_set.next'): first_result = partial_result_set.next() if first_result.values[0].string_value != _TEST_USERNAME: raise ValueError('incorrect streaming sql first result %s' % first_result.values[0].string_value) finally: if session is not None: with _execute_sql_tracer.span(name='stub.DeleteSession'): stub.DeleteSession( spanner_pb2.DeleteSessionRequest(name=session.name))
def test_execute_streaming_sql(self): # Setup Expected Response chunked_value = True resume_token = b'103' expected_response = { 'chunked_value': chunked_value, 'resume_token': resume_token } expected_response = result_set_pb2.PartialResultSet( **expected_response) # Mock the API response channel = ChannelStub(responses=[iter([expected_response])]) client = spanner_v1.SpannerClient(channel=channel) # Setup Request session = client.session_path('[PROJECT]', '[INSTANCE]', '[DATABASE]', '[SESSION]') sql = 'sql114126' response = client.execute_streaming_sql(session, sql) resources = list(response) assert len(resources) == 1 assert expected_response == resources[0] assert len(channel.requests) == 1 expected_request = spanner_pb2.ExecuteSqlRequest(session=session, sql=sql) actual_request = channel.requests[0][1] assert expected_request == actual_request
def test_execute_sql(self, mock_create_stub): # Mock gRPC layer grpc_stub = mock.Mock() mock_create_stub.return_value = grpc_stub client = spanner_v1.SpannerClient() # Mock request session = client.session_path('[PROJECT]', '[INSTANCE]', '[DATABASE]', '[SESSION]') sql = 'sql114126' # Mock response expected_response = {} expected_response = result_set_pb2.ResultSet(**expected_response) grpc_stub.ExecuteSql.return_value = expected_response response = client.execute_sql(session, sql) self.assertEqual(expected_response, response) grpc_stub.ExecuteSql.assert_called_once() args, kwargs = grpc_stub.ExecuteSql.call_args self.assertEqual(len(args), 2) self.assertEqual(len(kwargs), 1) self.assertIn('metadata', kwargs) actual_request = args[0] expected_request = spanner_pb2.ExecuteSqlRequest(session=session, sql=sql) self.assertEqual(expected_request, actual_request)
def test_execute_streaming_sql(self): # Setup Expected Response chunked_value = True resume_token = b"103" expected_response = { "chunked_value": chunked_value, "resume_token": resume_token, } expected_response = result_set_pb2.PartialResultSet( **expected_response) # Mock the API response channel = ChannelStub(responses=[iter([expected_response])]) patch = mock.patch("google.api_core.grpc_helpers.create_channel") with patch as create_channel: create_channel.return_value = channel client = spanner_v1.SpannerClient() # Setup Request session = client.session_path("[PROJECT]", "[INSTANCE]", "[DATABASE]", "[SESSION]") sql = "sql114126" response = client.execute_streaming_sql(session, sql) resources = list(response) assert len(resources) == 1 assert expected_response == resources[0] assert len(channel.requests) == 1 expected_request = spanner_pb2.ExecuteSqlRequest(session=session, sql=sql) actual_request = channel.requests[0][1] assert expected_request == actual_request
def test_execute_sql(self): # Setup Expected Response expected_response = {} expected_response = result_set_pb2.ResultSet(**expected_response) # Mock the API response channel = ChannelStub(responses=[expected_response]) patch = mock.patch("google.api_core.grpc_helpers.create_channel") with patch as create_channel: create_channel.return_value = channel client = spanner_v1.SpannerClient() # Setup Request session = client.session_path("[PROJECT]", "[INSTANCE]", "[DATABASE]", "[SESSION]") sql = "sql114126" response = client.execute_sql(session, sql) assert expected_response == response assert len(channel.requests) == 1 expected_request = spanner_pb2.ExecuteSqlRequest(session=session, sql=sql) actual_request = channel.requests[0][1] assert expected_request == actual_request
def test_execute_sql(self): # Setup Expected Response expected_response = {} expected_response = result_set_pb2.ResultSet(**expected_response) # Mock the API response channel = ChannelStub(responses=[expected_response]) client = spanner_v1.SpannerClient(channel=channel) # Setup Request session = client.session_path('[PROJECT]', '[INSTANCE]', '[DATABASE]', '[SESSION]') sql = 'sql114126' response = client.execute_sql(session, sql) assert expected_response == response assert len(channel.requests) == 1 expected_request = spanner_pb2.ExecuteSqlRequest(session=session, sql=sql) actual_request = channel.requests[0][1] assert expected_request == actual_request
def test_execute_streaming_sql(self, mock_create_stub): # Mock gRPC layer grpc_stub = mock.Mock() mock_create_stub.return_value = grpc_stub client = spanner_v1.SpannerClient() # Mock request session = client.session_path('[PROJECT]', '[INSTANCE]', '[DATABASE]', '[SESSION]') sql = 'sql114126' # Mock response chunked_value = True resume_token = b'103' expected_response = { 'chunked_value': chunked_value, 'resume_token': resume_token } expected_response = result_set_pb2.PartialResultSet( **expected_response) grpc_stub.ExecuteStreamingSql.return_value = iter([expected_response]) response = client.execute_streaming_sql(session, sql) resources = list(response) self.assertEqual(1, len(resources)) self.assertEqual(expected_response, resources[0]) grpc_stub.ExecuteStreamingSql.assert_called_once() args, kwargs = grpc_stub.ExecuteStreamingSql.call_args self.assertEqual(len(args), 2) self.assertEqual(len(kwargs), 1) self.assertIn('metadata', kwargs) actual_request = args[0] expected_request = spanner_pb2.ExecuteSqlRequest(session=session, sql=sql) self.assertEqual(expected_request, actual_request)
def execute_streaming_sql(self, session, sql, transaction=None, params=None, param_types=None, resume_token=None, query_mode=None, options=None): """ Like ``ExecuteSql``, except returns the result set as a stream. Unlike ``ExecuteSql``, there is no limit on the size of the returned result set. However, no individual row in the result set can exceed 100 MiB, and no column value can exceed 10 MiB. Example: >>> from google.cloud import spanner_v1 >>> >>> client = spanner_v1.SpannerClient() >>> >>> session = client.session_path('[PROJECT]', '[INSTANCE]', '[DATABASE]', '[SESSION]') >>> sql = '' >>> >>> for element in client.execute_streaming_sql(session, sql): ... # process element ... pass Args: session (str): Required. The session in which the SQL query should be performed. sql (str): Required. The SQL query string. transaction (Union[dict, ~google.cloud.spanner_v1.types.TransactionSelector]): The transaction to use. If none is provided, the default is a temporary read-only transaction with strong concurrency. If a dict is provided, it must be of the same form as the protobuf message :class:`~google.cloud.spanner_v1.types.TransactionSelector` params (Union[dict, ~google.cloud.spanner_v1.types.Struct]): The SQL query string can contain parameter placeholders. A parameter placeholder consists of ``'@'`` followed by the parameter name. Parameter names consist of any combination of letters, numbers, and underscores. Parameters can appear anywhere that a literal value is expected. The same parameter name can be used more than once, for example: ``\"WHERE id > @msg_id AND id < @msg_id + 100\"`` It is an error to execute an SQL query with unbound parameters. Parameter values are specified using ``params``, which is a JSON object whose keys are parameter names, and whose values are the corresponding parameter values. If a dict is provided, it must be of the same form as the protobuf message :class:`~google.cloud.spanner_v1.types.Struct` param_types (dict[str -> Union[dict, ~google.cloud.spanner_v1.types.Type]]): It is not always possible for Cloud Spanner to infer the right SQL type from a JSON value. For example, values of type ``BYTES`` and values of type ``STRING`` both appear in ``params`` as JSON strings. In these cases, ``param_types`` can be used to specify the exact SQL type for some or all of the SQL query parameters. See the definition of ``Type`` for more information about SQL types. If a dict is provided, it must be of the same form as the protobuf message :class:`~google.cloud.spanner_v1.types.Type` resume_token (bytes): If this request is resuming a previously interrupted SQL query execution, ``resume_token`` should be copied from the last ``PartialResultSet`` yielded before the interruption. Doing this enables the new SQL query execution to resume where the last one left off. The rest of the request parameters must exactly match the request that yielded this token. query_mode (~google.cloud.spanner_v1.types.QueryMode): Used to control the amount of debugging information returned in ``ResultSetStats``. options (~google.gax.CallOptions): Overrides the default settings for this call, e.g, timeout, retries etc. Returns: Iterable[~google.cloud.spanner_v1.types.PartialResultSet]. Raises: :exc:`google.gax.errors.GaxError` if the RPC is aborted. :exc:`ValueError` if the parameters are invalid. """ request = spanner_pb2.ExecuteSqlRequest( session=session, sql=sql, transaction=transaction, params=params, param_types=param_types, resume_token=resume_token, query_mode=query_mode) return self._execute_streaming_sql(request, options)
def execute_sql(self, session, sql, transaction=None, params=None, param_types=None, resume_token=None, query_mode=None, retry=google.api_core.gapic_v1.method.DEFAULT, timeout=google.api_core.gapic_v1.method.DEFAULT, metadata=None): """ Executes an SQL query, returning all rows in a single reply. This method cannot be used to return a result set larger than 10 MiB; if the query yields more data than that, the query fails with a ``FAILED_PRECONDITION`` error. Queries inside read-write transactions might return ``ABORTED``. If this occurs, the application should restart the transaction from the beginning. See ``Transaction`` for more details. Larger result sets can be fetched in streaming fashion by calling ``ExecuteStreamingSql`` instead. Example: >>> from google.cloud import spanner_v1 >>> >>> client = spanner_v1.SpannerClient() >>> >>> session = client.session_path('[PROJECT]', '[INSTANCE]', '[DATABASE]', '[SESSION]') >>> sql = '' >>> >>> response = client.execute_sql(session, sql) Args: session (str): Required. The session in which the SQL query should be performed. sql (str): Required. The SQL query string. transaction (Union[dict, ~google.cloud.spanner_v1.types.TransactionSelector]): The transaction to use. If none is provided, the default is a temporary read-only transaction with strong concurrency. If a dict is provided, it must be of the same form as the protobuf message :class:`~google.cloud.spanner_v1.types.TransactionSelector` params (Union[dict, ~google.cloud.spanner_v1.types.Struct]): The SQL query string can contain parameter placeholders. A parameter placeholder consists of ``'@'`` followed by the parameter name. Parameter names consist of any combination of letters, numbers, and underscores. Parameters can appear anywhere that a literal value is expected. The same parameter name can be used more than once, for example: ``\"WHERE id > @msg_id AND id < @msg_id + 100\"`` It is an error to execute an SQL query with unbound parameters. Parameter values are specified using ``params``, which is a JSON object whose keys are parameter names, and whose values are the corresponding parameter values. If a dict is provided, it must be of the same form as the protobuf message :class:`~google.cloud.spanner_v1.types.Struct` param_types (dict[str -> Union[dict, ~google.cloud.spanner_v1.types.Type]]): It is not always possible for Cloud Spanner to infer the right SQL type from a JSON value. For example, values of type ``BYTES`` and values of type ``STRING`` both appear in ``params`` as JSON strings. In these cases, ``param_types`` can be used to specify the exact SQL type for some or all of the SQL query parameters. See the definition of ``Type`` for more information about SQL types. If a dict is provided, it must be of the same form as the protobuf message :class:`~google.cloud.spanner_v1.types.Type` resume_token (bytes): If this request is resuming a previously interrupted SQL query execution, ``resume_token`` should be copied from the last ``PartialResultSet`` yielded before the interruption. Doing this enables the new SQL query execution to resume where the last one left off. The rest of the request parameters must exactly match the request that yielded this token. query_mode (~google.cloud.spanner_v1.types.QueryMode): Used to control the amount of debugging information returned in ``ResultSetStats``. retry (Optional[google.api_core.retry.Retry]): A retry object used to retry requests. If ``None`` is specified, requests will not be retried. timeout (Optional[float]): The amount of time, in seconds, to wait for the request to complete. Note that if ``retry`` is specified, the timeout applies to each individual attempt. Returns: A :class:`~google.cloud.spanner_v1.types.ResultSet` instance. Raises: google.api_core.exceptions.GoogleAPICallError: If the request failed for any reason. google.api_core.exceptions.RetryError: If the request failed due to a retryable error and retry attempts failed. ValueError: If the parameters are invalid. """ request = spanner_pb2.ExecuteSqlRequest( session=session, sql=sql, transaction=transaction, params=params, param_types=param_types, resume_token=resume_token, query_mode=query_mode, ) return self._execute_sql( request, retry=retry, timeout=timeout, metadata=metadata)