Example of reflection in SQLAlchemy

reflection.py

"""An example of table reflection with SQLAlchemy.

Test with Python 3.10 & SQLAlchemy 2.0.

See: https://stackoverflow.com/a/75389730/4244912
"""
from sqlalchemy import Table, create_engine, text
from sqlalchemy.orm import DeclarativeBase, Session

# Create a SQLAlchemy engine connected to an in-memory SQLite3 DB:
engine = create_engine("sqlite:///:memory:")

# Create the table in the DB using DDL:
DDL = text(
    """
    CREATE TABLE Book (
        "id" INTEGER PRIMARY KEY AUTOINCREMENT,
        isbn TEXT NOT NULL,
        title TEXT NOT NULL,
        author TEXT NOY NULL
    );
    """
)
with Session(engine) as session:
    session.execute(DDL)

# Confirm table is empty:
select_all = text("SELECT * FROM Book;")
with Session(engine) as session:
    print(session.execute(select_all).fetchall())
    # []


# Use reflection to automatically generate a SQLAlchemy Book model:
class Base(DeclarativeBase):
    pass


class Book(Base):
    __table__ = Table("Book", Base.metadata, autoload_with=engine)


# Insert a new book using the reflected table:
the_hobbit = Book(isbn="9780007270613", title="The Hobbit", author="J. R. R. Tolkien")

with Session(engine) as session:
    session.add(the_hobbit)
    session.flush()
    session.commit()

with Session(engine) as session:
    print(session.execute(select_all).one())
    # [(1, '9780007270613', 'The Hobbit', 'J. R. R. Tolkien')]