Essential SQLAlchemy (v1/v2)

import sqlalchemy
print(sqlalchemy.__version__)

from sqlalchemy import MetaData

metadata = MetaData()

from sqlalchemy import Table, Column, Integer, Numeric, String, CheckConstraint

cookies = Table(
    "cookies",
    metadata,
    Column("cookie_id", Integer(), primary_key=True),
    Column("cookie_name", String(50), index=True),
    Column("cookie_recipie_url", String(255)),
    Column("cookie_sku", String(55)),
    Column("quantity", Integer()),
    Column("unit_cost", Numeric(12, 2)),
    CheckConstraint("unit_cost >= 0.00", name="unit_cost_positive"),
)

from datetime import datetime
from sqlalchemy import DateTime, PrimaryKeyConstraint, UniqueConstraint

users = Table(
    "users",
    metadata,
    Column("user_id", Integer()),
    Column("username", String(15), nullable=False),
    Column("email_address", String(255), nullable=False),
    Column("phone", String(20), nullable=False),
    Column("password", String(25), nullable=False),
    Column("created_on", DateTime(), default=datetime.now),
    Column("updated_on", DateTime(), default=datetime.now, onupdate=datetime.now),
    PrimaryKeyConstraint("user_id", name="user_pk"),
    UniqueConstraint("username", name="uix_username"),
)

@startuml
skinparam linetype ortho
skinparam nodesep 30
skinparam ranksep 40

skinparam entity {
        BackgroundColor White
        BorderColor Black
        BorderThickness 1.2
        FontStyle bold
        FontSize 12
}

entity orders {
        **order_id** : PK
        --
        user_id : FK
}
entity line_items {
        **line_item_id**: PK
        --
        **order_id** : FK
        **cookie_id** : FK
        quantity
        extended_cost
}
entity cookies {
        **cookie_id** : PK
        --
        cookie_name
        cookie_reciepe_url
        cookie_sku
        quantity
        unit_cost
}
entity users {
        **user_id** : PK
        --
        customer_number
        username
        email-address
        phone
        password
        created_on
        updated_on
}

orders ||--|{ line_items
line_items }o--|| cookies
orders }o--|| users

orders -[hidden]right- line_items
line_items -[hidden]right- cookies
orders -[hidden]down- users
@enduml

from sqlalchemy import ForeignKey, Boolean

orders = Table(
    "orders",
    metadata,
    Column("order_id", Integer(), primary_key=True),
    Column("user_id", ForeignKey("users.user_id")),
    Column("shipped", Boolean(), default=False),
)


line_items = Table(
    "line_items",
    metadata,
    Column("line_items_id", Integer(), primary_key=True),
    Column("order_id", ForeignKey("orders.order_id")),
    Column("cookie_id", ForeignKey("cookies.cookie_id")),
    Column("quantity", Integer()),
    Column("extended_cost", Numeric(12, 2)),
)

from sqlalchemy import MetaData

metadata = MetaData()
from sqlalchemy import Table, Column, Integer, Numeric, String, CheckConstraint

cookies = Table(
    "cookies",
    metadata,
    Column("cookie_id", Integer(), primary_key=True),
    Column("cookie_name", String(50), index=True),
    Column("cookie_recipie_url", String(255)),
    Column("cookie_sku", String(55)),
    Column("quantity", Integer()),
    Column("unit_cost", Numeric(12, 2)),
    CheckConstraint("unit_cost >= 0.00", name="unit_cost_positive"),
)
from datetime import datetime
from sqlalchemy import DateTime, PrimaryKeyConstraint, UniqueConstraint

users = Table(
    "users",
    metadata,
    Column("user_id", Integer()),
    Column("username", String(15), nullable=False),
    Column("email_address", String(255), nullable=False),
    Column("phone", String(20), nullable=False),
    Column("password", String(25), nullable=False),
    Column("created_on", DateTime(), default=datetime.now),
    Column("updated_on", DateTime(), default=datetime.now, onupdate=datetime.now),
    PrimaryKeyConstraint("user_id", name="user_pk"),
    UniqueConstraint("username", name="uix_username"),
)
from sqlalchemy import ForeignKey, Boolean

orders = Table(
    "orders",
    metadata,
    Column("order_id", Integer(), primary_key=True),
    Column("user_id", ForeignKey("users.user_id")),
    Column("shipped", Boolean(), default=False),
)


line_items = Table(
    "line_items",
    metadata,
    Column("line_items_id", Integer(), primary_key=True),
    Column("order_id", ForeignKey("orders.order_id")),
    Column("cookie_id", ForeignKey("cookies.cookie_id")),
    Column("quantity", Integer()),
    Column("extended_cost", Numeric(12, 2)),
)
from sqlalchemy import create_engine

engine = create_engine("sqlite:///:memory:", echo=True)
metadata.create_all(engine)

#!/usr/bin/env python3
from datetime import datetime
from decimal import Decimal

from sqlalchemy import (
    Boolean,
    CheckConstraint,
    DateTime,
    ForeignKey,
    Integer,
    Numeric,
    String,
    UniqueConstraint,
    create_engine,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    """Базовый класс для всех ORM-моделей."""


class Cookie(Base):
    __tablename__ = "cookies"

    cookie_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    cookie_name: Mapped[str | None] = mapped_column(String(50), index=True)
    cookie_recipie_url: Mapped[str | None] = mapped_column(String(255))
    cookie_sku: Mapped[str | None] = mapped_column(String(55))
    quantity: Mapped[int | None] = mapped_column(Integer)
    unit_cost: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))

    __table_args__ = (CheckConstraint("unit_cost >= 0.00", name="unit_cost_positive"),)

    # Связь: одна cookie -> много line_items
    line_items: Mapped[list["LineItem"]] = relationship(back_populates="cookie")

    def __repr__(self) -> str:
        return f"Cookie(cookie_id={self.cookie_id!r}, cookie_name={self.cookie_name!r})"


class User(Base):
    __tablename__ = "users"

    user_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(15), nullable=False)
    email_address: Mapped[str] = mapped_column(String(255), nullable=False)
    phone: Mapped[str] = mapped_column(String(20), nullable=False)
    password: Mapped[str] = mapped_column(String(25), nullable=False)
    created_on: Mapped[datetime | None] = mapped_column(DateTime, default=datetime.now)
    updated_on: Mapped[datetime | None] = mapped_column(
        DateTime, default=datetime.now, onupdate=datetime.now
    )

    __table_args__ = (UniqueConstraint("username", name="uix_username"),)

    # Связь: один user -> много orders
    orders: Mapped[list["Order"]] = relationship(back_populates="user")

    def __repr__(self) -> str:
        return f"User(user_id={self.user_id!r}, username={self.username!r})"


