An object-relational mapper (ORM) represents relational tables, columns, and rows through programming-language objects and attributes. It reduces repetitive SQL and lets database code use regular Python while retaining access to SQL when needed. section 23.4 section 23.4.1
A SQLAlchemy workflow typically involves:
- Creating an engine, metadata container, table schema, and session. section 23.4.1
- Inserting rows, committing them, and selecting all rows or filtered rows. section 23.4.1
- Updating objects or table rows, committing changes, and deleting records. section 23.4.1
- Choosing between table-oriented expressions and declarative Python classes mapped to tables. section 23.4.1
Mapping and changing a people table
A declarative class can represent a table: People defines id, name, and count columns. Querying with filter_by(name='Jill') returns a Python object; assigning jill.count = 22, adding it to the session, and committing persists the update. New People objects can be added similarly, while session.delete removes an existing record. section 23.4.1
dbPath = 'datafile2.db'
engine = create_engine('sqlite:///%s' % dbPath) #1
metadata = MetaData()
people = Table('people', metadata, #2
Column('id', Integer, primary_key=True),
Column('name', String),
Column('count', Integer),
)
Session = sessionmaker(bind=engine) #3
session = Session()
metadata.create_all(engine) #4
The setup listing shows the engine, schema metadata, table columns, session factory, and database-table creation needed before ORM operations. section 23.4.1
