# Copyright 2024 Google LLC All rights reserved.

#

# Licensed under the Apache License, Version 2.0 (the "License");

# you may not use this file except in compliance with the License.

# You may obtain a copy of the License at

#

# http://www.apache.org/licenses/LICENSE-2.0

#

# Unless required by applicable law or agreed to in writing, software

# distributed under the License is distributed on an "AS IS" BASIS,

# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

# See the License for the specific language governing permissions and

# limitations under the License.

import datetime

import uuid

from sqlalchemy import create_engine

from sqlalchemy.orm import Session

from sample_helper import run_sample

from model import Singer, Concert, Venue

# Shows how to execute a read/write transaction on Spanner using SQLAlchemy.

def transaction_sample():

engine = create_engine(

"spanner:///projects/sample-project/"

"instances/sample-instance/"

"databases/sample-database",

echo=True,

)

with Session(engine) as session:

# SQLAlchemy by default automatically starts a transaction the first

# time the database is accessed.

singer_id = str(uuid.uuid4())

singer = Singer(id=singer_id, first_name="John", last_name="Doe")

session.add(singer)

# Flush the session. This pushes all changes in this session to the

# database without committing the current transaction.

session.flush([singer])

# Verify that we can read this singer from the database during the

# same transaction. The Singer model has a `full_name` property that is

# generated by the database. This will be read from the database when

# we call refresh.

session.refresh(singer)

print(

f"The database generated a full name for the singer: " f"{singer.full_name}"

)

# We cannot read the singer in a different session, as the current

# transaction has not yet committed.

# We use AUTOCOMMIT for the additional session, as we do not need a

# read/write transaction for just trying to read one row.

with Session(

engine.execution_options(isolation_level="AUTOCOMMIT")

) as session2:

# singer2 will be None, as the row will not be found.

singer2 = session2.get(Singer, singer_id)

print(

f"Fetching singer in a different transaction before the "

f"transaction was committed: {singer2}"

)

session.commit()

# Now that the original transaction has committed, we can read the

# singer in a different session.

with Session(

engine.execution_options(isolation_level="AUTOCOMMIT")

) as session2:

# singer2 will now return the actual row.

singer2 = session2.get(Singer, singer_id)

print(

f"Fetching singer in a different transaction after the "

f"transaction was committed: {singer2.full_name}"

)

if __name__ == "__main__":

run_sample(transaction_sample)