def execute_sql(self, sql, params=None, param_types=None, query_mode=None,
resume_token=b''):
"""Perform an ``ExecuteStreamingSql`` API request for rows in a table.
:type sql: str
:param sql: SQL query statement
:type params: dict, {str -> column value}
:param params: values for parameter replacement. Keys must match
the names used in ``sql``.
:type param_types: dict
:param param_types:
(Optional) maps explicit types for one or more param values;
required if parameters are passed.
:type query_mode:
:class:`google.cloud.proto.spanner.v1.ExecuteSqlRequest.QueryMode`
:param query_mode: Mode governing return of results / query plan. See
https://cloud.google.com/spanner/reference/rpc/google.spanner.v1#google.spanner.v1.ExecuteSqlRequest.QueryMode1
:type resume_token: bytes
:param resume_token: token for resuming previously-interrupted query
:rtype: :class:`~google.cloud.spanner.streamed.StreamedResultSet`
:returns: a result set instance which can be used to consume rows.
:raises ValueError:
for reuse of single-use snapshots, or if a transaction ID is
already pending for multiple-use snapshots.
"""
if self._read_request_count > 0:
if not self._multi_use:
raise ValueError("Cannot re-use single-use snapshot.")
if self._transaction_id is None:
raise ValueError("Transaction ID pending.")
if params is not None:
if param_types is None:
raise ValueError(
"Specify 'param_types' when passing 'params'.")
params_pb = Struct(fields={
key: _make_value_pb(value) for key, value in params.items()})
else:
params_pb = None
database = self._session._database
options = _options_with_prefix(database.name)
transaction = self._make_txn_selector()
api = database.spanner_api
iterator = api.execute_streaming_sql(
self._session.name, sql,
transaction=transaction, params=params_pb, param_types=param_types,
query_mode=query_mode, resume_token=resume_token, options=options)
self._read_request_count += 1
if self._multi_use:
return StreamedResultSet(iterator, source=self)
else:
return StreamedResultSet(iterator)
def __init__(self, query_plan=None, **query_stats):
from google.protobuf.struct_pb2 import Struct
from google.cloud.spanner._helpers import _make_value_pb
self.query_plan = query_plan
self.query_stats = Struct(fields={
key: _make_value_pb(value)
for key, value in query_stats.items()
})
def _make_result_set_stats(query_plan=None, **kw):
from google.cloud.proto.spanner.v1.result_set_pb2 import (
ResultSetStats)
from google.protobuf.struct_pb2 import Struct
from google.cloud.spanner._helpers import _make_value_pb
query_stats = Struct(fields={
key: _make_value_pb(value) for key, value in kw.items()})
return ResultSetStats(
query_plan=query_plan,
query_stats=query_stats,
)
def _callFUT(self, *args, **kw):
from google.cloud.spanner._helpers import _make_value_pb
return _make_value_pb(*args, **kw)
def _execute_sql_helper(self, multi_use, first=True, count=0):
from google.protobuf.struct_pb2 import Struct
from google.cloud.proto.spanner.v1.result_set_pb2 import (
PartialResultSet, ResultSetMetadata, ResultSetStats)
from google.cloud.proto.spanner.v1.transaction_pb2 import (
TransactionSelector)
from google.cloud.proto.spanner.v1.type_pb2 import Type, StructType
from google.cloud.proto.spanner.v1.type_pb2 import STRING, INT64
from google.cloud.spanner._helpers import _make_value_pb
TXN_ID = b'DEADBEEF'
VALUES = [
[u'bharney', u'rhubbyl', 31],
[u'phred', u'phlyntstone', 32],
]
VALUE_PBS = [[_make_value_pb(item) for item in row] for row in VALUES]
MODE = 2 # PROFILE
TOKEN = b'DEADBEEF'
struct_type_pb = StructType(fields=[
StructType.Field(name='first_name', type=Type(code=STRING)),
StructType.Field(name='last_name', type=Type(code=STRING)),
StructType.Field(name='age', type=Type(code=INT64)),
])
metadata_pb = ResultSetMetadata(row_type=struct_type_pb)
stats_pb = ResultSetStats(query_stats=Struct(
fields={
'rows_returned': _make_value_pb(2),
}))
result_sets = [
PartialResultSet(values=VALUE_PBS[0], metadata=metadata_pb),
PartialResultSet(values=VALUE_PBS[1], stats=stats_pb),
]
iterator = _MockIterator(*result_sets)
database = _Database()
api = database.spanner_api = _FauxSpannerAPI(
_execute_streaming_sql_response=iterator)
session = _Session(database)
derived = self._makeDerived(session)
derived._multi_use = multi_use
derived._read_request_count = count
if not first:
derived._transaction_id = TXN_ID
result_set = derived.execute_sql(SQL_QUERY_WITH_PARAM,
PARAMS,
PARAM_TYPES,
query_mode=MODE)
self.assertEqual(derived._read_request_count, count + 1)
if multi_use:
self.assertIs(result_set._source, derived)
else:
self.assertIsNone(result_set._source)
result_set.consume_all()
self.assertEqual(list(result_set.rows), VALUES)
self.assertEqual(result_set.metadata, metadata_pb)
self.assertEqual(result_set.stats, stats_pb)
(r_session, sql, transaction, params, param_types, resume_token,
query_mode, options) = api._executed_streaming_sql_with
self.assertEqual(r_session, self.SESSION_NAME)
self.assertEqual(sql, SQL_QUERY_WITH_PARAM)
self.assertIsInstance(transaction, TransactionSelector)
if multi_use:
if first:
self.assertTrue(transaction.begin.read_only.strong)
else:
self.assertEqual(transaction.id, TXN_ID)
else:
self.assertTrue(transaction.single_use.read_only.strong)
expected_params = Struct(fields={
key: _make_value_pb(value)
for (key, value) in PARAMS.items()
})
self.assertEqual(params, expected_params)
self.assertEqual(param_types, PARAM_TYPES)
self.assertEqual(query_mode, MODE)
self.assertEqual(resume_token, b'')
self.assertEqual(options.kwargs['metadata'],
[('google-cloud-resource-prefix', database.name)])
def _read_helper(self, multi_use, first=True, count=0):
from google.protobuf.struct_pb2 import Struct
from google.cloud.proto.spanner.v1.result_set_pb2 import (
PartialResultSet, ResultSetMetadata, ResultSetStats)
from google.cloud.proto.spanner.v1.transaction_pb2 import (
TransactionSelector)
from google.cloud.proto.spanner.v1.type_pb2 import Type, StructType
from google.cloud.proto.spanner.v1.type_pb2 import STRING, INT64
from google.cloud.spanner.keyset import KeySet
from google.cloud.spanner._helpers import _make_value_pb
TXN_ID = b'DEADBEEF'
VALUES = [
[u'bharney', 31],
[u'phred', 32],
]
VALUE_PBS = [[_make_value_pb(item) for item in row] for row in VALUES]
struct_type_pb = StructType(fields=[
StructType.Field(name='name', type=Type(code=STRING)),
StructType.Field(name='age', type=Type(code=INT64)),
])
metadata_pb = ResultSetMetadata(row_type=struct_type_pb)
stats_pb = ResultSetStats(query_stats=Struct(
fields={
'rows_returned': _make_value_pb(2),
}))
result_sets = [
PartialResultSet(values=VALUE_PBS[0], metadata=metadata_pb),
PartialResultSet(values=VALUE_PBS[1], stats=stats_pb),
]
KEYS = ['*****@*****.**', '*****@*****.**']
KEYSET = KeySet(keys=KEYS)
INDEX = 'email-address-index'
LIMIT = 20
TOKEN = b'DEADBEEF'
database = _Database()
api = database.spanner_api = _FauxSpannerAPI(
_streaming_read_response=_MockIterator(*result_sets))
session = _Session(database)
derived = self._makeDerived(session)
derived._multi_use = multi_use
derived._read_request_count = count
if not first:
derived._transaction_id = TXN_ID
result_set = derived.read(TABLE_NAME,
COLUMNS,
KEYSET,
index=INDEX,
limit=LIMIT)
self.assertEqual(derived._read_request_count, count + 1)
if multi_use:
self.assertIs(result_set._source, derived)
else:
self.assertIsNone(result_set._source)
result_set.consume_all()
self.assertEqual(list(result_set.rows), VALUES)
self.assertEqual(result_set.metadata, metadata_pb)
self.assertEqual(result_set.stats, stats_pb)
(r_session, table, columns, key_set, transaction, index, limit,
resume_token, options) = api._streaming_read_with
self.assertEqual(r_session, self.SESSION_NAME)
self.assertEqual(table, TABLE_NAME)
self.assertEqual(columns, COLUMNS)
self.assertEqual(key_set, KEYSET.to_pb())
self.assertIsInstance(transaction, TransactionSelector)
if multi_use:
if first:
self.assertTrue(transaction.begin.read_only.strong)
else:
self.assertEqual(transaction.id, TXN_ID)
else:
self.assertTrue(transaction.single_use.read_only.strong)
self.assertEqual(index, INDEX)
self.assertEqual(limit, LIMIT)
self.assertEqual(resume_token, b'')
self.assertEqual(options.kwargs['metadata'],
[('google-cloud-resource-prefix', database.name)])
def _makeValue(value):
from google.cloud.spanner._helpers import _make_value_pb
return _make_value_pb(value)
