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. 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()