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)