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
