def test_it(self): from google.cloud.spanner_v1.proto import type_pb2 from google.cloud.spanner_v1 import param_types struct_type = type_pb2.StructType(fields=[ type_pb2.StructType.Field( name='name', type=type_pb2.Type(code=type_pb2.STRING), ), type_pb2.StructType.Field( name='count', type=type_pb2.Type(code=type_pb2.INT64), ), ]) expected = type_pb2.Type( code=type_pb2.STRUCT, struct_type=struct_type, ) found = param_types.Struct([ param_types.StructField('name', param_types.STRING), param_types.StructField('count', param_types.INT64), ]) self.assertEqual(found, expected)
def test_it(self): from google.cloud.spanner_v1.proto import type_pb2 from google.cloud.spanner_v1 import param_types expected = type_pb2.Type( code=type_pb2.ARRAY, array_element_type=type_pb2.Type(code=type_pb2.INT64)) found = param_types.Array(param_types.INT64) self.assertEqual(found, expected)
def compute_interest_for_all(database): # In a real production DB, we would use larger batches. batch_size = 2 while True: # Find any account that hasn't been updated for the current month # (This is done in a read-only transaction, and hence does not # take locks on the table) results = database.execute_sql( """ SELECT CustomerNumber,AccountNumber,LastInterestCalculation FROM Accounts WHERE LastInterestCalculation IS NULL OR (EXTRACT(MONTH FROM LastInterestCalculation) <> EXTRACT(MONTH FROM CURRENT_TIMESTAMP()) AND EXTRACT(YEAR FROM LastInterestCalculation) <> EXTRACT(YEAR FROM CURRENT_TIMESTAMP())) LIMIT @batch_size""", params={'batch_size': batch_size}, param_types={'customer': type_pb2.Type(code=type_pb2.INT64)}) zero_results = True for customer_number, account_number, last_calculation in results: zero_results = False try: database.run_in_transaction(compute_interest_for_account, customer_number, account_number, last_calculation) print("Computed interest for account ", account_number) except RowAlreadyUpdated: print("Account {account_number} already updated".format( account_number=account_number)) if zero_results: break
def function_read_spanner_generic(data, instancename): """Background Cloud Function to be triggered by Pub/Sub. Args: data (dict): The dictionary with data specific to this type of event. instancename : Spanner instance name. """ import base64 if 'data' in data: msg = base64.b64decode(data['data']).decode('utf-8') parseddata = json.reads(msg) productid: int = parseddata["product_id"] upc: int = parseddata["sku_upc_nbr"] pricelevel = parseddata["fulfillment"]["price_level"] spanner_client = spanner.Client() instance = spanner_client.instance(instancename) database = instance.database('inventory') params = { 'productid': productid, 'upc': upc, 'pricelevel': pricelevel } param_types = { 'productid': type_pb2.Type(code=type_pb2.INT64), 'upc': type_pb2.Type(code=type_pb2.INT64), 'pricelevel': type_pb2.Type(code=type_pb2.INT64) } count: int = 0 try: while count < 500: with database.snapshot() as snapshot: keyset = spanner.KeySet(all_=True) results = snapshot.read( table='availability_by_productid_upc', columns=('SingerId', 'AlbumId', 'AlbumTitle',), keyset=keyset, ) for row in results: print(u'SingerId: {}, AlbumId: {}, AlbumTitle: {}'.format(*row)) count += 1 except Exception as e: print(f' Exception occurred : {e} ') print(f' Table has been update for UPC : {upc} and message {msg}')
def _spanner_type_of_python_object(value: GuessableParamType) -> type_pb2.Type: """Returns the Cloud Spanner type of the given object. Args: value: Object to guess the type of. """ # See # https://github.com/googleapis/python-spanner/blob/master/google/cloud/spanner_v1/proto/type.proto # for the Cloud Spanner types, and # https://github.com/googleapis/python-spanner/blob/e981adb3157bb06e4cb466ca81d74d85da976754/google/cloud/spanner_v1/_helpers.py#L91-L133 # for Python types. if value is None: raise TypeError( 'Cannot infer type of None, because any SQL type can be NULL.') simple_type_code = { bool: type_pb2.BOOL, int: type_pb2.INT64, float: type_pb2.FLOAT64, datetime_helpers.DatetimeWithNanoseconds: type_pb2.TIMESTAMP, datetime.datetime: type_pb2.TIMESTAMP, datetime.date: type_pb2.DATE, bytes: type_pb2.BYTES, str: type_pb2.STRING, decimal.Decimal: type_pb2.NUMERIC, }.get(type(value)) if simple_type_code is not None: return type_pb2.Type(code=simple_type_code) elif isinstance(value, (list, tuple)): element_types = tuple( _spanner_type_of_python_object(item) for item in value if item is not None) unique_element_type_count = len({ # Protos aren't hashable, so use their serializations. element_type.SerializeToString(deterministic=True) for element_type in element_types }) if unique_element_type_count == 1: return type_pb2.Type( code=type_pb2.ARRAY, array_element_type=element_types[0], ) else: raise TypeError( f'Array does not have elements of exactly one type: {value!r}') else: raise TypeError('Unknown type: {value!r}')
def last_n_transactions(database, account_number, n): params = { 'account': account_number, 'num': n, } param_types = { 'account': type_pb2.Type(code=type_pb2.INT64), 'num': type_pb2.Type(code=type_pb2.INT64), } results = database.execute_sql( """SELECT Ts, ChangeAmount, Memo FROM AccountHistory WHERE AccountNumber=@account ORDER BY Ts DESC LIMIT @num""", params=params, param_types=param_types) ret = list(results) print("RESULTS", ret) pprint.pprint(ret) return ret
def query_data_with_index( instance_id, database_id, start_title='Aardvark', end_title='Goo'): """Queries sample data from the database using SQL and an index. The index must exist before running this sample. You can add the index by running the `add_index` sample or by running this DDL statement against your database: CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle) This sample also uses the `MarketingBudget` column. You can add the column by running the `add_column` sample or by running this DDL statement against your database: ALTER TABLE Albums ADD COLUMN MarketingBudget INT64 """ from google.cloud.spanner_v1.proto import type_pb2 spanner_client = spanner.Client() instance = spanner_client.instance(instance_id) database = instance.database(database_id) params = { 'start_title': start_title, 'end_title': end_title } param_types = { 'start_title': type_pb2.Type(code=type_pb2.STRING), 'end_title': type_pb2.Type(code=type_pb2.STRING) } with database.snapshot() as snapshot: results = snapshot.execute_sql( "SELECT AlbumId, AlbumTitle, MarketingBudget " "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} " "WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title", params=params, param_types=param_types) for row in results: print( u'AlbumId: {}, AlbumTitle: {}, ' 'MarketingBudget: {}'.format(*row))
def Array(element_type): # pylint: disable=invalid-name """Construct an array paramter type description protobuf. :type element_type: :class:`type_pb2.Type` :param element_type: the type of elements of the array :rtype: :class:`type_pb2.Type` :returns: the appropriate array-type protobuf """ return type_pb2.Type(code=type_pb2.ARRAY, array_element_type=element_type)
def Struct(fields): # pylint: disable=invalid-name """Construct a struct parameter type description protobuf. :type fields: list of :class:`type_pb2.StructType.Field` :param fields: the fields of the struct :rtype: :class:`type_pb2.Type` :returns: the appropriate struct-type protobuf """ return type_pb2.Type(code=type_pb2.STRUCT, struct_type=type_pb2.StructType(fields=fields))
def account_balance(database, account_number): params = {'account': account_number} param_types = {'account': type_pb2.Type(code=type_pb2.INT64)} results = database.execute_sql( """SELECT Balance From Accounts@{FORCE_INDEX=UniqueAccountNumbers} WHERE AccountNumber=@account""", params=params, param_types=param_types) balance = extract_single_cell(results) print("ACCOUNT BALANCE", balance) return balance
def from_value(cls: Type[T], value: GuessableParamType) -> T: """Returns a Param with the type guessed from a Python value.""" guessed_type = _spanner_type_of_python_object(value) # BYTES must be base64-encoded, see # https://github.com/googleapis/python-spanner/blob/87789c939990794bfd91f5300bedc449fd74bd7e/google/cloud/spanner_v1/proto/type.proto#L108-L110 if (isinstance(value, bytes) and guessed_type == type_pb2.Type(code=type_pb2.BYTES)): encoded_value = base64.b64encode(value).decode() elif (isinstance(value, (list, tuple)) and all(isinstance(x, bytes) for x in value if x is not None) and guessed_type == type_pb2.Type( code=type_pb2.ARRAY, array_element_type=type_pb2.Type(code=type_pb2.BYTES), )): encoded_value = tuple( None if item is None else base64.b64encode(item).decode() for item in value) else: encoded_value = value return cls(value=encoded_value, type=guessed_type)
def compute_interest_for_account(transaction, customer_number, account_number, last_interest_calculation): # re-check (within the transaction) that the account has not been # updated for the current month results = transaction.execute_sql( """ SELECT Balance, CURRENT_TIMESTAMP() FROM Accounts WHERE CustomerNumber=@customer AND AccountNumber=@account AND (LastInterestCalculation IS NULL OR LastInterestCalculation=@calculation)""", params={ 'customer': customer_number, 'account': account_number, 'calculation': last_interest_calculation }, param_types={ 'customer': type_pb2.Type(code=type_pb2.INT64), 'account': type_pb2.Type(code=type_pb2.INT64), 'calculation': type_pb2.Type(code=type_pb2.TIMESTAMP) }) try: old_balance, current_timestamp = extract_single_row_to_tuple(results) except NoResults: # An exception means that the row has already been updated. # Abort the transaction. raise RowAlreadyUpdated # Ignoring edge-cases around new accounts and pro-rating first month cents = int(0.01 * old_balance) # monthly interest 1% new_balance = old_balance + cents deposit_helper(transaction, customer_number, account_number, cents, 'Monthly Interest', new_balance, current_timestamp) transaction.update(table='Accounts', columns=('CustomerNumber', 'AccountNumber', 'LastInterestCalculation'), values=[(customer_number, account_number, current_timestamp)])
def test_query_where_list_comparison(self, column, values, grpc_type): condition_generators = [condition.in_list, condition.not_in_list] for condition_generator in condition_generators: current_condition = condition_generator(column, values) select_query = self.select(current_condition) column_key = '{}0'.format(column) expected_sql = ' WHERE table.{} {} UNNEST(@{})'.format( column, current_condition.operator, column_key) list_type = type_pb2.Type(code=type_pb2.ARRAY, array_element_type=grpc_type) self.assertEndsWith(select_query.sql(), expected_sql) self.assertEqual(select_query.parameters(), {column_key: values}) self.assertEqual(select_query.types(), {column_key: list_type})
def customer_balance(database, customer_number): """Note: We could implement this method in terms of account_balance, but we explicitly want to demonstrate using JOIN""" params = {'customer': customer_number} param_types = {'customer': type_pb2.Type(code=type_pb2.INT64)} results = database.execute_sql( """SELECT SUM(Accounts.Balance) From Accounts INNER JOIN Customers ON Accounts.CustomerNumber=Customers.CustomerNumber WHERE Customers.CustomerNumber=@customer""", params=params, param_types=param_types) balance = extract_single_cell(results) print("CUSTOMER BALANCE", balance) return balance
def execute_transaction(transaction): query = """ SELECT COUNT(1) as num_books FROM MemberBook WHERE member_id = @member_id """ result = transaction.execute_sql( sql=query, params={'member_id': member_id}, param_types={'member_id': type_pb2.Type(code=type_pb2.STRING)}) num_books = list(result)[0][0] if (num_books >= max_books): raise ValueError("Member has too many books checked out") print("Checking out book for member") transaction.insert( table='MemberBook', columns=('library_book_id', 'member_id', 'date_checked_out'), values=[(library_book_id, member_id, current_date)] )
def grpc_type() -> type_pb2.Type: return type_pb2.Type(code=type_pb2.ARRAY)
def grpc_type() -> type_pb2.Type: return type_pb2.Type(code=type_pb2.STRING)
def grpc_type() -> type_pb2.Type: return type_pb2.Type(code=type_pb2.INT64)
# you may not use this file except in compliance with the License. # You may obtain a copy of the License at # # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. """Types exported from this package.""" from google.cloud.spanner_v1.proto import type_pb2 # Scalar parameter types STRING = type_pb2.Type(code=type_pb2.STRING) BYTES = type_pb2.Type(code=type_pb2.BYTES) BOOE = type_pb2.Type(code=type_pb2.BOOL) INT64 = type_pb2.Type(code=type_pb2.INT64) FLOAT64 = type_pb2.Type(code=type_pb2.FLOAT64) DATE = type_pb2.Type(code=type_pb2.DATE) TIMESTAMP = type_pb2.Type(code=type_pb2.TIMESTAMP) def Array(element_type): # pylint: disable=invalid-name """Construct an array paramter type description protobuf. :type element_type: :class:`type_pb2.Type` :param element_type: the type of elements of the array :rtype: :class:`type_pb2.Type`
class ConditionTest( spanner_emulator_testlib.TestCase, parameterized.TestCase, ): def setUp(self): super().setUp() self.run_orm_migrations( os.path.join( os.path.dirname(os.path.abspath(__file__)), 'migrations_for_emulator_test', )) @parameterized.parameters( (True, type_pb2.Type(code=type_pb2.BOOL)), (0, type_pb2.Type(code=type_pb2.INT64)), (0.0, type_pb2.Type(code=type_pb2.FLOAT64)), ( datetime_helpers.DatetimeWithNanoseconds(2021, 1, 5), type_pb2.Type(code=type_pb2.TIMESTAMP), ), (datetime.datetime(2021, 1, 5), type_pb2.Type(code=type_pb2.TIMESTAMP)), (datetime.date(2021, 1, 5), type_pb2.Type(code=type_pb2.DATE)), (b'\x01', type_pb2.Type(code=type_pb2.BYTES)), ('foo', type_pb2.Type(code=type_pb2.STRING)), (decimal.Decimal('1.23'), type_pb2.Type(code=type_pb2.NUMERIC)), ( (0, 1), type_pb2.Type( code=type_pb2.ARRAY, array_element_type=type_pb2.Type(code=type_pb2.INT64), ), ), ( ['a', None, 'b'], type_pb2.Type( code=type_pb2.ARRAY, array_element_type=type_pb2.Type(code=type_pb2.STRING), ), ), ) def test_param_from_value(self, value, expected_type): param = condition.Param.from_value(value) self.assertEqual(expected_type, param.type) # Test that the value and inferred type are compatible. This will raise an # exception if they're not. self.assertEmpty( models.SmallTestModel.where( condition.ArbitraryCondition( '$param IS NULL', dict(param=param), segment=condition.Segment.WHERE, ))) @parameterized.parameters( (None, 'Cannot infer type of None'), ((0, 'some-string'), 'elements of exactly one type'), ((0, 'some-string', None), 'elements of exactly one type'), (object(), 'Unknown type'), ) def test_param_from_value_error(self, value, error_regex): with self.assertRaisesRegex(TypeError, error_regex): condition.Param.from_value(value) @parameterized.named_parameters( ( 'bytes', condition.ArbitraryCondition( '$param = b"\x01\x02"', dict(param=condition.Param.from_value(b'\x01\x02')), segment=condition.Segment.WHERE, ), ), ( 'array_of_bytes', condition.ArbitraryCondition( '${param}[OFFSET(0)] = b"\x01\x02"', dict(param=condition.Param.from_value([b'\x01\x02'])), segment=condition.Segment.WHERE, ), ), ( 'array_of_bytes_and_null', condition.ArbitraryCondition( '${param}[OFFSET(0)] IS NULL', dict(param=condition.Param.from_value((None, b'\x01\x02'))), segment=condition.Segment.WHERE, ), ), ) def test_param_from_value_correctly_encodes(self, tautology): test_model = models.SmallTestModel( dict( key='some-key', value_1='some-value', value_2='other-value', )) test_model.save() self.assertCountEqual((test_model,), models.SmallTestModel.where(tautology)) @parameterized.named_parameters( ( 'minimal', condition.ArbitraryCondition( 'FALSE', segment=condition.Segment.WHERE, ), {}, {}, 'FALSE', (), ), ( 'full', condition.ArbitraryCondition( '$key = IF($true_param, ${key_param}, $value_1)', dict( key=models.SmallTestModel.key, true_param=condition.Param.from_value(True), key_param=condition.Param.from_value('some-key'), value_1=condition.Column('value_1'), ), segment=condition.Segment.WHERE, ), dict( true_param0=True, key_param0='some-key', ), dict( true_param0=type_pb2.Type(code=type_pb2.BOOL), key_param0=type_pb2.Type(code=type_pb2.STRING), ), ('SmallTestModel.key = ' 'IF(@true_param0, @key_param0, SmallTestModel.value_1)'), ('some-key',), ), ) def test_arbitrary_condition( self, condition_, expected_params, expected_types, expected_sql, expected_row_keys, ): models.SmallTestModel( dict( key='some-key', value_1='some-value', value_2='other-value', )).save() rows = models.SmallTestModel.where(condition_) self.assertEqual(expected_params, condition_.params()) self.assertEqual(expected_types, condition_.types()) self.assertEqual(expected_sql, condition_.sql()) self.assertCountEqual(expected_row_keys, tuple(row.key for row in rows)) @parameterized.named_parameters( ('key_not_found', '$not_found', KeyError, 'not_found'), ('invalid_template', '$', ValueError, 'Invalid placeholder'), ) def test_arbitrary_condition_template_error( self, template, error_class, error_regex, ): with self.assertRaisesRegex(error_class, error_regex): condition.ArbitraryCondition(template, segment=condition.Segment.WHERE) @parameterized.named_parameters( ( 'field_from_wrong_model', models.ChildTestModel.key, 'does not belong to the Model', ), ( 'column_not_found', condition.Column('not_found'), 'does not exist in the Model', ), ) def test_arbitrary_condition_validation_error( self, substitution, error_regex, ): condition_ = condition.ArbitraryCondition( '$substitution', dict(substitution=substitution), segment=condition.Segment.WHERE, ) with self.assertRaisesRegex(error.ValidationError, error_regex): models.SmallTestModel.where(condition_) @parameterized.named_parameters( ( 'empty_or', condition.OrCondition(), {}, {}, 'FALSE', '', ), ( 'empty_and', condition.OrCondition([]), {}, {}, '(TRUE)', 'ab', ), ( 'single', condition.OrCondition( [condition.equal_to(models.SmallTestModel.key, 'a')]), dict(key0='a'), dict(key0=type_pb2.Type(code=type_pb2.STRING)), '((SmallTestModel.key = @key0))', 'a', ), ( 'multiple', condition.OrCondition( [ condition.equal_to(models.SmallTestModel.key, 'a'), condition.equal_to(models.SmallTestModel.value_1, 'a'), ], [ condition.equal_to(models.SmallTestModel.key, 'b'), condition.equal_to(models.SmallTestModel.value_1, 'b'), ], ), dict( key0='a', value_11='a', key2='b', value_13='b', ), dict( key0=type_pb2.Type(code=type_pb2.STRING), value_11=type_pb2.Type(code=type_pb2.STRING), key2=type_pb2.Type(code=type_pb2.STRING), value_13=type_pb2.Type(code=type_pb2.STRING), ), ('(' '(SmallTestModel.key = @key0 AND SmallTestModel.value_1 = @value_11)' ' OR ' '(SmallTestModel.key = @key2 AND SmallTestModel.value_1 = @value_13)' ')'), 'ab', ), ) def test_or_condition( self, condition_, expected_params, expected_types, expected_sql, expected_row_keys, ): models.SmallTestModel(dict(key='a', value_1='a', value_2='a')).save() models.SmallTestModel(dict(key='b', value_1='b', value_2='b')).save() rows = models.SmallTestModel.where(condition_) self.assertEqual(expected_params, condition_.params()) self.assertEqual(expected_types, condition_.types()) self.assertEqual(expected_sql, condition_.sql()) self.assertCountEqual(expected_row_keys, tuple(row.key for row in rows)) @parameterized.parameters( ('ABCD', 'BC', True), ('ABCD', 'bc', False), ('ABCD', 'CB', False), (b'ABCD', b'BC', True), (b'ABCD', b'bc', False), (b'ABCD', b'CB', False), ('ABCD', 'BC', True, dict(case_sensitive=False)), ('ABCD', 'bc', True, dict(case_sensitive=False)), ('ABCD', 'CB', False, dict(case_sensitive=False)), (b'ABCD', b'BC', True, dict(case_sensitive=False)), (b'ABCD', b'bc', True, dict(case_sensitive=False)), (b'ABCD', b'CB', False, dict(case_sensitive=False)), ) def test_contains( self, haystack, needle, expect_results, kwargs={}, ): test_model = models.SmallTestModel(dict(key='a', value_1='a', value_2='a')) test_model.save() self.assertCountEqual( ((test_model,) if expect_results else ()), models.SmallTestModel.where( spanner_orm.contains( condition.Param.from_value(haystack), condition.Param.from_value(needle), **kwargs, )), )
def _types(self) -> type_pb2.Type: grpc_type = self.model_class.fields[self.column].grpc_type() list_type = type_pb2.Type(code=type_pb2.ARRAY, array_element_type=grpc_type) return {self._column_key: list_type}
def grpc_type() -> type_pb2.Type: return type_pb2.Type(code=type_pb2.TIMESTAMP)
def grpc_type() -> type_pb2.Type: return type_pb2.Type(code=type_pb2.BOOL)
def _types(self) -> Dict[str, type_pb2.Type]: types = {self._limit_key: type_pb2.Type(code=type_pb2.INT64)} if self.offset: types[self._offset_key] = type_pb2.Type(code=type_pb2.INT64) return types