def test_it(self): from google.cloud.proto.spanner.v1 import type_pb2 from google.cloud.spanner.types import INT64_PARAM_TYPE from google.cloud.spanner.types import STRING_PARAM_TYPE from google.cloud.spanner.types import StructParamType from google.cloud.spanner.types import StructField 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 = StructParamType([ StructField('name', STRING_PARAM_TYPE), StructField('count', INT64_PARAM_TYPE), ]) self.assertEqual(found, expected)
def test_it(self): from google.cloud.proto.spanner.v1 import type_pb2 from google.cloud.spanner.types import ArrayParamType from google.cloud.spanner.types import INT64_PARAM_TYPE expected = type_pb2.Type( code=type_pb2.ARRAY, array_element_type=type_pb2.Type(code=type_pb2.INT64)) found = ArrayParamType(INT64_PARAM_TYPE) 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 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 ArrayParamType(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 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.proto.spanner.v1 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 StructParamType(fields): # pylint: disable=invalid-name """Construct a struct paramter 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 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 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
# 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.proto.spanner.v1 import type_pb2 # Scalar paramter types STRING_PARAM_TYPE = type_pb2.Type(code=type_pb2.STRING) BYTES_PARAM_TYPE = type_pb2.Type(code=type_pb2.BYTES) BOOL_PARAM_TYPE = type_pb2.Type(code=type_pb2.BOOL) INT64_PARAM_TYPE = type_pb2.Type(code=type_pb2.INT64) FLOAT64_PARAM_TYPE = type_pb2.Type(code=type_pb2.FLOAT64) DATE_PARAM_TYPE = type_pb2.Type(code=type_pb2.DATE) TIMESTAMP_PARAM_TYPE = type_pb2.Type(code=type_pb2.TIMESTAMP) def ArrayParamType(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`