class Order(Base):
    __tablename__ = "orders"

    order_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.user_id"))
    shipped: Mapped[bool | None] = mapped_column(Boolean, default=False)

    # Связи
    user: Mapped["User | None"] = relationship(back_populates="orders")
    line_items: Mapped[list["LineItem"]] = relationship(back_populates="order")

    def __repr__(self) -> str:
        return f"Order(order_id={self.order_id!r}, user_id={self.user_id!r})"


class LineItem(Base):
    __tablename__ = "line_items"

    line_items_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    order_id: Mapped[int | None] = mapped_column(ForeignKey("orders.order_id"))
    cookie_id: Mapped[int | None] = mapped_column(ForeignKey("cookies.cookie_id"))
    quantity: Mapped[int | None] = mapped_column(Integer)
    extended_cost: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))

    # Связи
    order: Mapped["Order | None"] = relationship(back_populates="line_items")
    cookie: Mapped["Cookie | None"] = relationship(back_populates="line_items")

    def __repr__(self) -> str:
        return (
            f"LineItem(line_items_id={self.line_items_id!r}, "
            f"order_id={self.order_id!r}, cookie_id={self.cookie_id!r})"
        )


if __name__ == "__main__":
    engine = create_engine("sqlite:///:memory:", echo=True)
    Base.metadata.create_all(engine)

from datetime import datetime

from sqlalchemy import (
    Boolean,
    CheckConstraint,
    Column,
    DateTime,
    ForeignKey,
    Integer,
    MetaData,
    Numeric,
    PrimaryKeyConstraint,
    String,
    Table,
    UniqueConstraint,
    create_engine,
)
from sqlparse import format as sqlformat


def sqlf(
    sql: str | object,
    reindent=True,
    keyword_case="upper",
    indent_width=2,
    strip_comments=False,
    identifier_case="lower",
):
    if not isinstance(sql, str):
        sql = str(sql)
    return sqlformat(
        sql,
        encoding="utf8",
        reindent=reindent,
        keyword_case=keyword_case,
        indent_width=indent_width,
        strip_comments=strip_comments,
        identifier_case=identifier_case,
    )


metadata = MetaData()

cookies = Table(
    "cookies",
    metadata,
    Column("cookie_id", Integer(), primary_key=True),
    Column("cookie_name", String(50), index=True),
    Column("cookie_recipe_url", String(255), unique=True),
    Column("cookie_sku", String(55), unique=True),
    Column("quantity", Integer()),
    Column("unit_cost", Numeric(12, 2)),
    CheckConstraint("unit_cost >= 0.00", name="unit_cost_positive"),
)

users = Table(
    "users",
    metadata,
    Column("user_id", Integer()),
    Column("username", String(15), nullable=False),
    Column("email_address", String(255), nullable=False),
    Column("phone", String(20), nullable=False),
    Column("password", String(25), nullable=False),
    Column("created_on", DateTime(), default=datetime.now),
    Column("updated_on", DateTime(), default=datetime.now, onupdate=datetime.now),
    PrimaryKeyConstraint("user_id", name="user_pk"),
    UniqueConstraint("username", name="uix_username"),
)

orders = Table(
    "orders",
    metadata,
    Column("order_id", Integer(), primary_key=True),
    Column("user_id", ForeignKey("users.user_id")),
    Column("shipped", Boolean(), default=False),
)


line_items = Table(
    "line_items",
    metadata,
    Column("line_items_id", Integer(), primary_key=True),
    Column("order_id", ForeignKey("orders.order_id")),
    Column("cookie_id", ForeignKey("cookies.cookie_id")),
    Column("quantity", Integer()),
    Column("extended_cost", Numeric(12, 2)),
)

engine = create_engine("sqlite:///:memory:", echo=True)
connection = engine.connect()
metadata.create_all(engine)

from datetime import datetime

from sqlalchemy import (
    Boolean,
    CheckConstraint,
    Column,
    DateTime,
    ForeignKey,
    Integer,
    MetaData,
    Numeric,
    PrimaryKeyConstraint,
    String,
    Table,
    UniqueConstraint,
    create_engine,
)
from sqlparse import format as sqlformat


def sqlf(
    sql: str | object,
    reindent=True,
    keyword_case="upper",
    indent_width=2,
    strip_comments=False,
    identifier_case="lower",
):
    if not isinstance(sql, str):
        sql = str(sql)
    return sqlformat(
        sql,
        encoding="utf8",
        reindent=reindent,
        keyword_case=keyword_case,
        indent_width=indent_width,
        strip_comments=strip_comments,
        identifier_case=identifier_case,
    )


metadata = MetaData()

cookies = Table(
    "cookies",
    metadata,
    Column("cookie_id", Integer(), primary_key=True),
    Column("cookie_name", String(50), index=True),
    Column("cookie_recipe_url", String(255), unique=True),
    Column("cookie_sku", String(55), unique=True),
    Column("quantity", Integer()),
    Column("unit_cost", Numeric(12, 2)),
    CheckConstraint("unit_cost >= 0.00", name="unit_cost_positive"),
)

users = Table(
    "users",
    metadata,
    Column("user_id", Integer()),
    Column("username", String(15), nullable=False),
    Column("email_address", String(255), nullable=False),
    Column("phone", String(20), nullable=False),
    Column("password", String(25), nullable=False),
    Column("created_on", DateTime(), default=datetime.now),
    Column("updated_on", DateTime(), default=datetime.now, onupdate=datetime.now),
    PrimaryKeyConstraint("user_id", name="user_pk"),
    UniqueConstraint("username", name="uix_username"),
)

orders = Table(
    "orders",
    metadata,
    Column("order_id", Integer(), primary_key=True),
    Column("user_id", ForeignKey("users.user_id")),
    Column("shipped", Boolean(), default=False),
)

line_items = Table(
    "line_items",
    metadata,
    Column("line_items_id", Integer(), primary_key=True),
    Column("order_id", ForeignKey("orders.order_id")),
    Column("cookie_id", ForeignKey("cookies.cookie_id")),
    Column("quantity", Integer()),
    Column("extended_cost", Numeric(12, 2)),
)

from sqlalchemy.pool import StaticPool

