Read-write Transactions¶
A Transaction represents a
transaction: when the transaction commits, it will send any accumulated
mutations to the server.
To understand more about how transactions work, visit [Transaction](https://cloud.google.com/spanner/docs/reference/rest/v1/Transaction). To learn more about how to use them in the Python client, continue reading.
Read Table Data¶
Read data for selected rows from a table in the database. Calls the Read
API, which returns all rows specified in key_set, or else fails if the
result set is too large,
result = transaction.read(
table='table-name', columns=['first_name', 'last_name', 'age'],
key_set=['phred@example.com', 'bharney@example.com'])
for row in list(result):
print(row)
Note
If streaming a chunk fails due to a “resumable” error,
Snapshot.read() retries the StreamingRead API request,
passing the resume_token from the last partial result streamed.
Execute a SQL Select Statement¶
Read data from a query against tables in the database. Calls
the ExecuteSql API, which returns all rows matching the query, or else
fails if the result set is too large,
QUERY = (
'SELECT e.first_name, e.last_name, p.telephone '
'FROM employees as e, phones as p '
'WHERE p.employee_id == e.employee_id')
result = transaction.execute_sql(QUERY)
for row in list(result):
print(row)
Execute a SQL DML Statement¶
Modify data from a query against tables in the database. Calls
the ExecuteSql API, and returns the number of rows affected,
QUERY = 'DELETE from Table WHERE 1=1'
row_count = transaction.execute_sql(QUERY)
Insert records using a Transaction¶
Transaction.insert() adds one or more new records to a table. Fails if
any of the records already exists.
transaction.insert(
'citizens', columns=['email', 'first_name', 'last_name', 'age'],
values=[
['phred@exammple.com', 'Phred', 'Phlyntstone', 32],
['bharney@example.com', 'Bharney', 'Rhubble', 31],
])
Note
Ensure that data being sent for STRING columns uses a text string
(str in Python 3; unicode in Python 2).
Additionally, if you are writing data intended for a BYTES column, you
must base64 encode it.
Update records using a Transaction¶
Transaction.update() updates one or more existing records in a table. Fails
if any of the records does not already exist.
transaction.update(
'citizens', columns=['email', 'age'],
values=[
['phred@exammple.com', 33],
['bharney@example.com', 32],
])
Note
Ensure that data being sent for STRING columns uses a text string
(str in Python 3; unicode in Python 2).
Additionally, if you are writing data intended for a BYTES column, you
must base64 encode it.
Insert or update records using a Transaction¶
Transaction.insert_or_update() inserts or updates one or more records
in a table. Existing rows have values for the supplied columns overwritten;
other column values are preserved.
transaction.insert_or_update(
'citizens', columns=['email', 'first_name', 'last_name', 'age'],
values=[
['phred@exammple.com', 'Phred', 'Phlyntstone', 31],
['wylma@example.com', 'Wylma', 'Phlyntstone', 29],
])
Note
Ensure that data being sent for STRING columns uses a text string
(str in Python 3; unicode in Python 2).
Additionally, if you are writing data intended for a BYTES column, you
must base64 encode it.
Replace records using a Transaction¶
Transaction.replace() inserts or updates one or more records in a
table. Existing rows have values for the supplied columns overwritten; other
column values are set to null.
transaction.replace(
'citizens', columns=['email', 'first_name', 'last_name', 'age'],
values=[
['bharney@example.com', 'Bharney', 'Rhubble', 30],
['bhettye@example.com', 'Bhettye', 'Rhubble', 30],
])
Note
Ensure that data being sent for STRING columns uses a text string
(str in Python 3; unicode in Python 2).
Additionally, if you are writing data intended for a BYTES column, you
must base64 encode it.
Delete records using a Transaction¶
Transaction.delete() removes one or more records from a table.
Non-existent rows do not cause errors.
transaction.delete(
'citizens', keyset=['bharney@example.com', 'nonesuch@example.com'])
Using run_in_transaction()¶
Rather than calling commit() or rollback()
manually, you should use run_in_transaction() to run the
function that you need. The transaction’s commit() method
will be called automatically if the with block exits without raising an
exception. The function will automatically be retried for
Aborted errors, but will raise on
GoogleAPICallError and
rollback() will be called on all others.
def _unit_of_work(transaction):
transaction.insert(
'citizens', columns=['email', 'first_name', 'last_name', 'age'],
values=[
['phred@exammple.com', 'Phred', 'Phlyntstone', 32],
['bharney@example.com', 'Bharney', 'Rhubble', 31],
])
transaction.update(
'citizens', columns=['email', 'age'],
values=[
['phred@exammple.com', 33],
['bharney@example.com', 32],
])
...
transaction.delete('citizens',
keyset['bharney@example.com', 'nonesuch@example.com'])
db.run_in_transaction(_unit_of_work)
Use a Transaction as a Context Manager¶
Alternatively, you can use the Transaction instance as a context
manager. The transaction’s commit() method will be called
automatically if the with block exits without raising an exception.
If an exception is raised inside the with block, the transaction’s
rollback() method will automatically be called.
with session.transaction() as transaction:
transaction.insert(
'citizens', columns=['email', 'first_name', 'last_name', 'age'],
values=[
['phred@exammple.com', 'Phred', 'Phlyntstone', 32],
['bharney@example.com', 'Bharney', 'Rhubble', 31],
])
transaction.update(
'citizens', columns=['email', 'age'],
values=[
['phred@exammple.com', 33],
['bharney@example.com', 32],
])
...
transaction.delete('citizens',
keyset['bharney@example.com', 'nonesuch@example.com'])
Begin a Transaction¶
Note
Normally, applications will not construct transactions manually. Rather,
consider using run_in_transaction() or the context manager
as described above.
To begin using a transaction manually:
transaction = session.transaction()
Commit changes for a Transaction¶
Note
Normally, applications will not commit transactions manually. Rather,
consider using run_in_transaction() or the context manager
as described above.
After modifications to be made to table data via the
Transaction.insert(), Transaction.update(),
Transaction.insert_or_update(), Transaction.replace(), and
Transaction.delete() methods above, send them to
the back-end by calling Transaction.commit(), which makes the Commit
API call.
transaction.commit()
Roll back changes for a Transaction¶
Note
Normally, applications will not roll back transactions manually. Rather,
consider using run_in_transaction() or the context manager
as described above.
After describing the modifications to be made to table data via the
Transaction.insert(), Transaction.update(),
Transaction.insert_or_update(), Transaction.replace(), and
Transaction.delete() methods above, cancel the transaction on the
the back-end by calling Transaction.rollback(), which makes the
Rollback API call.
transaction.rollback()
