cockroachdb_sqlalchemy_orm_transactions_quickstart.py

1import uuid
2from sqlalchemy import create_engine, Column, BigInteger, String
3from sqlalchemy.orm import sessionmaker, declarative_base
4from sqlalchemy_cockroachdb import run_transaction
5
6Base = declarative_base()
7
8# The Account class corresponds to the "accounts" table
9class Account(Base):
10    __tablename__ = 'accounts'
11    id = Column(BigInteger, primary_key=True)
12    balance = Column(BigInteger)
13
14# Create an engine to communicate with the database
15# Replace 'postgresql://<user>:<password>@<host>:<port>/defaultdb?sslmode=verify-full' 
16# with your actual connection string.
17engine = create_engine('postgresql://root@localhost:26257/defaultdb?sslmode=disable')
18
19# Create the table
20Base.metadata.create_all(engine)
21
22def create_accounts(session):
23    """Create accounts with initial balances."""
24    print("Creating new accounts...")
25    session.add_all([
26        Account(id=1, balance=1000),
27        Account(id=2, balance=250)
28    ])
29
30def transfer_funds(session, frm, to, amount):
31    """Transfer funds from one account to another."""
32    source = session.query(Account).filter(Account.id == frm).one()
33    dest = session.query(Account).filter(Account.id == to).one()
34
35    if source.balance < amount:
36        raise ValueError(f"Insufficient funds in account {frm}")
37
38    source.balance -= amount
39    dest.balance += amount
40    print(f"Transferred {amount} from {frm} to {to}.")
41
42# Run the operations within a transaction
43run_transaction(sessionmaker(bind=engine), create_accounts)
44run_transaction(sessionmaker(bind=engine), lambda s: transfer_funds(s, 1, 2, 100))
45
46# Verify the final balances
47def print_balances(session):
48    accounts = session.query(Account).all()
49    print("Final balances:")
50    for account in accounts:
51        print(f"Account {account.id}: {account.balance}")
52
53run_transaction(sessionmaker(bind=engine), print_balances)