Exhibit to show that DDL and Transactions don't mix thus transactions have to be implicitly committed first

ddl_txn_bad_mix.py

#!/usr/bin/env python3

from google.cloud import spanner_v1 as spanner

def get_instance(client):
    ins = client.instance('django-tests')
    
    fresh_instance = False
    if not ins.exists():
        ins.configuration_name = 'projects/appdev-soda-spanner-staging/instanceConfigs/regional-us-west2'
        _ = ins.create()
        fresh_instance = True

    return ins, fresh_instance

def main():
    client = spanner.Client()
    ins, freshly_created_ins = get_instance(client)
    db, freshly_created_db = get_db(ins)

    sess = db.session()
    if not sess.exists():
        sess.create()

    txn = sess.transaction()
    txn.begin()

    table_created = False
    table_name = 'foo_txn_test'

    try:
        res = txn.execute_sql("SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int")
        for i, value in enumerate(res):
            print('#%d: %s' % (i, value))

        # Create the table.
        _ = db.update_ddl(('''
            CREATE TABLE %s (
                id INT64 NOT NULL,
                name STRING(100)
            ) PRIMARY KEY (id)
            ''' % table_name,)).result()
        table_created = True

    except Exception as e:
        print('Exception ', e)
        txn.rollback()
    else:
        try:
            txn.commit()
        except Exception as ee:
            print('Commit exception ', ee)
            print('Trying again to invoke commit')
            txn.commit()
    finally:
        # Perform some clean-ups if necessary.
        sess.delete()

        if freshly_created_db:
            db.drop()

        if freshly_created_ins and False:
            ins.delete()

        if table_created:
            _ = db.update_ddl(('''DROP TABLE %s''' % table_name,)).result()


def get_db(ins):
    db = ins.database('testdb')
    freshly_created_db = False
    if not db.exists():
        db.create()
        freshly_created_db = True

    return db, freshly_created_db

if __name__ == '__main__':
    main()