SQLAlchemy connects Python programs to relational databases while mapping tables and columns to Python objects. It automates repetitive database work without hiding access to SQL when direct control is needed. section 23.4.1
A basic workflow combines:
- An engine for connecting to the database. section 23.4.1
- Metadata and Table objects for declaring schemas and columns. section 23.4.1
- A session for inserting, querying, updating, deleting, and committing records. section 23.4.1
Table-oriented and object-oriented access
Define a people table with id, name, and count columns, insert Bob, Jill, and Joe, query Jill, then update her count. A declarative People class provides the same table through Python objects: create Jane with session.add(), change Jill.count, or remove Jane with session.delete(). 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
Use the setup listing to see engine creation, schema declaration, session construction, and table creation together. section 23.4.1