engine = create_engine("sqlite:///:memory:", echo=True)
connection = engine.connect()
metadata.create_all(connection)

import sys

sys.stderr = sys.stdout

from datetime import datetime

from sqlalchemy import (
    Boolean,
    CheckConstraint,
    Column,
    DateTime,
    ForeignKey,
    Integer,
    MetaData,
    Numeric,
    String,
    Table,
    create_engine,
    PrimaryKeyConstraint,
    UniqueConstraint,
)
from sqlparse import format as sqlformat


def sqlf(
    sql: str | object,
    reindent=True,
    keyword_case="upper",
    indent_width=2,
    strip_comments=False,
    identifier_case="lower",
):
    if not isinstance(sql, str):
        sql = str(sql)
    return sqlformat(
        sql,
        encoding="utf8",
        reindent=reindent,
        keyword_case=keyword_case,
        indent_width=indent_width,
        strip_comments=strip_comments,
        identifier_case=identifier_case,
    )


metadata = MetaData()

cookies = Table(
    "cookies",
    metadata,
    Column("cookie_id", Integer(), primary_key=True),
    Column("cookie_name", String(50), index=True),
    Column("cookie_recipe_url", String(255), unique=True),
    Column("cookie_sku", String(55), unique=True),
    Column("quantity", Integer()),
    Column("unit_cost", Numeric(12, 2)),
    CheckConstraint("unit_cost >= 0.00", name="unit_cost_positive"),
    CheckConstraint("quantity >= 0", name="quantity_positive"),
)

users = Table(
    "users",
    metadata,
    Column("user_id", Integer()),
    Column("username", String(15), nullable=False),
    Column("email_address", String(255), nullable=False),
    Column("phone", String(20), nullable=False),
    Column("password", String(25), nullable=False),
    Column("created_on", DateTime(), default=datetime.now),
    Column("updated_on", DateTime(), default=datetime.now, onupdate=datetime.now),
    PrimaryKeyConstraint("user_id", name="user_pk"),
    UniqueConstraint("username", name="uix_username"),
)

orders = Table(
    "orders",
    metadata,
    Column("order_id", Integer(), primary_key=True),
    Column("user_id", ForeignKey("users.user_id")),
    Column("shipped", Boolean(), default=False),
)


line_items = Table(
    "line_items",
    metadata,
    Column("line_items_id", Integer(), primary_key=True),
    Column("order_id", ForeignKey("orders.order_id")),
    Column("cookie_id", ForeignKey("cookies.cookie_id")),
    Column("quantity", Integer()),
    Column("extended_cost", Numeric(12, 2)),
)

engine = create_engine("sqlite:///:memory:", echo=True)
connection = engine.connect()
metadata.create_all(engine)

#!/usr/bin/env python3
import sys

sys.stderr = sys.stdout

import logging
from datetime import datetime

from sqlalchemy import (
    Boolean,
    CheckConstraint,
    Column,
    DateTime,
    ForeignKey,
    Integer,
    MetaData,
    Numeric,
    PrimaryKeyConstraint,
    String,
    Table,
    UniqueConstraint,
    create_engine,
    insert,
    select,
    update,
)
from sqlalchemy.exc import IntegrityError
from sqlparse import format as sqlformat


def sqlf(
    sql: str | object,
    reindent=True,
    keyword_case="upper",
    indent_width=2,
    strip_comments=False,
    identifier_case="lower",
):
    if not isinstance(sql, str):
        sql = str(sql)
    return sqlformat(
        sql,
        encoding="utf8",
        reindent=reindent,
        keyword_case=keyword_case,
        indent_width=indent_width,
        strip_comments=strip_comments,
        identifier_case=identifier_case,
    )


metadata = MetaData()

cookies = Table(
    "cookies",
    metadata,
    Column("cookie_id", Integer(), primary_key=True),
    Column("cookie_name", String(50), index=True),
    Column("cookie_recipe_url", String(255), unique=True),
    Column("cookie_sku", String(55), unique=True),
    Column("quantity", Integer()),
    Column("unit_cost", Numeric(12, 2)),
    CheckConstraint("unit_cost >= 0.00", name="unit_cost_positive"),
    CheckConstraint("quantity >= 0", name="quantity_positive"),
)

users = Table(
    "users",
    metadata,
    Column("user_id", Integer()),
    Column("username", String(15), nullable=False),
    Column("email_address", String(255), nullable=False),
    Column("phone", String(20), nullable=False),
    Column("password", String(25), nullable=False),
    Column("created_on", DateTime(), default=datetime.now),
    Column("updated_on", DateTime(), default=datetime.now, onupdate=datetime.now),
    PrimaryKeyConstraint("user_id", name="user_pk"),
    UniqueConstraint("username", name="uix_username"),
)

orders = Table(
    "orders",
    metadata,
    Column("order_id", Integer(), primary_key=True),
    Column("user_id", ForeignKey("users.user_id")),
    Column("shipped", Boolean(), default=False),
)

line_items = Table(
    "line_items",
    metadata,
    Column("line_items_id", Integer(), primary_key=True),
    Column("order_id", ForeignKey("orders.order_id")),
    Column("cookie_id", ForeignKey("cookies.cookie_id")),
    Column("quantity", Integer()),
    Column("extended_cost", Numeric(12, 2)),
)

engine = create_engine("sqlite:///:memory:", echo=True)

with engine.begin() as connection:
    metadata.create_all(connection)

    # --- insert одного пользователя ---
    ins = insert(users).values(
        username="cookiemon",
        email_address="mon@cookie.com",
        phone="111-111-1111",
        password="password",
    )
    connection.execute(ins)

    # --- select возвращает Row, у которого есть .username ---
    s = select(users.c.username, users.c.password)
    results = connection.execute(s)
    try:
        [(row.username, row.password) for row in results]
    except AttributeError:
        logging.exception(results)

    # --- проверка вставки ---
    s = select(users.c.username)
    print(connection.execute(s).fetchall())

    # --- нарушение уникальности username ---
    ins = insert(users).values(
        username="cookiemon",
        email_address="damon@cookie.com",
        phone="111-111-1111",
        password="password",
    )
    try:
        with connection.begin_nested():  # SAVEPOINT, чтобы не убить внешнюю транзакцию
            connection.execute(ins)
    except IntegrityError:
        logging.exception(str(ins))

    # --- массовая вставка cookies ---
    inventory_list = [
        {
            "cookie_name": "chocolate chip",
            "cookie_recipe_url": "http://some.aweso.me/cookie/recipe.html",
            "cookie_sku": "CC01",
            "quantity": "12",
            "unit_cost": "0.5",
        },
        {
            "cookie_name": "dark chocolate chip",
            "cookie_recipe_url": "http://some.aweso.me/cookie/recipe_dark.html",
            "cookie_sku": "CC02",
            "quantity": "1",
            "unit_cost": "0.75",
        },
    ]
    connection.execute(insert(cookies), inventory_list)

    # --- заказы ---
    connection.execute(insert(orders).values(user_id=1, order_id="1"))
    connection.execute(
        insert(line_items),
        [
            {
                "order_id": 1,
                "cookie_id": 1,
                "quantity": 9,
                "extended_cost": 4.50,
            }
        ],
    )

    connection.execute(insert(orders).values(user_id=1, order_id="2"))
    connection.execute(
        insert(line_items),
        [
            {
                "order_id": 2,
                "cookie_id": 1,
                "quantity": 4,
                "extended_cost": 1.50,
            },
            {
                "order_id": 2,
                "cookie_id": 2,
                "quantity": 1,
                "extended_cost": 4.50,
            },
        ],
    )


