A SQL query can return everything needed for an invoice yet leave the relationships among orders, items, and products as flat rows. Reconstructing that hierarchy in application code is extra work. SQLAlchemy’s object-relational mapper (ORM) bridges this gap by mapping relational data to Python objects. section 10.5
You can still access a database with SQL directly. SQLAlchemy offers a different working style: define Python classes for table structures, field types, and relationships, then work with objects while the library generates the corresponding SQL. It also abstracts many differences between database systems and helps protect queries from SQL injection when user-supplied data is involved. section 10.5.1 section 10.6
Persisting users
For a Flask login flow, a User model defines fields such as an ID, email, and hashed password. SQLAlchemy manages those records in SQLite so the application can retrieve and identify users later. Flask-SQLAlchemy supplies convenience definitions for this integration; the classes could also be defined with SQLAlchemy alone. section 8.2.1 section 10.6
class Post(db.Model):
__tablename__ = "post"
post_uid = db.Column(db.String, primary_key=True, default=get_uuid)
parent_uid = db.Column(db.String,
db.ForeignKey("post.post_uid"),
default=None) #1
sort_key = db.Column(db.Integer,
nullable=False, unique=True,
default=get_next_sort_key) #2
user_uid = db.Column(db.String,
db.ForeignKey("user.user_uid"),
nullable=False, index=True)
title = db.Column(db.String)
content = db.Column(db.String)
children = db.relationship("Post",
backref=db.backref("parent",
remote_side=[post_uid], lazy="joined")) #3
active = db.Column(db.Boolean, nullable=False, default=True)
created = db.Column(db.DateTime,
nullable=False,
default=datetime.now(tz=timezone.utc))
updated = db.Column(db.DateTime, nullable=False, default=datetime.now(
tz=timezone.utc), onupdate=datetime.now(tz=timezone.utc))
The Post model shows a relationship that is more than a collection of columns: parent_uid identifies a parent post, while SQLAlchemy’s children relationship gives a queried Post object access to its child posts. section 11.7.1
SQLite keeps this application setup small because it needs no separate database server. SQLAlchemy also reduces dependence on that choice, making a later move to another database system easier. The practical payoff is to handle persistent, related data in Python terms without constantly switching between Python objects and SQL result sets. section 10.4 section 10.8
