1. ORM简介

ORM技术(Object-Relational Mapping)，就是把关系数据库的表结构映射到对象上，借此实现把对数据库的操作改变成为对象的操作。

Python常见的ORM是SQLAlchemy，Flask中可以有Flask-SQLAlchemy库，是在SQLAlchemy的基础上，继续做了一层包装，简化了对数据库上下文的管理，所以我们选择Flask-SQLAlchemy作为学习的对象。

按照《Flask Web开发实战》——搭建开发环境的方法搭建新的Flask开发环境，按照相关的库。这里选择sqlite作为数据库，如果是MySQL等其他数据库，还需要安装相关的driver。

pipenv install Flask
pipenv install flask-sqlalchemy

2. demo展示

这里先做一个demo的展示，新建app.py，包括创建表、数据的增删改查。

2.1 基本的环境准备

核心是需要指定数据库的URI，不同数据库有不同的格式
db = SQLAlchemy(app)是init，并获取到数据库的上下文

import click
import os
from flask import Flask, flash, redirect, url_for, render_template, abort
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import FlaskForm
from wtforms import TextAreaField, SubmitField
from wtforms.validators import DataRequired


app = Flask(__name__)

# 指定数据库的URI
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL', 'sqlite:///' + os.path.join(app.root_path, 'data.db'))
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = os.getenv('SECRET_KEY', 'secret string')

db = SQLAlchemy(app)

2.2 创建表

- 这里继续创建Note表，用Class表示我们的表

class Note(db.Model):
    __tablename__ = 'note'

    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.Text)

初始化的操作可以在shell中完成，调用db.create_all()，这样在sqlite中就会自动创建note表

2.3 增删改查

增删改查可以跟Flask中的Form一起做，核心还是对db的操作，继续在app.py里添加

# 新建三个表单类，用于创建表单
class NewNoteForm(FlaskForm):
    body = TextAreaField('Body', validators=[DataRequired()])
    submit = SubmitField('Save')


class UpdateNoteForm(FlaskForm):
    body = TextAreaField('body', validators=[DataRequired()])
    submit = SubmitField('Update')


class DeleteNoteForm(FlaskForm):
    submit = SubmitField('Delete')

@app.route('/new', methods=['GET', 'POST'])
def new_note():
    form = NewNoteForm()
    if form.validate_on_submit():
        body = form.body.data
        note = Note(body=body)
        db.session.add(note)
        db.session.commit()
        flash("Your Note is saved.")
        return redirect(url_for("index"))
    return render_template('new_note.html', form=form)


@app.route('/edit/<int:note_id>', methods=['GET', 'POST'])
def edit_note(note_id):
    form = UpdateNoteForm()
    note = Note.query.get(note_id)
    if form.validate_on_submit():
        note.body = form.body.data
        db.session.commit()
        flash("Your Note is updated!")
        return redirect(url_for("index"))
    form.body.data = note.body
    return render_template('edit_note.html', form=form)


@app.route('/delete/<int:note_id>', methods=['POST'])
def delete_note(note_id):
    form = DeleteNoteForm()
    if form.validate_on_submit():
        note = Note.query.get(note_id)
        db.session.delete(note)
        db.session.commit()
        flash("Your Note is deleted!")
    else:
        abort(400)
    return redirect(url_for("index"))


@app.route('/', methods=['GET'])
def index():
    form = DeleteNoteForm()
    notes = Note.query.all()
    return render_template('index.html', notes=notes, form=form)

2.4 效果

1577882217424.jpg

3. 关系的设置

数据库中的表之间常常需要相互关联，SQLAlchemy也可以定义这些关系。

3.1 定义表

常见的关系有：

一对多（作者与文章，典型的一对多）

#   one to more
class Author(db.Model):
    __tablename__ = 'author'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(70), unique=True)
    phone = db.Column(db.String(20))
    articles = db.relationship('Article')


class Article(db.Model):
    __tablename__ = 'article'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(50), index=True)
    body = db.Column(db.Text)
    author_id = db.Column(db.Integer, db.ForeignKey('author.id'))

一对多（双向）

 #  one to more both
class Writer(db.Model):
    __tablename__ = 'writer'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(70), unique=True)
    books = db.relationship('Book', back_populates='writer')


class Book(db.Model):
    __tablename__ = 'book'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(50), index=True)
    writer_id = db.Column(db.Integer, db.ForeignKey('writer.id'))
    writer = db.relationship('Writer', back_populates='books')

多对一

#  more to one
class Citizen(db.Model):
    __tablename__ = 'citizen'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True)
    city_id = db.Column(db.Integer, db.ForeignKey('city.id'))
    city = db.relationship('City')


class City(db.Model):
    __tablename__ = 'city'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), unique=True)

一对一

#  one to one
class Country(db.Model):
    __tablename__ = 'country'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), unique=True)
    capital = db.relationship('Capital', uselist=False)


class Capital(db.Model):
    __tablename__ = 'capital'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), unique=True)
    country_id = db.Column(db.Integer, db.ForeignKey('country.id'))
    country = db.relationship('Country')

多对多

# many to many both
association_table = db.Table('association_table',
                             db.Column('student_id', db.Integer, db.ForeignKey('student.id')),
                             db.Column('teacher_id', db.Integer, db.ForeignKey('teacher.id')))


class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(70), unique=True)
    grade = db.Column(db.String(20))
    teachers = db.relationship('Teacher', secondary=association_table, back_populates='students')


class Teacher(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(70), unique=True)
    office = db.Column(db.String(20))
    students = db.relationship('Student',
                               secondary=association_table,
                               back_populates='teachers')

3.2 操作

from app import db, Student, Teacher

zhangsan = Student(name='zhangsan')
lisi = Student(name='lisi')

teacher_wang = Teacher(name='teacher_wang', office='Chinese')

zhangsan.teachers.append(teacher_wang)
db.session.add(zhangsan)
db.session.add(lisi)
db.session.add(teacher_wang)
db.session.commit()