def ship_it(order_id: int) -> None:
    """Версия без явной транзакции — каждый execute коммитится сам."""
    s = select(line_items.c.cookie_id, line_items.c.quantity).where(
        line_items.c.order_id == order_id
    )
    cookies_to_ship = connection.execute(s).fetchall()
    for cookie in cookies_to_ship:
        u = (
            update(cookies)
            .where(cookies.c.cookie_id == cookie.cookie_id)
            .values(quantity=cookies.c.quantity - cookie.quantity)
        )
        connection.execute(u)
    u = update(orders).where(orders.c.order_id == order_id).values(shipped=True)
    connection.execute(u)
    print(f"Shipped order ID: {order_id}")


def ship_it_atomic(order_id: int) -> None:
    """Версия с явной транзакцией и rollback при ошибке."""
    s = select(line_items.c.cookie_id, line_items.c.quantity).where(
        line_items.c.order_id == order_id
    )

    with engine.begin() as conn:
        cookies_to_ship = conn.execute(s).fetchall()
        try:
            for cookie in cookies_to_ship:
                u = (
                    update(cookies)
                    .where(cookies.c.cookie_id == cookie.cookie_id)
                    .values(quantity=cookies.c.quantity - cookie.quantity)
                )
                conn.execute(u)
            u = update(orders).where(orders.c.order_id == order_id).values(shipped=True)
            conn.execute(u)
            print(f"Shipped order ID: {order_id}")
        except IntegrityError:
            # with engine.begin() сам сделает rollback
            logging.exception("Failed to ship order %s", order_id)
            raise


# --- используем транзакционную версию ---
ship_it_atomic(1)
s = select(cookies.c.cookie_name, cookies.c.quantity)
with engine.connect() as conn:
    print(conn.execute(s).fetchall())

try:
    ship_it_atomic(2)
except IntegrityError:
    logging.exception("")

import unittest
from decimal import Decimal

from app import get_orders_by_customer
from db import dal, prep_db


class TestApp(unittest.TestCase):
    cookie_orders = [("wlk001", "cookiemon", "111-111-1111")]
    cookie_details = [
        (
            "wlk001",
            "cookiemon",
            "111-111-1111",
            "dark chocolate chip",
            2,
            Decimal("1.00"),
        ),
        ("wlk001", "cookiemon", "111-111-1111", "oatmeal raisin", 12, Decimal("3.00")),
    ]

    @classmethod
    def setUpClass(cls):
        dal.db_init("sqlite:///:memory:")
        prep_db()

    def test_orders_by_customer_blank(self):
        """
        - значение cust_name пустая строка,
        - shipped = None
        """
        results = get_orders_by_customer("")
        self.assertEqual(results, [])

    def test_orders_by_customer_blank_shipped(self):
        """
        - значение cust_name пустая строка,
        - shipped = True
        """
        results = get_orders_by_customer("", True)
        self.assertEqual(results, [])

    def test_orders_by_customer_blank_notshipped(self):
        """
        - значение cust_name пустая строка,
        - shipped = False
        """
        results = get_orders_by_customer("", False)
        self.assertEqual(results, [])

    def test_orders_by_customer_blank_details(self):
        results = get_orders_by_customer("", details=True)
        self.assertEqual(results, [])

    def test_orders_by_customer_blank_shipped_details(self):
        results = get_orders_by_customer("", True, True)
        self.assertEqual(results, [])

    def test_orders_by_customer_blank_notshipped_details(self):
        results = get_orders_by_customer("", False, True)
        self.assertEqual(results, [])

    def test_orders_by_customer(self):
        expected_results = [("wlk001", "cookiemon", "111-111-1111")]
        results = get_orders_by_customer("cookiemon")
        self.assertEqual(results, expected_results)

    def test_orders_by_customer_shipped_only(self):
        results = get_orders_by_customer("cookiemon", True)
        self.assertEqual(results, [])

    def test_orders_by_customer_unshipped_only(self):
        expected_results = [("wlk001", "cookiemon", "111-111-1111")]
        results = get_orders_by_customer("cookiemon", False)
        self.assertEqual(results, expected_results)

    def test_orders_by_customer_with_details(self):
        expected_results = [
            (
                "wlk001",
                "cookiemon",
                "111-111-1111",
                "dark chocolate chip",
                2,
                Decimal("1.00"),
            ),
            (
                "wlk001",
                "cookiemon",
                "111-111-1111",
                "oatmeal raisin",
                12,
                Decimal("3.00"),
            ),
        ]
        results = get_orders_by_customer("cookiemon", details=True)
        self.assertEqual(results, expected_results)

    def test_orders_by_customer_shipped_only_with_details(self):
        results = get_orders_by_customer("cookiemon", True, True)
        self.assertEqual(results, [])

    def test_orders_by_customer_unshipped_only_details(self):
        expected_results = [
            (
                "wlk001",
                "cookiemon",
                "111-111-1111",
                "dark chocolate chip",
                2,
                Decimal("1.00"),
            ),
            (
                "wlk001",
                "cookiemon",
                "111-111-1111",
                "oatmeal raisin",
                12,
                Decimal("3.00"),
            ),
        ]
        results = get_orders_by_customer("cookiemon", False, True)
        self.assertEqual(results, expected_results)

import unittest
from decimal import Decimal
from unittest import mock

