sqlalchemy_cockroachdb_crud_with_orm_session.py

1import os
2from sqlalchemy import create_engine, Column, Integer, String
3from sqlalchemy.orm import declarative_base, sessionmaker
4
5# 1. Define the database connection URL.
6# For CockroachDB, use the 'cockroachdb://' dialect prefix.
7# Example: cockroachdb://user:password@host:port/database?sslmode=verify-full
8conn_string = os.environ.get("DATABASE_URL", "cockroachdb://root@localhost:26257/defaultdb")
9
10# 2. Create the engine.
11engine = create_engine(conn_string)
12
13# 3. Define the data model.
14Base = declarative_base()
15
16class Account(Base):
17    __tablename__ = 'accounts'
18    id = Column(Integer, primary_key=True)
19    balance = Column(Integer)
20
21# 4. Create the table in the database.
22Base.metadata.create_all(engine)
23
24# 5. Create a session and perform operations.
25Session = sessionmaker(bind=engine)
26
27with Session() as session:
28    # Insert: Create a new account
29    new_account = Account(id=1, balance=1000)
30    session.add(new_account)
31    
32    # Commit the transaction
33    session.commit()
34    
35    # Query: Read the account back
36    account = session.query(Account).filter_by(id=1).first()
37    print(f"Account ID: {account.id}, Balance: {account.balance}")