在本文你会看到

怎么配置一对一映射
怎么使用一对一数据
怎么配置一对多映射
怎么使用一对多数据
怎么配置多对多映射
怎么使用多对多数据

开始

最近再用Flask来做一个博客，在用SQLAlchemy进行配置数据库的时候发现了问题，网上的博客和官方文档都只是讲了一点，自己跟着做怎么也实现不了，最后通过找了好几个不同的博客终于写出来，想记录一下整个过程

怎么配置一对一映射

一对一映射是里面最简单的映射，我们先创建数据库，这里我用的是sqlite数据库

CREATE TABLE POST (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  TITLE TEXT NOT NULL,
  BODY TEXT NOT NULL,
  UP_DATE DATETIME NOT NULL,
  CLICKED INTEGER NOT NULL,
  );

数据库创建好后在程序里面创建一个映射数据的类

class Post(db.Model):
    __tablename__ = "post"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(80))
    body = db.Column(db.Text)
    up_date = db.Column(db.DateTime)
    clicked = db.Column(db.Integer)
    def __init__(self, title, body, clicked, up_date=None):
        self.title = title
        self.body = body
        if up_date is None:
            up_date = datetime.utcnow()
        self.up_date = up_date
        self.clicked = clicked

    def __repr__(self):
        return '<Post %r>' % self.title

创建好了对象就完成了映射下面开始使用了

怎么使用一对一数据

增

通过POST请求获得数据然后存储到数据库中

        title = request.form.get("title")
        content = request.form.get("content")
        p = Post(title, content, 0)
        db.session.add(p)
        db.session.commit()

删

与增相似，只不过把db.session.add()改成db.session.delete()

查

在创建对象时需要继承db.model，SQLalchemy就可以使用对象中的query方法了

 Post.query.all()

查询所有数据 SQLAlchemy ORM教程之二：Query这里面有其他查询的方法

怎么配置一对多映射

先是创建数据库

CREATE TABLE POST (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  TITLE TEXT NOT NULL,
  BODY TEXT NOT NULL,
  UP_DATE DATETIME NOT NULL,
  CLICKED INTEGER NOT NULL,
  CATEGORY_ID INTEGER NOT NULL ,
    FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY(ID)
);

在原本的基础上新增了一个category的外键，相应的在映射对象中添加category映射

class Post(db.Model):
    __tablename__ = "post"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(80))
    body = db.Column(db.Text)
    up_date = db.Column(db.DateTime)
    clicked = db.Column(db.Integer)

    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category',
                               backref=db.backref('post', lazy='dynamic'))
    def __init__(self, title, body, category, clicked, up_date=None):
        self.title = title
        self.body = body
        if up_date is None:
            up_date = datetime.utcnow()
        self.up_date = up_date
        self.category = category
        self.clicked = clicked

    def __repr__(self):
        return '<Post %r>' % self.title

db.relationship()官方文档解释是

What does db.relationship() do? That function returns a new property that can do multiple things. In this case we told it to point to the Address class and load multiple of those. How does it know that this will return more than one address? Because SQLAlchemy guesses a useful default from your declaration. If you would want to have a one-to-one relationship you can pass uselist=False to relationship()

对于backref()的解释是

So what do backref and lazy mean? backref is a simple way to also declare a new property on the Address class. You can then also use my_address.person to get to the person at that address. lazy defines when SQLAlchemy will load the data from the database:

select / True (which is the default, but explicit is better than implicit) means that SQLAlchemy will load the data as necessary in one go using a standard select statement.

joined / False tells SQLAlchemy to load the relationship in the same query as the parent using a JOIN statement.

subquery works like joined but instead SQLAlchemy will use a subquery.

dynamic is special and can be useful if you have many items and always want to apply additional SQL filters to them. Instead of loading the items SQLAlchemy will return another query object which you can further refine before loading the items. Note that this cannot be turned into a different loading strategy when querying so it’s often a good idea to avoid using this in favor of lazy=True. A query object equivalent to a dynamic user.addresses relationship can be created using Address.query.with_parent(user) while still being able to use lazy or eager loading on the relationship itself as necessary.

怎么使用一对多数据

##增

        title = request.form.get("title")
        content = request.form.get("content")
        category = request.form.get("category")
        tags_web = request.form.get("tags")
        tags_str = tags_web.split(",")
        c = Category(category)
        p = Post(title, content, c, 0)
        db.session.add(p)
        db.session.commit()

##删与一对一类似

##查与一对一类似

怎么配置多对多映射

多对多的配置比较特殊，它通过一张辅助表类实现多对多，所以在原本的数据库不需要添加任何字段，但需要新建一张辅助表

CREATE TABLE TAG_POST(
  ID INTEGER PRIMARY KEY AUTOINCREMENT ,
  TAG_ID INTEGER  ,
  POST_ID INTEGER
);

然后在主程序中建立表格对应

tags = db.Table('tag_post',
                db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
                db.Column('post_id', db.Integer, db.ForeignKey('post.id'))
                )

在映射对象中加入映射

class Post(db.Model):
    __tablename__ = "post"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(80))
    body = db.Column(db.Text)
    up_date = db.Column(db.DateTime)
    clicked = db.Column(db.Integer)

    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category',
                               backref=db.backref('post', lazy='dynamic'))
    tags = db.relationship('Tag', secondary=tag_post,
                           backref=db.backref('post', lazy='dynamic'))

    def __init__(self, title, body, category, clicked, up_date=None):
        self.title = title
        self.body = body
        if up_date is None:
            up_date = datetime.utcnow()
        self.up_date = up_date
        self.category = category
        self.clicked = clicked

    def __repr__(self):
        return '<Post %r>' % self.title

怎么使用多对多数据

增

        title = request.form.get("title")
        content = request.form.get("content")
        category = request.form.get("category")
        tags_web = request.form.get("tags")
        tags_str = tags_web.split(",")
        c = Category(category)
        p = Post(title, content, c, 0)
        for tag in tags_str:
            t = Tag(tag)
            p.tags.append(t)
        db.session.add(p)
        db.session.commit()

删

改

查

想通过tag对象来找到里面的post的个数可以使用tag.post.count()

SQLAlchemy数据库映射和使用