from app import get_orders_by_customer
from db import dal, prep_db


class TestApp(unittest.TestCase):
    cookie_orders = [("wlk001", "cookiemon", "111-111-1111")]
    cookie_details = [
        (
            "wlk001",
            "cookiemon",
            "111-111-1111",
            "dark chocolate chip",
            2,
            Decimal("1.00"),
        ),
        ("wlk001", "cookiemon", "111-111-1111", "oatmeal raisin", 12, Decimal("3.00")),
    ]

    @mock.patch("app.select")
    @mock.patch("app.dal.connection")
    def test_orders_by_customer_blank(self, mock_conn, mock_select):
        mock_select.return_value.select_from.return_value.where.return_value = None
        mock_conn.execute.return_value.fetchall.return_value = []
        results = get_orders_by_customer("")
        self.assertEqual(results, [])

    @mock.patch("app.dal.connection")
    def test_orders_by_customer_blank_shipped(self, mock_conn):
        mock_conn.execute.return_value.fetchall.return_value = []
        results = get_orders_by_customer("", True)
        self.assertEqual(results, [])

    @mock.patch("app.dal.connection")
    def test_orders_by_customer(self, mock_conn):
        mock_conn.execute.return_value.fetchall.return_value = self.cookie_orders
        results = get_orders_by_customer("cookiemon")
        self.assertEqual(results, self.cookie_orders)

from decimal import Decimal

import pytest

from app import get_orders_by_customer
from db import dal, prep_db


@pytest.fixture(autouse=True, scope="class")
def setup_db():
    dal.db_init("sqlite:///:memory:")
    prep_db()
    yield
    # здесь можно закрыть соединение, если нужно:
    # dal.connection.close()


def test_orders_by_customer_blank():
    """
    - значение cust_name пустая строка,
    - shipped = None
    """
    results = get_orders_by_customer("")
    assert results == []


def test_orders_by_customer_blank_shipped():
    """
    - значение cust_name пустая строка,
    - shipped = True
    """
    results = get_orders_by_customer("", True)
    assert results == []


def test_orders_by_customer_blank_notshipped():
    """
    - значение cust_name пустая строка,
    - shipped = False
    """
    results = get_orders_by_customer("", False)
    assert results == []


def test_orders_by_customer_blank_details():
    results = get_orders_by_customer("", details=True)
    assert results == []


def test_orders_by_customer_blank_shipped_details():
    results = get_orders_by_customer("", True, True)
    assert results == []


def test_orders_by_customer_blank_notshipped_details():
    results = get_orders_by_customer("", False, True)
    assert results == []


def test_orders_by_customer():
    expected_results = [("wlk001", "cookiemon", "111-111-1111")]
    results = get_orders_by_customer("cookiemon")
    assert results == expected_results


def test_orders_by_customer_shipped_only():
    results = get_orders_by_customer("cookiemon", True)
    assert results == []


def test_orders_by_customer_unshipped_only():
    expected_results = [("wlk001", "cookiemon", "111-111-1111")]
    results = get_orders_by_customer("cookiemon", False)
    assert results == expected_results


def test_orders_by_customer_with_details():
    expected_results = [
        (
            "wlk001",
            "cookiemon",
            "111-111-1111",
            "dark chocolate chip",
            2,
            Decimal("1.00"),
        ),
        (
            "wlk001",
            "cookiemon",
            "111-111-1111",
            "oatmeal raisin",
            12,
            Decimal("3.00"),
        ),
    ]
    results = get_orders_by_customer("cookiemon", details=True)
    assert results == expected_results


def test_orders_by_customer_shipped_only_with_details():
    results = get_orders_by_customer("cookiemon", True, True)
    assert results == []


def test_orders_by_customer_unshipped_only_details():
    expected_results = [
        (
            "wlk001",
            "cookiemon",
            "111-111-1111",
            "dark chocolate chip",
            2,
            Decimal("1.00"),
        ),
        (
            "wlk001",
            "cookiemon",
            "111-111-1111",
            "oatmeal raisin",
            12,
            Decimal("3.00"),
        ),
    ]
    results = get_orders_by_customer("cookiemon", False, True)
    assert results == expected_results

from decimal import Decimal
from unittest import mock

import pytest

from app import get_orders_by_customer
from db import dal, prep_db


# --- общие тестовые данные ---

@pytest.fixture
def cookie_orders():
    return [("wlk001", "cookiemon", "111-111-1111")]


@pytest.fixture
def cookie_details():
    return [
        (
            "wlk001",
            "cookiemon",
            "111-111-1111",
            "dark chocolate chip",
            2,
            Decimal("1.00"),
        ),
        ("wlk001", "cookiemon", "111-111-1111", "oatmeal raisin", 12, Decimal("3.00")),
    ]


# --- тесты ---

@mock.patch("app.select")
@mock.patch("app.dal.connection")
def test_orders_by_customer_blank(mock_conn, mock_select):
    mock_select.return_value.select_from.return_value.where.return_value = None
    mock_conn.execute.return_value.fetchall.return_value = []

    results = get_orders_by_customer("")

    assert results == []


@mock.patch("app.dal.connection")
def test_orders_by_customer_blank_shipped(mock_conn):
    mock_conn.execute.return_value.fetchall.return_value = []

    results = get_orders_by_customer("", True)

    assert results == []


@mock.patch("app.dal.connection")
def test_orders_by_customer(mock_conn, cookie_orders):
    mock_conn.execute.return_value.fetchall.return_value = cookie_orders

    results = get_orders_by_customer("cookiemon")

    assert results == cookie_orders

from sqlalchemy import MetaData, create_engine
metadata = MetaData()
engine = create_engine("sqlite:///Chinook_Sqlite.sqlite")

from sqlalchemy import Table

artist = Table("Artist", metadata, autoload_with=engine)
artist.columns.keys()

from sqlalchemy import select

with engine.begin() as connection:
    s = select(artist).limit(10)
    result = connection.execute(s).fetchall()
result

album = Table('Album', metadata, autoload_with=engine)
res = metadata.tables['Album']
repr(res)

album.foreign_keys

import re
import logging
from ruff_format import format_string
from sqlalchemy import Table, Column, Integer, Numeric, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class MyBase:
    @classmethod
    def display(cls: Base) -> str:
        raw = repr(cls.__table__).replace(f"<{cls.__tablename__}>", f'"{cls.__tablename__}"')
        raw = re.sub(
            r"CallableColumnDefault\(<function datetime\.now at 0x[0-9a-f]+>\)",
            "NOW",
            raw,
        )
        try:
            return format_string(raw)
        except Exception:
            logging.exception(raw)

    def __repr__(self) -> str:
        raw = f"{self.__class__.__name__}"
        columns = ','.join(
            f"{column.name}='{getattr(self, column.name)}'"
            for column in self.__table__.columns
        )
        return format_string(raw + '(' + columns + ')')


