def clear_tables(database):
with database.batch() as batch:
batch.delete(table='AccountHistory', keyset=spanner.KeySet(all_=True))
batch.delete(table='Accounts', keyset=spanner.KeySet(all_=True))
batch.delete(table='Customers', keyset=spanner.KeySet(all_=True))
if AGGREGATE_BALANCE_SHARDS > 0:
batch.delete(table='AggregateBalance',
keyset=spanner.KeySet(all_=True))
def read_only_transaction(instance_id, database_id):
"""Reads data inside of a read-only transaction.
Within the read-only transaction, or "snapshot", the application sees
consistent view of the database at a particular timestamp.
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot(multi_use=True) as snapshot:
# Read using SQL.
results = snapshot.execute_sql(
'SELECT SingerId, AlbumId, AlbumTitle FROM Albums')
print('Results from first read:')
for row in results:
print(u'SingerId: {}, AlbumId: {}, AlbumTitle: {}'.format(*row))
# Perform another read using the `read` method. Even if the data
# is updated in-between the reads, the snapshot ensures that both
# return the same data.
keyset = spanner.KeySet(all_=True)
results = snapshot.read(
table='Albums',
columns=(
'SingerId',
'AlbumId',
'AlbumTitle',
),
keyset=keyset,
)
print('Results from second read:')
for row in results:
print(u'SingerId: {}, AlbumId: {}, AlbumTitle: {}'.format(*row))
def test_spanner_update(self):
_prefex = 'test_update'
# adding data to perform test
instance = self._SPANNER_INSTANCE
database = instance.database(self.TEST_DATABASE)
data = [
(_prefex + '1', _prefex + 'inset-1'),
(_prefex + '2', _prefex + 'inset-2'),
(_prefex + '3', _prefex + 'inset-3'),
]
with database.batch() as batch:
batch.insert(table='Users', columns=('UserId', 'Key'), values=data)
mutations_update = [
WriteMutation.update('Users', ('UserId', 'Key'),
[(_prefex + '1', _prefex + 'update-1')]),
WriteMutation.update('Users', ('UserId', 'Key'),
[(_prefex + '2', _prefex + 'update-2')]),
WriteMutation.delete('Users',
spanner.KeySet(keys=[[_prefex + '3']]))
]
p = beam.Pipeline(argv=self.args)
_ = (p | beam.Create(mutations_update)
| WriteToSpanner(project_id=self.project,
instance_id=self.instance,
database_id=self.TEST_DATABASE))
res = p.run()
res.wait_until_finish()
self.assertEqual(self._count_data(_prefex), 2)
def find_multi(
cls,
transaction: Optional[spanner_transaction.Transaction],
keys: Iterable[Dict[str, Any]],
) -> List["ModelObject"]:
"""Retrieves objects from Spanner based on the provided keys.
Args:
transaction: The existing transaction to use, or None to start a new
transaction
keys: An iterable of dictionaries, each dictionary representing the set of
primary key values necessary to uniquely identify an object in this
table.
Returns:
A list containing all requested objects that exist in the table (can be
an empty list)
"""
key_values = []
for key in keys:
key_values.append([key[column] for column in cls.primary_keys])
keyset = spanner.KeySet(keys=key_values)
args = [cls.table, cls.columns, keyset]
results = cls._execute_read(table_apis.find, transaction, args)
return cls._results_to_models(results)
def test_spanner_write_mutation_groups(self, mock_batch_snapshot_class,
mock_batch_checkout):
ks = spanner.KeySet(keys=[[1233], [1234]])
mutation_groups = [
MutationGroup([
WriteMutation.insert("roles", ("key", "rolename"),
[('9001233', "mutations-inset-1233")]),
WriteMutation.insert("roles", ("key", "rolename"),
[('9001234', "mutations-inset-1234")])
]),
MutationGroup([
WriteMutation.update(
"roles", ("key", "rolename"),
[('9001234', "mutations-inset-9001233-updated")])
]),
MutationGroup([WriteMutation.delete("roles", ks)])
]
p = TestPipeline()
_ = (p
| beam.Create(mutation_groups)
| WriteToSpanner(project_id=TEST_PROJECT_ID,
instance_id=TEST_INSTANCE_ID,
database_id=_generate_database_name(),
max_batch_size_bytes=100))
res = p.run()
res.wait_until_finish()
metric_results = res.metrics().query(
MetricsFilter().with_name('SpannerBatches'))
batches_counter = metric_results['counters'][0]
self.assertEqual(batches_counter.committed, 3)
self.assertEqual(batches_counter.attempted, 3)
def read_data_with_storing_index(instance_id, database_id):
"""Reads sample data from the database using an index with a storing
clause.
The index must exist before running this sample. You can add the index
by running the `add_soring_index` sample or by running this DDL statement
against your database:
CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)
STORING (MarketingBudget)
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
keyset = spanner.KeySet(all_=True)
results = snapshot.read(table='Albums',
columns=('AlbumId', 'AlbumTitle',
'MarketingBudget'),
keyset=keyset,
index='AlbumsByAlbumTitle2')
for row in results:
print(u'AlbumId: {}, AlbumTitle: {}, '
'MarketingBudget: {}'.format(*row))
def run_batch_query(instance_id, database_id):
"""Runs an example batch query."""
# Expected Table Format:
# CREATE TABLE Singers (
# SingerId INT64 NOT NULL,
# FirstName STRING(1024),
# LastName STRING(1024),
# SingerInfo BYTES(MAX),
# ) PRIMARY KEY (SingerId);
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
# Create the batch transaction and generate partitions
snapshot = database.batch_snapshot()
partitions = snapshot.generate_read_batches(
table="Singers",
columns=("SingerId", "FirstName", "LastName"),
keyset=spanner.KeySet(all_=True),
)
# Create a pool of workers for the tasks
start = time.time()
with concurrent.futures.ThreadPoolExecutor() as executor:
futures = [executor.submit(process, snapshot, p) for p in partitions]
for future in concurrent.futures.as_completed(futures, timeout=3600):
finish, row_ct = future.result()
elapsed = finish - start
print(u"Completed {} rows in {} seconds".format(row_ct, elapsed))
# Clean up
snapshot.close()
def delete_all_from_table(self, table_name):
"""Delete all values from a given table.
Args:
table_name (str): the name of the table to delete from
"""
with self.database.batch() as batch:
remaining_values = spanner.KeySet(all_=True)
batch.delete(table_name, remaining_values)
def delete_data(instance_id, database_id):
"""Deletes sample data from the given database.
The database, table, and data must already exist and can be created using
`create_database` and `insert_data`.
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
singers_to_delete = spanner.KeySet(keys=[[1], [2], [3], [4], [5]])
albums_to_delete = spanner.KeySet(
keys=[[1, 1], [1, 2], [2, 1], [2, 2], [2, 3]])
with database.batch() as batch:
batch.delete('Albums', albums_to_delete)
batch.delete('Singers', singers_to_delete)
print('Deleted data.')
def test_delete_by_key_deletes(self, delete):
mock_transaction = mock.Mock()
models.SmallTestModel.delete_by_key(
key='some-key',
transaction=mock_transaction,
)
delete.assert_called_once_with(
mock_transaction,
models.SmallTestModel.table,
spanner.KeySet(keys=[['some-key']]),
)
def update_albums(transaction):
# Read the second album budget.
second_album_keyset = spanner.KeySet(keys=[(2, 2)])
second_album_result = transaction.read(
table='Albums', columns=('MarketingBudget',),
keyset=second_album_keyset, limit=1)
second_album_row = list(second_album_result)[0]
second_album_budget = second_album_row[0]
transfer_amount = 200000
if second_album_budget < transfer_amount:
# Raising an exception will automatically roll back the
# transaction.
raise ValueError(
'The second album doesn\'t have enough funds to transfer')
# Read the first album's budget.
first_album_keyset = spanner.KeySet(keys=[(1, 1)])
first_album_result = transaction.read(
table='Albums', columns=('MarketingBudget',),
keyset=first_album_keyset, limit=1)
first_album_row = list(first_album_result)[0]
first_album_budget = first_album_row[0]
# Update the budgets.
second_album_budget -= transfer_amount
first_album_budget += transfer_amount
print(
'Setting first album\'s budget to {} and the second album\'s '
'budget to {}.'.format(
first_album_budget, second_album_budget))
# Update the rows.
transaction.update(
table='Albums',
columns=(
'SingerId', 'AlbumId', 'MarketingBudget'),
values=[
(1, 1, first_album_budget),
(2, 2, second_album_budget)])
def read_data(instance_id, database_id):
"""Reads sample data from the database."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
keyset = spanner.KeySet(all_=True)
results = database.read(
table='Albums',
columns=('SingerId', 'AlbumId', 'AlbumTitle',),
keyset=keyset,)
for row in results:
print(u'SingerId: {}, AlbumId: {}, AlbumTitle: {}'.format(*row))
def search(self, table_name, column_names=None) -> dict:
"""Get a row from a table with matching ID."""
if not column_names:
# TODO: faster lookup on column names
column_names = self.get_table_mapping().get(table_name).keys()
with self.database.snapshot() as snapshot:
result = snapshot.read(
table=table_name,
columns=column_names,
keyset=spanner.KeySet(all_=True),
)
for row in result:
yield dict(zip(column_names, row))
def delete(self, transaction: spanner_transaction.Transaction = None) -> None:
"""Deletes this object from the Spanner database.
Args:
transaction: The existing transaction to use, or None to start a new
transaction
"""
key = [getattr(self, column) for column in self._primary_keys]
keyset = spanner.KeySet([key])
db_api = table_apis.delete
args = [self._table, keyset]
if transaction is not None:
db_api(transaction, *args)
else:
self.spanner_api().run_write(db_api, *args)
def row_exists(self, table_name, id_):
"""Check whether a row is exist."""
exists = False
with self.database.snapshot() as snapshot:
result = snapshot.read(
table=table_name,
columns=["doc_id"],
keyset=spanner.KeySet([[id_]]),
limit=1,
)
with suppress(IndexError, NotFound):
row = list(result)[0]
if row:
exists = True
return exists
def function_read_spanner_generic(data, instancename):
"""Background Cloud Function to be triggered by Pub/Sub listener..
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.loads(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')
count: int = 0
try:
while count < 1000:
with database.snapshot() as snapshot:
keyset = spanner.KeySet(keys=[(int(productid) + int(count),
int(upc) + int(count))])
results = snapshot.read(
table='availability_by_productid_upc',
columns=(
'productid',
'upc',
'fiaavail',
),
keyset=keyset,
)
for row in results:
print(u'productid: {}, upc: {}, fiaavail: {}'.format(
*row))
count += 1
except Exception as e:
print(f' Exception occurred : {e} ')
else:
print('No data to read')
print('spanner generic read function completed')
def all(
cls, transaction: Optional[spanner_transaction.Transaction] = None
) -> List["ModelObject"]:
"""Returns all objects of this type stored in Spanner.
Note: this method should only be called on subclasses of Model that have
a table associated with it. Violating this will cause an exception to be
raised.
Args:
transaction: The existing transaction to use, or None to start a new
transaction
Returns:
A list of models, one per row in the associated Spanner table
"""
args = [cls.table, cls.columns, spanner.KeySet(all_=True)]
results = cls._execute_read(table_apis.find, transaction, args)
return cls._results_to_models(results)
def backup_data(tables):
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
counter = 0
rows = []
for tab in tables:
counter = counter+1
with database.snapshot() as snapshot:
keyset = spanner.KeySet(all_=True)
# todo figure out how to specify all columns
results = snapshot.read(
table=tab,
columns=(),
keyset=keyset
)
rows.append(results)
if counter == 15:
counter = 0
make_json(results);
def get(self, table_name, id_, column_names=None) -> dict:
"""Get a row from a table with matching ID."""
if not column_names:
# TODO: faster lookup on column names
column_names = self.get_table_mapping().get(table_name).keys()
entry = {}
with self.database.snapshot() as snapshot:
result = snapshot.read(
table=table_name,
columns=column_names,
keyset=spanner.KeySet([[id_]]),
limit=1,
)
with suppress(IndexError, NotFound):
row = list(result)[0]
entry = dict(zip(column_names, row))
return entry
def delete_by_key(
cls,
*,
transaction: Optional[spanner_transaction.Transaction] = None,
**keys: Any,
) -> None:
"""Deletes rows from Spanner based on the provided primary key.
Args:
transaction: The existing transaction to use, or None to start a new
transaction.
**keys: The keys provided are the complete set of primary keys for this
table and the corresponding values make up the unique identifier of the
object being deleted.
"""
cls._delete_by_keyset(
transaction=transaction,
keyset=spanner.KeySet(
keys=[[keys[column] for column in cls.primary_keys]]),
)
def delete_batch(
cls: Type[T],
models: List[T],
*,
transaction: Optional[spanner_transaction.Transaction] = None,
) -> None:
"""Deletes rows from Spanner based on the provided models' primary keys.
Args:
models: A list of models to be deleted from Spanner.
transaction: The existing transaction to use, or None to start a new
transaction
"""
key_list = []
for model in models:
key_list.append(
[getattr(model, column) for column in cls.primary_keys])
cls._delete_by_keyset(
transaction=transaction,
keyset=spanner.KeySet(keys=key_list),
)
def read_stale_data(instance_id, database_id):
"""Reads sample data from the database. The data is exactly 15 seconds
stale."""
import datetime
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
staleness = datetime.timedelta(seconds=15)
with database.snapshot(exact_staleness=staleness) as snapshot:
keyset = spanner.KeySet(all_=True)
results = snapshot.read(table='Albums',
columns=(
'SingerId',
'AlbumId',
'AlbumTitle',
),
keyset=keyset)
for row in results:
print(u'SingerId: {}, AlbumId: {}, AlbumTitle: {}'.format(*row))
def read_data_with_index(instance_id, database_id):
"""Reads sample data from the database using 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)
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
keyset = spanner.KeySet(all_=True)
results = database.read(table='Albums',
columns=('AlbumId', 'AlbumTitle'),
keyset=keyset,
index='AlbumsByAlbumTitle')
for row in results:
print('AlbumId: {}, AlbumTitle: {}'.format(*row))
def delete_batch(cls,
transaction: Optional[spanner_transaction.Transaction],
models: List[ModelObject]) -> None:
"""Deletes rows from Spanner based on the provided models' primary keys.
Args:
transaction: The existing transaction to use, or None to start a new
transaction
models: A list of models to be deleted from Spanner.
"""
key_list = []
for model in models:
key_list.append(
[getattr(model, column) for column in cls.primary_keys])
keyset = spanner.KeySet(keys=key_list)
db_api = table_apis.delete
args = [cls.table, keyset]
if transaction is not None:
db_api(transaction, *args)
else:
cls.spanner_api().run_write(db_api, *args)
def update_avail(transaction):
print('In Update Avail function 1 ')
# Read the timestamp.
existing_avail_keyset = spanner.KeySet(keys=[(productid, upc)])
existing_avail_result = transaction.read(
table='availability_by_productid_upc',
columns=('lastmodified', ),
keyset=existing_avail_keyset,
limit=1)
print('In Update Avail function 2 ')
if existing_avail_result is None:
print('Start inserting the data in the table ')
with database.batch() as batch:
batch.insert(table='availability_by_productid_upc',
columns=(
'productid',
'upc',
'division',
'fiaavail',
'ordermethod',
'pricelevel',
'pricelevelid',
'returnscode',
'maxqty',
'reasoncode',
'lowavailability',
'shiprep',
'lastmodified',
),
values=[
(productid, upc, div, avail, ordermethod,
pricelevel, pricelevelid, returnscode,
maxqty, reasoncode, lowavailability,
shiprep, lastmodified)
])
print(
f' Table has been update for UPC : {upc} and message {msg}'
)
else:
print('Row Exists, Compare the timestamp ')
existing_avail_row = list(existing_avail_result)[0]
existing_avail_timestamp = existing_avail_row[0]
print(
f' new timestamp : {lastmodified} and previous timestamp {existing_avail_timestamp}'
)
if lastmodified < existing_avail_timestamp:
# Raising an exception will automatically roll back the
# transaction.
raise ValueError(
'The data is not latest . Latest record is already present'
)
# Update the rows.
transaction.update(
table='availability_by_productid_upc',
columns=('productid', 'upc', 'division', 'fiaavail',
'ordermethod', 'pricelevel', 'pricelevelid',
'returnscode', 'maxqty', 'reasoncode',
'lowavailability', 'shiprep', 'lastmodified'),
values=[
(productid, upc, div, avail, ordermethod, pricelevel,
pricelevelid, returnscode, maxqty, reasoncode,
lowavailability, shiprep, lastmodified)
])
database.run_in_transaction(update_avail)
print('Transaction complete.')
import flask
import requests
import os
from google.cloud import spanner
k = spanner.KeySet(all_=True)
instance_id = 'autofocus-instance'
database_id = 'autofocus-database'
database = None
columns = ['postcardId', 'comment', 'img', 'x', 'y']
defaults = ['', '', '', 0, 0]
def get_db():
c = spanner.Client(project=os.environ['GCLOUD_PROJECT_NAME'])
i = c.instance(instance_id)
d = i.database(database_id)
return d
def serialize(postcard_tuple):
return dict(zip(columns, postcard_tuple))
def upsertPostcard(postcard):
global database
if not database:
database = get_db()
def delete_customer_record(fxa_id):
customer_to_delete = spanner.KeySet(keys=[[fxa_id]])
with spanner_database.batch() as batch:
batch.delete('customer_record', customer_to_delete)
return True
