def __init__(
self,
instance: str,
database: str,
project: Optional[str] = None,
credentials: Optional[auth_credentials.Credentials] = None,
pool: Optional[spanner_pool.AbstractSessionPool] = None,
create_ddl: Optional[Iterable[str]] = None,
client_info: Optional[ClientInfo] = None,
client_options=None,
query_options=None,
):
"""Connects to the specified Spanner database."""
params = dict(
project=project,
credentials=credentials,
client_options=client_options,
query_options=query_options,
)
if client_info is not None:
params["client_info"] = client_info
client = spanner.Client(**params)
instance = client.instance(instance)
self.database = instance.database(database,
pool=pool,
ddl_statements=create_ddl or ())
def query_nested_struct_field(instance_id, database_id):
"""Query a table using nested field access on a STRUCT parameter. """
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
song_info_type = param_types.Struct([
param_types.StructField('SongName', param_types.STRING),
param_types.StructField(
'ArtistNames',
param_types.Array(
param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])))
])
song_info = ('Imagination', [('Elena', 'Campbell'), ('Hannah', 'Harris')])
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId, @song_info.SongName "
"FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) "
"IN UNNEST(@song_info.ArtistNames)",
params={'song_info': song_info},
param_types={'song_info': song_info_type})
for row in results:
print(u'SingerId: {} SongName: {}'.format(*row))
def create_database(instance_id, database_id):
"""Creates a database and tables for sample data."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id,
ddl_statements=[
"""CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId)""", """CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX)
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE"""
])
operation = database.create()
print('Waiting for operation to complete...')
operation.result()
print('Created database {} on instance {}'.format(database_id,
instance_id))
def insert_data(instance_id, database_id):
"""Inserts sample data into the given database.
The database and table must already exist and can be created using
`create_database`.
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.batch() as batch:
batch.insert(table='Singers',
columns=(
'SingerId',
'FirstName',
'LastName',
),
values=[(1, u'Marc', u'Richards'),
(2, u'Catalina', u'Smith'),
(3, u'Alice', u'Trentor'), (4, u'Lea', u'Martin'),
(5, u'David', u'Lomond')])
batch.insert(table='Albums',
columns=(
'SingerId',
'AlbumId',
'AlbumTitle',
),
values=[(1, 1, u'Total Junk'), (1, 2, u'Go, Go, Go'),
(2, 1, u'Green'),
(2, 2, u'Forever Hold Your Peace'),
(2, 3, u'Terrified')])
print('Inserted data.')
def query_with_struct(instance_id, database_id):
"""Query a table using STRUCT parameters. """
# [START spanner_create_struct_with_data]
record_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
record_value = ('Elena', 'Campbell')
# [END spanner_create_struct_with_data]
# [START spanner_query_data_with_struct]
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId FROM Singers WHERE "
"(FirstName, LastName) = @name",
params={'name': record_value},
param_types={'name': record_type})
for row in results:
print(u'SingerId: {}'.format(*row))
def run_quickstart():
# [START spanner_quickstart]
# Imports the Google Cloud Client Library.
from google.cloud import spanner
# Instantiate a client.
spanner_client = spanner.Client()
# Your Cloud Spanner instance ID.
instance_id = "my-instance-id"
# Get a Cloud Spanner instance by ID.
instance = spanner_client.instance(instance_id)
# Your Cloud Spanner database ID.
database_id = "my-database-id"
# Get a Cloud Spanner database by ID.
database = instance.database(database_id)
# Execute a simple SQL statement.
with database.snapshot() as snapshot:
results = snapshot.execute_sql("SELECT 1")
for row in results:
print(row)
def update_with_batch_dml(instance_id, database_id):
"""Updates sample data in the database using Batch DML. """
# [START spanner_dml_batch_update]
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
insert_statement = ("INSERT INTO Albums "
"(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
"VALUES (1, 3, 'Test Album Title', 10000)")
update_statement = ("UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 3")
def update_albums(transaction):
row_cts = transaction.batch_update([
insert_statement,
update_statement,
])
print("Executed {} SQL statements using Batch DML.".format(
len(row_cts)))
database.run_in_transaction(update_albums)
def list_backup_operations(instance_id, database_id, backup_id):
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
# List the CreateBackup operations.
filter_ = ("(metadata.@type:type.googleapis.com/"
"google.spanner.admin.database.v1.CreateBackupMetadata) "
"AND (metadata.database:{})").format(database_id)
operations = instance.list_backup_operations(filter_=filter_)
for op in operations:
metadata = op.metadata
print("Backup {} on database {}: {}% complete.".format(
metadata.name, metadata.database,
metadata.progress.progress_percent))
# List the CopyBackup operations.
filter_ = (
"(metadata.@type:type.googleapis.com/google.spanner.admin.database.v1.CopyBackupMetadata) "
"AND (metadata.source_backup:{})").format(backup_id)
operations = instance.list_backup_operations(filter_=filter_)
for op in operations:
metadata = op.metadata
print("Backup {} on source backup {}: {}% complete.".format(
metadata.name,
metadata.source_backup,
metadata.progress.progress_percent,
))
def create_database_with_version_retention_period(instance_id, database_id,
retention_period):
"""Creates a database with a version retention period."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
ddl_statements = [
"CREATE TABLE Singers (" + " SingerId INT64 NOT NULL," +
" FirstName STRING(1024)," + " LastName STRING(1024)," +
" SingerInfo BYTES(MAX)" + ") PRIMARY KEY (SingerId)",
"CREATE TABLE Albums (" + " SingerId INT64 NOT NULL," +
" AlbumId INT64 NOT NULL," + " AlbumTitle STRING(MAX)" +
") PRIMARY KEY (SingerId, AlbumId)," +
" INTERLEAVE IN PARENT Singers ON DELETE CASCADE",
"ALTER DATABASE `{}`"
" SET OPTIONS (version_retention_period = '{}')".format(
database_id, retention_period),
]
db = instance.database(database_id, ddl_statements)
operation = db.create()
operation.result(30)
db.reload()
print(
"Database {} created with version retention period {} and earliest version time {}"
.format(db.database_id, db.version_retention_period,
db.earliest_version_time))
db.drop()
def init(self, conf):
self.conf = conf.with_fallback(SpannerMetadataExtractor.DEFAULT_CONFIG)
self._project_id = conf.get_string(
SpannerMetadataExtractor.PROJECT_ID_KEY)
self._connection_name = conf.get_string(
SpannerMetadataExtractor.CONNECTION_NAME_KEY)
self._instance_id = self.conf.get_string(
SpannerMetadataExtractor.INSTANCE_ID_KEY)
self._database_id = self.conf.get(
SpannerMetadataExtractor.DATABASE_ID_KEY, None)
self._key_path = self.conf.get(SpannerMetadataExtractor.KEY_PATH_KEY,
None)
client_kwargs = {"project": self._project_id}
if self._key_path is not None:
spanner_client = spanner.Client.from_service_account_json(
self._key_path, **client_kwargs)
else:
spanner_client = spanner.Client(**client_kwargs)
self.instance = spanner_client.instance(self._instance_id)
self.database = self.instance.database(self._database_id)
self.sql_stmt = SpannerMetadataExtractor.SQL_STATEMENT.format(
where_clause_suffix=self.conf.get_string(
SpannerMetadataExtractor.WHERE_CLAUSE_SUFFIX_KEY))
LOGGER.info("SQL for Spanner: {}".format(self.sql_stmt))
self._extract_iter = None # type: Union[None, Iterator]
def restore_database_with_encryption_key(instance_id, new_database_id,
backup_id, kms_key_name):
"""Restores a database from a backup using a Customer Managed Encryption Key (CMEK)."""
from google.cloud.spanner_admin_database_v1 import RestoreDatabaseEncryptionConfig
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
# Start restoring an existing backup to a new database.
backup = instance.backup(backup_id)
encryption_config = {
"encryption_type": RestoreDatabaseEncryptionConfig.EncryptionType.
CUSTOMER_MANAGED_ENCRYPTION,
"kms_key_name": kms_key_name,
}
new_database = instance.database(new_database_id,
encryption_config=encryption_config)
operation = new_database.restore(backup)
# Wait for restore operation to complete.
operation.result(1600)
# Newly created database has restore information.
new_database.reload()
restore_info = new_database.restore_info
print(
"Database {} restored to {} from backup {} with using encryption key {}."
.format(
restore_info.backup_info.source_database,
new_database_id,
restore_info.backup_info.backup,
new_database.encryption_config.kms_key_name,
))
def create_database(instance_id, database_id):
print('create_database called')
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id,
ddl_statements=[
"""CREATE TABLE Airports (
ident STRING(1024),
type STRING(1024),
name STRING(1024),
elevation_ft INT64,
continent STRING(1024),
iso_country STRING(1024),
iso_region STRING(1024),
municipality STRING(1024),
gps_code STRING(1024),
iata_code STRING(1024),
local_code STRING(1024),
coordinates STRING(1024)
) PRIMARY KEY (ident)"""
])
operation = database.create()
print('Waiting for operation to complete...')
operation.result(120)
print('Created database {} on instance {}'.format(database_id,
instance_id))
def get_instance(instance_id):
"""Gets Cloud Spanner Instance ID."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
return instance
def get_database():
credentials, project = get_auth()
client = gcloud_spanner.Client(project=project, credentials=credentials)
instance = client.instance(os.environ.get("INSTANCE", "test-instance"))
database = instance.database(os.environ.get("DATABASE", "test-database"))
return database
def create_backup(instance_id, database_id, backup_id):
"""Creates a backup for a database."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
# Create a backup
expire_time = datetime.utcnow() + timedelta(days=14)
backup = instance.backup(backup_id, database=database, expire_time=expire_time)
operation = backup.create()
# Wait for backup operation to complete.
operation.result(1200)
# Verify that the backup is ready.
backup.reload()
assert backup.is_ready() is True
# Get the name, create time and backup size.
backup.reload()
print(
"Backup {} of size {} bytes was created at {}".format(
backup.name, backup.size_bytes, backup.create_time
)
)
def copy_backup(instance_id, backup_id, source_backup_path):
"""Copies a backup."""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
# Create a backup object and wait for copy backup operation to complete.
expire_time = datetime.utcnow() + timedelta(days=14)
copy_backup = instance.copy_backup(backup_id=backup_id,
source_backup=source_backup_path,
expire_time=expire_time)
operation = copy_backup.create()
# Wait for copy backup operation to complete.
operation.result(2100)
# Verify that the copy backup is ready.
copy_backup.reload()
assert copy_backup.is_ready() is True
print("Backup {} of size {} bytes was created at {} with version time {}".
format(
copy_backup.name,
copy_backup.size_bytes,
copy_backup.create_time,
copy_backup.version_time,
))
def cancel_backup(instance_id, database_id, backup_id):
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
expire_time = datetime.utcnow() + timedelta(days=30)
# Create a backup.
backup = instance.backup(backup_id,
database=database,
expire_time=expire_time)
operation = backup.create()
# Cancel backup creation.
operation.cancel()
# Cancel operations are best effort so either it will complete or
# be cancelled.
while not operation.done():
time.sleep(300) # 5 mins
# Deal with resource if the operation succeeded.
if backup.exists():
print("Backup was created before the cancel completed.")
backup.delete()
print("Backup deleted.")
else:
print("Backup creation was successfully cancelled.")
def page_index():
"""Main test entry point."""
r = ['Spanner client test:']
try:
spanner_client = spanner.Client()
instance = spanner_client.instance('test-instance')
database = instance.database('example-db')
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT 'Success from Spanner!' as message")
for row in results:
r.append(row[0])
# Stop after the first result row, because google-cloud-spanner 1.3.0
# hasn't been updated for Python 3.7 yet, and throws a RuntimeError when
# it hits the end:
# https://github.com/GoogleCloudPlatform/google-cloud-python/issues/5282
break
except Exception as e: # pylint: disable=broad-except
r.append('Error during Spanner access: %s\n%s' %
(repr(e), traceback.format_exc()))
return ('\n'.join(r) + '\n'), 200, {'Content-Type': 'text/plain'}
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 query_data_with_index(instance_id, database_id):
"""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
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
results = database.execute_sql(
"SELECT AlbumId, AlbumTitle, MarketingBudget "
"FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} "
"WHERE AlbumTitle >= 'Ardvark' AND AlbumTitle < 'Goo'")
for row in results:
print(
u'AlbumId: {}, AlbumTitle: {}, '
'MarketingBudget: {}'.format(*row))
def insert_data_with_timestamp(instance_id, database_id):
"""Inserts data with a COMMIT_TIMESTAMP field into a table. """
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.batch() as batch:
batch.insert(table='Performances',
columns=(
'SingerId',
'VenueId',
'EventDate',
'Revenue',
'LastUpdateTime',
),
values=[
(1, 4, "2017-10-05", 11000, spanner.COMMIT_TIMESTAMP),
(1, 19, "2017-11-02", 15000,
spanner.COMMIT_TIMESTAMP),
(2, 42, "2017-12-23", 7000, spanner.COMMIT_TIMESTAMP)
])
print('Inserted data.')
def read_write_transaction(instance_id, database_id):
"""Performs a read-write transaction to update two sample records in the
database.
This will transfer 200,000 from the `MarketingBudget` field for the second
Album to the first Album. If the `MarketingBudget` is too low, it will
raise an exception.
Before running this sample, you will need to run the `update_data` sample
to populate the fields.
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
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)])
database.run_in_transaction(update_albums)
print('Transaction complete.')
def update_data_with_timestamp(instance_id, database_id):
"""Updates Performances tables in the database with the COMMIT_TIMESTAMP
column.
This updates the `MarketingBudget` column which must be created before
running this sample. 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
In addition this update expects the LastUpdateTime column added by
applying this DDL statement against your database:
ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP
OPTIONS(allow_commit_timestamp=true)
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.batch() as batch:
batch.update(table='Albums',
columns=('SingerId', 'AlbumId', 'MarketingBudget',
'LastUpdateTime'),
values=[(1, 1, 1000000, spanner.COMMIT_TIMESTAMP),
(2, 2, 750000, spanner.COMMIT_TIMESTAMP)])
print('Updated data.')
def run_writer():
# Instantiate a client.
spanner_client = spanner.Client()
# Your Cloud Spanner instance ID.
instance_id = 'meetup'
# Get a Cloud Spanner instance by ID.
instance = spanner_client.instance(instance_id)
# Your Cloud Spanner database ID.
database_id = 'performance-log'
# Get a Cloud Spanner database by ID.
database = instance.database(database_id)
while True:
data = 'a' * 2000
values = []
row_id = (uuid.uuid4().int & (1 << 63) - 1)
for i in range(500):
row_id += 1
utilization = random.randint(0, 100)
ts = datetime.datetime.now()
values.append((row_id, data, ts, utilization))
with database.batch() as batch:
batch.insert(table='cpu',
columns=('id', 'data', 'ts', 'utilization'),
values=values)
def query_with_array_of_struct(instance_id, database_id):
"""Query a table using an array of STRUCT parameters. """
# [START spanner_create_user_defined_struct]
name_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
# [END spanner_create_user_defined_struct]
# [START spanner_create_array_of_struct_with_data]
band_members = [("Elena", "Campbell"), ("Gabriel", "Wright"),
("Benjamin", "Martinez")]
# [END spanner_create_array_of_struct_with_data]
# [START spanner_query_data_with_array_of_struct]
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) IN UNNEST(@names)",
params={'names': band_members},
param_types={'names': param_types.Array(name_type)})
for row in results:
print(u'SingerId: {}'.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 update_data_with_dml_struct(instance_id, database_id):
"""Updates data with a DML statement and STRUCT parameters. """
# [START spanner_dml_structs]
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
record_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
record_value = ('Timothy', 'Campbell')
def write_with_struct(transaction):
row_ct = transaction.execute_update(
"UPDATE Singers SET LastName = 'Grant' "
"WHERE STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) = @name",
params={'name': record_value},
param_types={'name': record_type})
print("{} record(s) updated.".format(row_ct))
database.run_in_transaction(write_with_struct)
def insert_data(instance_id, database_id, data_file):
"""Inserts sample data into the given database.
The database and table must already exist and can be created using
`create_database`.
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
dat = []
f = open(data_file,"r") #opens file with name of "test.txt"
for line in f:
dat.append(line)
dat = [x.strip() for x in dat] # Strips newline and spaces
table_name = dat.pop(0)
print('Table name: ' + table_name)
dat = [make_tuple(x) for x in dat] # Makes tuples as expected by the insert function
col_names = dat.pop(0)
print('Column Names: ' + str(col_names))
f.close()
with database.batch() as batch:
batch.insert(
table=table_name,
columns=col_names,
values=dat)
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 raw_sql(self, query: str, results=False, params=None):
query_parameters = [
cloud_spanner_param(param, value) for param, value in (params or {}).items()
]
spanner_client = spanner.Client()
instance_id = self.instance_id
instance = spanner_client.instance(instance_id)
database_id = self.dataset_id
database_1 = instance.database(database_id)
with database_1.snapshot() as snapshot:
if query_parameters:
param = {}
param_type = {}
for i in query_parameters:
param.update(i["params"])
param_type.update(i["param_types"])
results = snapshot.execute_sql(
query, params=param, param_types=param_type
)
else:
results = snapshot.execute_sql(query)
sp = SpannerCursor(results)
return sp