class Cookie(Base, MyBase):
    __tablename__ = "cookies"

    cookie_id = Column(Integer(), primary_key=True)
    cookie_name = Column(String(50), index=True)
    cookie_recipe_url = Column(String(255))
    cookie_sku = Column(String(55))
    quantity = Column(Integer())
    unit_cost = Column(Numeric(12, 2))


Cookie.display()

from datetime import datetime
from sqlalchemy import DateTime

class User(Base, MyBase):
    __tablename__ = "users"

    user_id = Column(Integer(), primary_key=True)
    username = Column(String(12), nullable=False, unique=True)
    email_address = Column(String(255), nullable=False)
    phone = Column(String(20), nullable=False)
    password = Column(String(25), nullable=False)
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)

User.display()

import re
import logging
from datetime import datetime
from decimal import Decimal

from ruff_format import format_string
from sqlalchemy import (
    Boolean,
    DateTime,
    ForeignKey,
    Integer,
    Numeric,
    String,
    create_engine,
    select,
)
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    mapped_column,
    relationship,
    Session,
)


class Base(DeclarativeBase):
    """Базовый класс для всех декларативных моделей."""


class MyBase:
    """Миксин для красивого отображения таблицы."""

    @classmethod
    def display(cls) -> str:
        raw = repr(cls.__table__).replace(
            f"<{cls.__tablename__}>", f'"{cls.__tablename__}"'
        )
        raw = re.sub(
            r"CallableColumnDefault\(<function datetime\.now at 0x[0-9a-f]+>\)",
            "NOW",
            raw,
        )
        try:
            return format_string(raw)
        except Exception:
            logging.exception(raw)
            return raw


class Cookie(Base, MyBase):
    __tablename__ = "cookies"

    cookie_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    cookie_name: Mapped[str | None] = mapped_column(String(50), index=True)
    cookie_recipe_url: Mapped[str | None] = mapped_column(String(255))
    cookie_sku: Mapped[str | None] = mapped_column(String(55))
    quantity: Mapped[int | None] = mapped_column(Integer)
    unit_cost: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))


Cookie.display()

class User(Base, MyBase):
    __tablename__ = "users"

    user_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(12), nullable=False, unique=True)
    email_address: Mapped[str] = mapped_column(String(255), nullable=False)
    phone: Mapped[str] = mapped_column(String(20), nullable=False)
    password: Mapped[str] = mapped_column(String(25), nullable=False)
    created_on: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)
    updated_on: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.now, onupdate=datetime.now
    )

    orders: Mapped[list["Order"]] = relationship(
        back_populates="user", order_by="Order.order_id"
    )


User.display()

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("sqlite:///:memory:")
Session = sessionmaker(bind=engine)
session = Session()

import re
import logging
from ruff_format import format_string
from sqlalchemy import Table, Column, Integer, Numeric, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class MyBase:
    @classmethod
    def display(cls: Base) -> str:
        raw = repr(cls.__table__).replace(f"<{cls.__tablename__}>", f'"{cls.__tablename__}"')
        raw = re.sub(
            r"CallableColumnDefault\(<function datetime\.now at 0x[0-9a-f]+>\)",
            "NOW",
            raw,
        )
        try:
            return format_string(raw)
        except Exception:
            logging.exception(raw)

    def __repr__(self) -> str:
        raw = f"{self.__class__.__name__}"
        columns = ','.join(
            f"{column.name}='{getattr(self, column.name)}'"
            for column in self.__table__.columns
        )
        return format_string(raw + '(' + columns + ')')


class Cookie(Base, MyBase):
    __tablename__ = "cookies"

    cookie_id = Column(Integer(), primary_key=True)
    cookie_name = Column(String(50), index=True)
    cookie_recipe_url = Column(String(255))
    cookie_sku = Column(String(55))
    quantity = Column(Integer())
    unit_cost = Column(Numeric(12, 2))


Cookie.display()
from datetime import datetime
from sqlalchemy import DateTime

class User(Base, MyBase):
    __tablename__ = "users"

    user_id = Column(Integer(), primary_key=True)
    username = Column(String(12), nullable=False, unique=True)
    email_address = Column(String(255), nullable=False)
    phone = Column(String(20), nullable=False)
    password = Column(String(25), nullable=False)
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)

User.display()
from sqlalchemy import ForeignKey, Boolean
from sqlalchemy.orm import relationship, backref


class Order(Base, MyBase):
    __tablename__ = "orders"

    order_id = Column(Integer(), primary_key=True)
    user_id = Column(Integer(), ForeignKey("users.user_id"))
    shipped = Column(Boolean(), default=False)

    user = relationship("User", backref=backref("orders", order_by=order_id))


Order.display()
class LineItem(Base, MyBase):
    __tablename__ = "line_items"

    line_item_id = Column(Integer(), primary_key=True)
    order_id = Column(Integer(), ForeignKey("orders.order_id"))
    cookie_id = Column(Integer(), ForeignKey("cookies.cookie_id"))
    quantity = Column(Integer())
    extended_cost = Column(Numeric(12, 2))
    order = relationship("Order", backref=backref("line_items", order_by=line_item_id))
    cookie = relationship("Cookie", uselist=False)

LineItem.display()

Base.metadata.create_all(engine)

#!/usr/bin/env python3
"""Идиоматичный SQLAlchemy 2.0 пример (по мотивам «Essential SQLAlchemy»)."""

import logging
import re
from datetime import datetime
from decimal import Decimal

import pandas as pd
from ruff_format import format_string
from sqlalchemy import (
    Boolean,
    DateTime,
    ForeignKey,
    Integer,
    Numeric,
    String,
    create_engine,
    delete,
    func,
    insert,
    or_,
    select,
    text,
    update,
)
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    Session,
    mapped_column,
    relationship,
)

# ---------------------------------------------------------------------------
# Engine
# ---------------------------------------------------------------------------
engine = create_engine("sqlite:///:memory:", echo=False)


