Sqlalchemy-utils¶
Description:
- Custom data types and helpers for SQLAlchemy, used here against an Oracle database
Resources:
Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
ORACLE_HOST: hostname of your Oracle DatabaseORACLE_PORT: optional, defaults to1521ORACLE_USER: database user nameORACLE_PASSWORD: password for that userORACLE_SERVICE_NAME: service name of the database (for exampleFREEPDB1)
Example 1: sqlalchemy and sqlalchemy-utils¶
Connect to a remote oracle database using sqlalchemy, create tables that use sqlalchemy-utils column types, insert a row and read it back
In [ ]:
Copied!
import enum
import os
from typing import List, Optional
import arrow
from sqlalchemy import Column, ForeignKey, Identity, Integer, String, URL, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship
from sqlalchemy_utils import ArrowType, ChoiceType
class Base(DeclarativeBase):
pass
class UserType(enum.Enum):
admin = 1
regular = 2
class User(Base):
__tablename__ = "user_account"
# Identity() lets Oracle generate the primary key values
id: Mapped[int] = mapped_column(Identity(), primary_key=True)
name: Mapped[str] = mapped_column(String(30))
# Oracle needs a length for every string column
fullname: Mapped[Optional[str]] = mapped_column(String(100))
addresses: Mapped[List["Address"]] = relationship(
back_populates="user", cascade="all, delete-orphan"
)
# sqlalchemy-utils column types: ArrowType stores an Arrow datetime,
# ChoiceType stores an enum member as its integer value
created_at = Column(ArrowType)
type = Column(ChoiceType(UserType, impl=Integer()))
def __repr__(self) -> str:
return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"
class Address(Base):
__tablename__ = "address"
id: Mapped[int] = mapped_column(Identity(), primary_key=True)
email_address: Mapped[str] = mapped_column(String(320))
user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
user: Mapped["User"] = relationship(back_populates="addresses")
def __repr__(self) -> str:
return f"Address(id={self.id!r}, email_address={self.email_address!r})"
url_object = URL.create(
"oracle+oracledb",
username=os.environ["ORACLE_USER"],
password=os.environ["ORACLE_PASSWORD"],
host=os.environ["ORACLE_HOST"],
database=os.environ["ORACLE_SERVICE_NAME"],
port=int(os.environ.get("ORACLE_PORT", "1521")),
)
engine = create_engine(url_object, echo=True)
# Create the tables (tables that already exist are left alone)
Base.metadata.create_all(engine)
with Session(engine) as session:
print("Connected to db")
user = User(
name="spongebob",
fullname="Spongebob Squarepants",
created_at=arrow.utcnow().shift(hours=-1),
type=UserType.admin,
addresses=[Address(email_address="spongebob@example.com")],
)
session.add(user)
session.commit()
saved = session.scalars(select(User).where(User.id == user.id)).one()
print(saved, saved.addresses)
print(saved.type)
print(saved.created_at.humanize())
import enum
import os
from typing import List, Optional
import arrow
from sqlalchemy import Column, ForeignKey, Identity, Integer, String, URL, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship
from sqlalchemy_utils import ArrowType, ChoiceType
class Base(DeclarativeBase):
pass
class UserType(enum.Enum):
admin = 1
regular = 2
class User(Base):
__tablename__ = "user_account"
# Identity() lets Oracle generate the primary key values
id: Mapped[int] = mapped_column(Identity(), primary_key=True)
name: Mapped[str] = mapped_column(String(30))
# Oracle needs a length for every string column
fullname: Mapped[Optional[str]] = mapped_column(String(100))
addresses: Mapped[List["Address"]] = relationship(
back_populates="user", cascade="all, delete-orphan"
)
# sqlalchemy-utils column types: ArrowType stores an Arrow datetime,
# ChoiceType stores an enum member as its integer value
created_at = Column(ArrowType)
type = Column(ChoiceType(UserType, impl=Integer()))
def __repr__(self) -> str:
return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"
class Address(Base):
__tablename__ = "address"
id: Mapped[int] = mapped_column(Identity(), primary_key=True)
email_address: Mapped[str] = mapped_column(String(320))
user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
user: Mapped["User"] = relationship(back_populates="addresses")
def __repr__(self) -> str:
return f"Address(id={self.id!r}, email_address={self.email_address!r})"
url_object = URL.create(
"oracle+oracledb",
username=os.environ["ORACLE_USER"],
password=os.environ["ORACLE_PASSWORD"],
host=os.environ["ORACLE_HOST"],
database=os.environ["ORACLE_SERVICE_NAME"],
port=int(os.environ.get("ORACLE_PORT", "1521")),
)
engine = create_engine(url_object, echo=True)
# Create the tables (tables that already exist are left alone)
Base.metadata.create_all(engine)
with Session(engine) as session:
print("Connected to db")
user = User(
name="spongebob",
fullname="Spongebob Squarepants",
created_at=arrow.utcnow().shift(hours=-1),
type=UserType.admin,
addresses=[Address(email_address="spongebob@example.com")],
)
session.add(user)
session.commit()
saved = session.scalars(select(User).where(User.id == user.id)).one()
print(saved, saved.addresses)
print(saved.type)
print(saved.created_at.humanize())