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)