# ---------------------------------------------------------------------------
# Базовый класс с удобным repr и display
# ---------------------------------------------------------------------------
class Base(DeclarativeBase):
    """Базовый класс с repr() и display() для схемы таблицы."""

    def __repr__(self) -> str:
        raw = f"{self.__class__.__name__}"
        columns = ",".join(
            f"{col.name}={getattr(self, col.name)!r}"
            for col in self.__table__.columns
        )
        return format_string(f"{raw}({columns})")

    @classmethod
    def display(cls) -> str:
        raw = repr(cls.__table__).replace(
            f"<{cls.__tablename__}>", f'"{cls.__tablename__}"'
        )
        raw = re.sub(
            r"CallableColumnDefault\(<function datetime\.now at 0x[0-9a-f]+>\)",
            "NOW",
            raw,
        )
        try:
            return format_string(raw)
        except Exception:
            logging.exception(raw)
            return raw


# ---------------------------------------------------------------------------
# Модели
# ---------------------------------------------------------------------------
class Cookie(Base):
    __tablename__ = "cookies"

    cookie_id: Mapped[int] = mapped_column(primary_key=True)
    cookie_name: Mapped[str | None] = mapped_column(String(50), index=True)
    cookie_recipe_url: Mapped[str | None] = mapped_column(String(255))
    cookie_sku: Mapped[str | None] = mapped_column(String(55))
    quantity: Mapped[int | None] = mapped_column(Integer())
    unit_cost: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))


class User(Base):
    __tablename__ = "users"

    user_id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(12), nullable=False, unique=True)
    email_address: Mapped[str] = mapped_column(String(255), nullable=False)
    phone: Mapped[str] = mapped_column(String(20), nullable=False)
    password: Mapped[str] = mapped_column(String(25), nullable=False)
    created_on: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)
    updated_on: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.now, onupdate=datetime.now
    )


class Order(Base):
    __tablename__ = "orders"

    order_id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.user_id"))
    shipped: Mapped[bool] = mapped_column(Boolean, default=False)

    user: Mapped["User"] = relationship(back_populates="orders")
    line_items: Mapped[list["LineItem"]] = relationship(back_populates="order")


class LineItem(Base):
    __tablename__ = "line_items"

    line_item_id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int | None] = mapped_column(ForeignKey("orders.order_id"))
    cookie_id: Mapped[int | None] = mapped_column(ForeignKey("cookies.cookie_id"))
    quantity: Mapped[int | None] = mapped_column(Integer())
    extended_cost: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))

    order: Mapped["Order"] = relationship(back_populates="line_items")
    cookie: Mapped["Cookie"] = relationship(uselist=False)


# Дополняем User.orders после объявления Order (back_populates с обеих сторон)
User.orders = relationship("Order", back_populates="user", order_by="Order.order_id")


# ---------------------------------------------------------------------------
# Создание схемы
# ---------------------------------------------------------------------------
Base.metadata.create_all(engine)

Cookie.display()
User.display()
Order.display()
LineItem.display()


