读读文档吧 - flask_sqlalchemy

Quick Starthttps://flask-sqlalchemy.palletsprojects.com/en/3.0.x/quickstart/

Flask-SQLAlchemy does not change how SQLAlchemy works or is used. See the SQLAlchemy documentation to learn how to work with the ORM in depth. The documentation here will only cover setting up the extension, not how to use SQLAlchemy. Flask-SQLAlchemy is a wrapper around SQLAlchemy.

The only required Flask app config is the SQLALCHEMY_DATABASE_URI key.

The model will generate a table name by converting the CamelCase class name to snake_case. 命名规则自动转换，只对表名有用，对列名没用。

class MumAndDad(Base):
    firstName = Column(String(10))
    lastName = Column(String(10))

CREATE TABLE `mum_and_dad`  (
  `firstName` varchar(10),
  `lastName` varchar(10)
) 

实践经验：列名如果都是小写，可以不填；如果是驼峰命名，就需要填入蛇形命名。

class Base(db.Model):
    id = Column(Integer)
    createTime = Column('create_time', DateTime) 

总所周知，Python没有像Java那样，（按参数类型）重载方法的特性。源码显示，这里手写了一个判断，大概意思就是：如果第一个参数是字符串类型，那它就是列名，赋给name变量；且第二个参数就应该是字段类型，赋给type_变量。否则第一个字段就应该是字段类型，赋给type_变量。

# 非完整源码，只摘录了核心部分
# :param name: The name of this column as represented in the database.
# :param type_: The column's type, indicated using an instance which subclasses :class:`~sqlalchemy.types.TypeEngine`.
if args:
    if isinstance(args[0], util.string_types):
        name = args.pop(0)
if args:
    coltype = args[0]
    if hasattr(coltype, "_sqla_type"):
        type_ = args.pop(0)

create_all does not update tables if they are already in the database. If you change a model’s columns, use a migration library like Alembic with Flask-Alembic or Flask-Migrate to generate migrations that update the database schema.

增 db.session.add(obj) adds an object to the session, to be inserted.

改 Modifying an object’s attributes updates the object.

删 db.session.delete(obj) deletes an object. Remember to call db.session.commit() after modifying, adding, or deleting any data.

查 db.session.execute(db.select(...)) constructs a query to select data from the database.

After modifying data, you must call db.session.commit() to commit the changes to the database. Otherwise, they will be discarded at the end of the request.

You may see uses of Model.query to build queries. This is an older interface for queries that is considered legacy in SQLAlchemy（但感觉还挺方便的）. Prefer using db.session.execute(db.select(...)) instead.

Models and Tableshttps://flask-sqlalchemy.palletsprojects.com/en/3.0.x/models/

For convenience, the extension object provides access to names in the sqlalchemy and sqlalchemy.orm modules. So you can use db.Column（IDE不识别） instead of importing and using sqlalchemy.Column（IDE能识别，所以还是用这个）, although the two are equivalent.

A common reason to create a table directly is when defining many to many relationships. The association table doesn't need its own model class, as it will be accessed through the relevant relationship attributes on the related models.

import sqlalchemy as sa

user_book_m2m = db.Table(  # 但还是习惯都用db.Model来创建
    "user_book",
    sa.Column("user_id", sa.ForeignKey(User.id), primary_key=True),
    sa.Column("book_id", sa.ForeignKey(Book.id), primary_key=True),
)

Advanced Customizationhttps://flask-sqlalchemy.palletsprojects.com/en/3.0.x/customizing/If behavior is only needed on some models rather than all models, use an abstract model base class to customize only those models. For example, if some models should track when they are created or updated.

from datetime import datetime

class TimestampModel(db.Model):
    __abstract__ = True
    created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    updated = db.Column(db.DateTime, onupdate=datetime.utcnow)

class Author(db.Model):
    ...

class Post(TimestampModel):
    ...

This can also be done with a mixin class, inheriting from db.Model separately. （多重继承）

class TimestampMixin:
    created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    updated = db.Column(db.DateTime, onupdate=datetime.utcnow)

class Post(TimestampMixin, db.Model):
    ...

Paging Query Resultshttps://flask-sqlalchemy.palletsprojects.com/en/3.0.x/pagination/During a request, this will take page and per_page arguments from the query string request.args. Pass max_per_page to prevent users from requesting too many results on a single page. If not given, the default values will be page 1 with 20 items per page.

Don't create pagination objects manually. They are created by SQLAlchemy.paginate() and Query.paginate().

The Pagination object's Pagination.items attribute is the list of items for the current page. 

def project_list():
    page = wx_request('page', to_int=True)
    per_page = 10
    stmt = db.select(Project).where(and_(Project.a == ... , 
                                         Project.b == ... )) 
    projectPage = db.paginate(stmt, page=page, per_page=per_page)
    projects = projectPage.items
    # 返回
    data = {}
    projectSm = ProjectSm1(many=True)
    data.update({'projects': projectSm.dump(projects)})
    data.update({'has_next': projectPage.has_next})
    return jsonify(data)

定义Model时，用的都是类属性；但query出来后的实例，都拥有对应的实例属性。但二者不是一回事，是SQLAlchemy自动映射上的。

with app.app_context():
    user = User.queryById(1)
    mapper = User.__mapper__
    print(f'{type(mapper)}: {mapper}')
    print(type(user))
    print(f'{type(user.id)}: {user.id}')
    print(type(User))
    print(f'{type(User.id)}: {User.id}')

<class 'sqlalchemy.orm.mapper.Mapper'>: mapped class User->user
<class 'model.user.User'>
<class 'int'>: 1
<class 'flask_sqlalchemy.model.DefaultMeta'>
<class 'sqlalchemy.orm.attributes.InstrumentedAttribute'>: User.id