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`