# ---------------------------------------------------------------------------
# Работа с сессией
# ---------------------------------------------------------------------------
with Session(engine) as session:
    # =======================================================================
    # INSERT: одиночные объекты через ORM
    # =======================================================================
    cc_cookie = Cookie(
        cookie_name="chocolate chip",
        cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
        cookie_sku="CC01",
        quantity=12,
        unit_cost=Decimal("0.50"),
    )
    session.add(cc_cookie)
    session.commit()
    print("cc_cookie.cookie_id =", cc_cookie.cookie_id)

    dcc = Cookie(
        cookie_name="dark chocolate chip",
        cookie_recipe_url="http://some.aweso.me/cookie/recipe/recipe_dark.html",
        cookie_sku="CC02",
        quantity=1,
        unit_cost=Decimal("0.75"),
    )
    mol = Cookie(
        cookie_name="molasses",
        cookie_recipe_url="http://some.aweso.me/cookie/recipe_molasses.html",
        cookie_sku="MOL01",
        quantity=1,
        unit_cost=Decimal("0.80"),
    )
    session.add_all([dcc, mol])
    session.flush()
    print([("dcc.cookie_id", dcc.cookie_id), ("mol.cookie_id", mol.cookie_id)])

    # =======================================================================
    # INSERT: bulk-вставка (замена legacy bulk_save_objects)
    # =======================================================================
    session.execute(
        insert(Cookie),
        [
            {
                "cookie_name": "peanut butter",
                "cookie_recipe_url": "http://some.aweso.me/cookie/recipe/peanut.html",
                "cookie_sku": "PB01",
                "quantity": 24,
                "unit_cost": Decimal("0.25"),
            },
            {
                "cookie_name": "oatmeal raisin",
                "cookie_recipe_url": "http://some.okay.me/cookie/raisin.html",
                "cookie_sku": "EWW01",
                "quantity": 100,
                "unit_cost": Decimal("1.00"),
            },
        ],
    )
    session.commit()

    # Пример с .returning() — если нужны обратно PK/имена
    result = session.execute(
        insert(Cookie).returning(Cookie.cookie_id, Cookie.cookie_name),
        [
            {
                "cookie_name": "sugar",
                "cookie_recipe_url": "http://example.com/sugar",
                "cookie_sku": "SGR01",
                "quantity": 50,
                "unit_cost": Decimal("0.40"),
            },
        ],
    )
    print("inserted (returning):", result.all())
    session.commit()

    # =======================================================================
    # SELECT: базовые
    # =======================================================================
    cookies = session.scalars(select(Cookie)).all()
    print(cookies)

    row = session.execute(select(Cookie.cookie_name, Cookie.quantity)).first()
    print(row)

    # =======================================================================
    # DataFrame из select()
    # =======================================================================
    stmt = select(Cookie.quantity, Cookie.cookie_name).order_by(Cookie.quantity.desc())
    rows = session.execute(stmt).all()
    df = pd.DataFrame(rows, columns=["quantity", "cookie_name"])

    headers = [""] + df.columns.tolist()
    data_rows = [[idx, *row] for idx, row in zip(df.index, df.to_numpy().tolist())]
    summary = [headers, None, *data_rows]
    print(summary)

    # =======================================================================
    # LIMIT
    # =======================================================================
    stmt = select(Cookie).order_by(Cookie.quantity).limit(2)
    print([(c.cookie_name,) for c in session.scalars(stmt)])

    # =======================================================================
    # Агрегаты
    # =======================================================================
    inv_count = session.scalar(select(func.sum(Cookie.quantity)))
    print("inv_count =", inv_count)

    rec = session.execute(
        select(func.count(Cookie.cookie_name).label("inventory_count"))
    ).one()
    print((list(rec._mapping.keys()), (rec.inventory_count,)))

    # =======================================================================
    # Фильтры
    # =======================================================================
    record = session.scalars(
        select(Cookie).where(Cookie.cookie_name == "chocolate chip")
    ).first()
    print(record)

    record = session.scalars(
        select(Cookie).filter_by(cookie_name="chocolate chip")
    ).first()
    print(record)

    stmt = select(Cookie).where(Cookie.cookie_name.like("chocolate chip"))
    print([c.cookie_name for c in session.scalars(stmt)])

    # =======================================================================
    # Вычисляемые колонки
    # =======================================================================
    stmt = select(Cookie.cookie_name, ("SKU-" + Cookie.cookie_sku).label("sku"))
    for r in session.execute(stmt):
        print(r)

    stmt = select(
        Cookie.cookie_name,
        func.cast(Cookie.quantity * Cookie.unit_cost, Numeric(12, 2)).label("inv_cost"),
    )
    print([(r.cookie_name, repr(r.inv_cost)) for r in session.execute(stmt)])

    stmt = select(Cookie).where(
        Cookie.quantity > 23, Cookie.unit_cost < Decimal("0.40")
    )
    print([(c.cookie_name,) for c in session.scalars(stmt)])

    stmt = select(Cookie).where(
        or_(
            Cookie.quantity.between(10, 50),
            Cookie.cookie_name.contains("chip"),
        )
    )
    print([(c.cookie_name,) for c in session.scalars(stmt)])

    # =======================================================================
    # UPDATE (ORM-объект)
    # =======================================================================
    cc_cookie = session.scalars(
        select(Cookie).filter_by(cookie_name="chocolate chip")
    ).first()
    assert cc_cookie is not None
    cc_cookie.quantity = (cc_cookie.quantity or 0) + 120
    session.commit()
    print("cc_cookie.quantity =", cc_cookie.quantity)

    # UPDATE (bulk)
    session.execute(
        update(Cookie)
        .where(Cookie.cookie_name == "chocolate chip")
        .values(quantity=Cookie.quantity - 20)
    )
    session.commit()
    print(
        "after bulk update:",
        session.scalars(
            select(Cookie.quantity).where(Cookie.cookie_name == "chocolate chip")
        ).one(),
    )

    # =======================================================================
    # DELETE (ORM)
    # =======================================================================
    dcc_cookie = session.scalars(
        select(Cookie).filter_by(cookie_name="dark chocolate chip")
    ).one()
    session.delete(dcc_cookie)
    session.commit()
    assert (
        session.scalars(
            select(Cookie).filter_by(cookie_name="dark chocolate chip")
        ).first()
        is None
    )

    # DELETE (bulk)
    session.execute(delete(Cookie).where(Cookie.cookie_name == "molasses"))
    session.commit()
    assert (
        session.scalars(select(Cookie).filter_by(cookie_name="molasses")).first()
        is None
    )

    # =======================================================================
    # Пользователи и связи
    # =======================================================================
    cookiemon = User(
        username="cookiemon",
        email_address="mon@cookie.com",
        phone="111-111-1111",
        password="password",
    )
    cakeeater = User(
        username="cakeeater",
        email_address="cakeeater@cake.com",
        phone="222-222-2222",
        password="password",
    )
    pieperson = User(
        username="pieperson",
        email_address="person@pie.com",
        phone="333-333-3333",
        password="password",
    )
    session.add_all([cookiemon, cakeeater, pieperson])
    session.commit()

    o1 = Order()
    o1.user = cookiemon
    session.add(o1)

    cc = session.scalars(
        select(Cookie).filter_by(cookie_name="chocolate chip")
    ).one()
    pb = session.scalars(
        select(Cookie).filter_by(cookie_name="peanut butter")
    ).one()

    line1 = LineItem(cookie=cc, quantity=2, extended_cost=Decimal("1.00"))
    line2 = LineItem(quantity=12, extended_cost=Decimal("3.00"))
    line2.cookie = pb
    line2.order = o1
    o1.line_items.append(line1)
    o1.line_items.append(line2)
    session.commit()

    o2 = Order()
    o2.user = cakeeater
    line1 = LineItem(cookie=cc, quantity=24, extended_cost=Decimal("12.00"))
    oat = session.scalars(
        select(Cookie).filter_by(cookie_name="oatmeal raisin")
    ).one()
    line2 = LineItem(cookie=oat, quantity=6, extended_cost=Decimal("6.00"))
    o2.line_items.append(line1)
    o2.line_items.append(line2)
    session.add(o2)
    session.commit()

    # =======================================================================
    # JOIN
    # =======================================================================
    stmt = (
        select(
            Order.order_id,
            User.username,
            User.phone,
            Cookie.cookie_name,
            LineItem.quantity,
            LineItem.extended_cost,
        )
        .join(User)
        .join(LineItem)
        .join(Cookie)
        .where(User.username == "cookiemon")
    )
    print([tuple(r) for r in session.execute(stmt)])

    # =======================================================================
    # GROUP BY + OUTER JOIN
    # =======================================================================
    stmt = (
        select(User.username, func.count(Order.order_id))
        .outerjoin(Order)
        .group_by(User.username)
    )
    print(session.execute(stmt).all())

    # =======================================================================
    # Функция-обёртка
    # =======================================================================
    def get_orders_by_customer(
        cust_name: str,
        shipped: bool | None = None,
        details: bool = False,
    ):
        stmt = select(Order.order_id, User.username, User.phone).join(User)
        if details:
            stmt = (
                stmt.add_columns(
                    Cookie.cookie_name,
                    LineItem.quantity,
                    LineItem.extended_cost,
                )
                .join(LineItem)
                .join(Cookie)
            )
        if shipped is not None:
            stmt = stmt.where(Order.shipped == shipped)
        stmt = stmt.where(User.username == cust_name)
        return session.execute(stmt).all()

    print(get_orders_by_customer("cakeeater"))
    print(get_orders_by_customer("cakeeater", details=True))
    print(get_orders_by_customer("cakeeater", shipped=True))
    print(get_orders_by_customer("cakeeater", shipped=False))
    print(get_orders_by_customer("cakeeater", shipped=False, details=True))

    # =======================================================================
    # Текстовый SQL
    # =======================================================================
    stmt = select(User).where(text("username='cookiemon'"))
    for u in session.scalars(stmt):
        print(u)