Skip to content

Latest commit

 

History

History
251 lines (162 loc) · 7.63 KB

database-usage.rst

File metadata and controls

251 lines (162 loc) · 7.63 KB

Database Admin

After creating a :class:`~google.cloud.spanner.instance.Instance`, you can interact with individual databases for that instance.

List Databases

To iterate over all existing databases for an instance, use its :meth:`~google.cloud.spanner.instance.Instance.list_databases` method:

for database in instance.list_databases():
    # `database` is a `Database` object.

This method yields :class:`~.spanner_admin_database_v1.types.Database` objects.

Database Factory

To create a :class:`~google.cloud.spanner.database.Database` object:

database = instance.database(database_id, ddl_statements)
  • ddl_statements is a string containing DDL for the new database.

You can also use :meth:`Instance.database` to create a local wrapper for a database that has already been created:

database = instance.database(existing_database_id)

Create a new Database

After creating the database object, use its :meth:`~google.cloud.spanner.database.Database.create` method to trigger its creation on the server:

operation = database.create()

Note

Creating an instance triggers a "long-running operation" and returns an :class:`~concurrent.futures.Future`-like object. Use the :meth:`~concurrent.futures.Future.result` method to wait for and inspect the result.

Update an existing Database

After creating the database object, you can apply additional DDL statements via its :meth:`~google.cloud.spanner.database.Database.update_ddl` method:

operation = database.update_ddl(ddl_statements, operation_id)
  • ddl_statements is a string containing DDL to be applied to the database.
  • operation_id is a string ID for the long-running operation.

Note

Update an instance triggers a "long-running operation" and returns a :class:`google.cloud.spanner.database.Operation` object. See :ref:`check-on-current-database-operation` for polling to find out if the operation is completed.

Drop a Database

Drop a database using its :meth:`~google.cloud.spanner.database.Database.drop` method:

database.drop()

Check on Current Database Operation

The :meth:`~google.cloud.spanner.database.Database.create` and :meth:`~google.cloud.spanner.database.Database.update` methods of instance object trigger long-running operations on the server, and return instances conforming to the :class:`~.concurrent.futures.Future` class.

>>> operation = instance.create()
>>> operation.result()

Non-Admin Database Usage

Use a Snapshot to Read / Query the Database

A snapshot represents a read-only point-in-time view of the database.

Calling :meth:`~google.cloud.spanner.database.Database.snapshot` with no arguments creates a snapshot with strong concurrency:

with database.snapshot() as snapshot:
    do_something_with(snapshot)

See :class:`~google.cloud.spanner.snapshot.Snapshot` for the other options which can be passed.

Note

:meth:`~google.cloud.spanner.database.Database.snapshot` returns an object intended to be used as a Python context manager (i.e., as the target of a with statement). Use the instance, and any result sets returned by its read or execute_sql methods, only inside the block created by the with statement.

See :doc:`snapshot-usage` for more complete examples of snapshot usage.

Use a Batch to Modify Rows in the Database

A batch represents a bundled set of insert/upsert/update/delete operations on the rows of tables in the database.

with database.batch() as batch:
     batch.insert_or_update(table, columns, rows)
     batch.delete(table, keyset_to_delete)

Note

:meth:`~google.cloud.spanner.database.Database.batch` returns an object intended to be used as a Python context manager (i.e., as the target of a with statement). It applies any changes made inside the block of its with statement when exiting the block, unless an exception is raised within the block. Use the batch only inside the block created by the with statement.

See :doc:`batch-usage` for more complete examples of batch usage.

Use a Transaction to Query / Modify Rows in the Database

A transaction represents the union of a "strong" snapshot and a batch: it allows read and execute_sql operations, and accumulates insert/upsert/update/delete operations.

Because other applications may be performing concurrent updates which would invalidate the reads / queries, the work done by a transaction needs to be bundled as a retryable "unit of work" function, which takes the transaction as a required argument:

def unit_of_work(transaction):
    result = transaction.execute_sql(QUERY)

    for emp_id, hours, pay in _compute_pay(result):
        transaction.insert_or_update(
            table='monthly_hours',
            columns=['employee_id', 'month', 'hours', 'pay'],
            values=[emp_id, month_start, hours, pay])

database.run_in_transaction(unit_of_work)

Note

:meth:`~google.cloud.spanner.database.Database.run_in_transaction` commits the transaction automatically if the "unit of work" function returns without raising an exception.

Note

:meth:`~google.cloud.spanner.database.Database.run_in_transaction` retries the "unit of work" function if the read / query operatoins or the commit are aborted due to concurrent updates

See :doc:`transaction-usage` for more complete examples of transaction usage.

Configuring a session pool for a database

Under the covers, the snapshot, batch, and run_in_transaction methods use a pool of :class:`~google.cloud.spanner.session.Session` objects to manage their communication with the back-end. You can configure one of the pools manually to control the number of sessions, timeouts, etc., and then passing it to the :class:`~google.cloud.spanner.database.Database` constructor:

from google.cloud import spanner

# Instantiate the Spanner client, and get the appropriate instance.
client = spanner.Client()
instance = client.instance(INSTANCE_NAME)

# Create a database with a pool of a fixed size.
pool = spanner.FixedSizePool(size=10, default_timeout=5)
database = instance.database(DATABASE_NAME, pool=pool)

Note that creating a database with a pool may presume that its database already exists, as it may need to pre-create sessions (rather than creating them on demand, as the default implementation does).

You can supply your own pool implementation, which must satisfy the contract laid out in :class:`~google.cloud.spanner.pool.AbstractSessionPool`:

from google.cloud.pool import AbstractSessionPool

class MyCustomPool(AbstractSessionPool):

     def __init__(self, database, custom_param):
         super(MyCustomPool, self).__init__(database)
         self.custom_param = custom_param

     def get(self, read_only=False):
         ...

     def put(self, session, discard_if_full=True):
         ...

database = instance.database(DATABASE_NAME, pool=pool)
pool = MyCustomPool(database, custom_param=42)

See :doc:`advanced-session-pool-topics` for more advanced coverage of session pools.