flask_十三、一对一、一对多及多对多相关

一对一、一对多、多对多相关

pipenv shell

flask shell

 

ORM对象关系映射：通过操作python类的对象，把对象、以及对象的关系映射到数据库表

 

app.py内容

 

# encoding=utf-8
from flask import Flask,render_template,flash,url_for,redirect,request
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import FlaskForm
from wtforms import TextAreaField,SubmitField
from wtforms.validators import DataRequired

app = Flask(__name__)

import os
app.secret_key = os.getenv('SECRET_KEY','secret string')
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL','sqlite:///' + os.path.join(app.root_path,'data.db'))
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# 该方法跟模板上下文函数类似，返回的是一个字典，将对象以key和value
@app.shell_context_processor
def make_shell_content():
    return dict(db=db,Note=Note,Author=Author,Article=Article,Writer=Writer,Book=Book)

class Author(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(70), unique=True)
    articles = db.relationship('Article') # 定义关联函数，关联属性，一对多一，即为标量，出发侧，参数是关联的模型类

    def __repr__(self):
        return '<Author id: %r, name: %r>' % (self.id, self.name)

class Article(db.Model):
    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')) #定义外键，对应多，即为集量

    # 指定显示内容，否则默认显示<表名 主键id>
    def __repr__(self):
        return '<Article id: %r, title: %r, body: %r, author_id: %r>' % (self.id, self.title,self.body,self.author_id)


class Writer(db.Model):
    id = db.Column(db.Integer,primary_key = True)
    name = db.Column(db.String(70),unique = True)
    # back_populats定义双向关系
    # back_populats参数的值需要设为关系另一侧的关系属性名
    books = db.relationship('Book', back_populates='writer')

    def __repr__(self):
        return '<Writer id: %r, name: %r>' %(self.id,self.name)


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

    def __repr__(self):
        return '<Book id: %r, title: %r, writer_id:%r>' %(self.id,self.title,self.writer_id)
#
class Singer(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(70), unique=True)
    songs = db.relationship('Song', backref='singer')
    # songs = db.relationship('Song',uselist=False,backref=db.backref('singer',uselist=False))


    def __repr__(self):
        return '<Singer id: %r, name: %r>' %(self.id,self.name)
#
class Song(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), index=True)
    singer_id = db.Column(db.Integer, db.ForeignKey('singer.id'))

    def __repr__(self):
        return '<Song id: %r, name: %r, singer_id>' %(self.id,self.name,self.singer_id)
#
class Citizen(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(70), unique=True)
    city_id = db.Column(db.Integer, db.ForeignKey('city.id'))
    # back_population的值为另一侧的关系属性名
    # city = db.relationship('City')
    # relationship函数的第一个参数是另一侧的模型名（类型）
    city = db.relationship('City', back_populates='citizen')
    def __repr__(self):
        return '<Citizen id: %r, name: %r, city_id: %r>' %(self.id,self.name,self.city_id)
#
class City(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), unique=True)
    # back_population的值为另一侧的关系属性名
    # relationship函数的第一个参数是另一侧的模型名（类型）
    citizen = db.relationship('Citizen', back_populates='city')
    def __repr__(self):
        return '<City id: %r, name: %r>' %(self.id,self.name)
#
# 101行
class Country(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), unique=True)
    capital = db.relationship('Capital',uselist=False)
# 109hang
    def __repr__(self):
        return '<Country id: %r, name: %r>' %(self.id,self.name)
#
class Capital(db.Model):
    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')

    def __repr__(self):
        return '<Capital id: %r, name: %r, country_id: %r>' %(self.id,self.name,self.country_id)
# 多对多关系
association_table = db.Table('association',
                    db.Column('student_id',db.Integer,db.ForeignKey('student.id')),
                    db.Column('teacher_id',db.Integer,db.ForeignKey('teacher.id')),

                             )
# 125行代码
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') # collection
    def __repr__(self):
        return '<Student id: %r, name: %r, grade: %r>' %(self.id, self.name,self.grade)
# 135行行号
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))
    # black_populates，定义双向关系
    # back_populations参数的值需要设为关系另一侧的关系属性名
    students = db.relationship('Student',
                               secondary = association_table,
                               back_populates = 'teachers') # collection
    def __repr__(self):
        return '<Teacher id: %r,name: %r,office: %r>' %(self.id,self.name,self.office)

#定义Note模型类，映射到表note
class Note(db.Model):
    id = db.Column(db.Integer,primary_key = True)
    body = db.Column(db.Text)

    def __repr__(self):
        # %r 是用repr()方法处理对象，返回类型本身，而不进行类型转化
        return '<Note id: %r, body: %r>' % (self.id,self.body)


class NewNoteForm(FlaskForm):
    body = TextAreaField('Body',validators=[DataRequired()]) # 跳转新页面的body框
    submit = SubmitField('Save')

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

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

import click
@app.cli.command()
def initdb():
  db.create_all()
  click.echo('Initialized database')

@app.route('/new/',methods=['GET','POST'])
def new_note():
    print("request.form in new_note: %s" % request.form)
    form = NewNoteForm()
    print("form:" , form)
    print("form.validate_on_submit(): " ,form.validate_on_submit())
    # if request.method == 'POST' and form validate():
    if form.validate_on_submit():
        print("pass")
        try:
            print(Note.query.all())
        except:
            print("initdb...")
            initDB()
        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):
    print("request.form in edit_note: %s" % request.form)
    form = EditNoteForm()
    print("form.body: %s"  % form.body)
    print("form.body.data: %s"  % form.body.data)
    note = Note.query.get(note_id)
    print("note.body: %s" % note.body)
    if form.validate_on_submit(): # 判断表单提交的数据是否不为空+是post请求
        print("validated")
        note.body = form.body.data # 赋值新值
        print("note.body in validate: %s" % note.body)
        db.session.commit()
        flash("your note is edited")
        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('/index/')
def index():
    db.create_all()
    form = NewNoteForm
    form_delete = DeleteNoteForm()
    notes = Note.query.all()
    return render_template('index.html',notes=notes,form=form,form_delete=form_delete)


if __name__ == "__main__":
    print(app.config)
    app.run(debug=True)

 

二、一对多和多对多关系（基础版，Book和Writer类）

 

>>> from app import Book,Writer,db
>>> Book.query.all()

>>> Book.query.all()
[]
>>> Writer.query.all()
[]
>>> b1 = Book(title = 'ssssssss')
>>> b2 = Book(title = 'weuwuei')
>>> w1 = Writer(name = 'seedddf')
>>> db.session.add_all([b1,b2,w1])

>>> w1.books
[]
>>> w1.books.append(b1)
>>> w1.books.append(b2)
>>> w1.books
[<Book id: None, title: 'ssssssss', writer_id:None>, <Book id: None, title: 'weuwuei', writer_id:None>]

>>> db.session.rollback()
>>> db.session.add_all([b1,b2,w1])

>>> w1.books
[<Book id: None, title: 'ssssssss', writer_id:1>, <Book id: None, title: 'weuwuei', writer_id:1>]
>>>
>>> b1.writer
<Writer id: 1, name: 'seedddf'>
>>> w1.books
[<Book id: None, title: 'ssssssss', writer_id:1>, <Book id: None, title: 'weuwuei', writer_id:1>]
>>>


>>> db
<SQLAlchemy engine=sqlite:///E:\FlaskTest\data.db>
>>>
>>> w1.books
[<Book id: None, title: 'ssssssss', writer_id:1>, <Book id: None, title: 'weuwuei', writer_id:1>]
>>> w1.books.pop()
<Book id: None, title: 'weuwuei', writer_id:1>
>>> db.session.rollback()
>>> w1.books
[<Book id: None, title: 'ssssssss', writer_id:1>]
>>>

 

三、一对多和多对多关系（优化版，Song和Singer类）

 

>>> from app import db,Singer,Song
>>> Singer.query.all()
[]
>>> c1 = Singer(name = 'xxx')
>>> c2 = Singer(name = '111')
>>> db.session.add_all([c1,c2])
>>> Singer.query.all()
[<Singer id: 1, name: 'xxx'>, <Singer id: 2, name: '111'>]
>>> db.session.commit()
>>> Singer.query.all()
[<Singer id: 1, name: 'xxx'>, <Singer id: 2, name: '111'>]
>>>

 

四、backref相关

back_popilates简写的方法——backref(不推荐使用)（需要指定参数uselist的时候用backref）

 

 

>>> from app import db,Singer,Song
>>> db
<SQLAlchemy engine=sqlite:///E:\FlaskTest\data.db>
>>> Song.query.all()
[<Song id: 1, name: 'yyy'>, <Song id: 2, name: 'zzz'>, <Song id: 3, name: 'ttt'>, <Song id: 4, name: 'uuu'>]
>>>
>>> Singer.query.all()
[<Singer id: 1, name: 'xxx'>, <Singer id: 2, name: '111'>]
>>>
>>>
>>> s1 = Singer(name='wer')
>>> singer1 = Singer(name='wertwe')
>>> song1 = Song(name='wsdefed')
>>> song2 = Song(name='idsesf')
>>>
>>>
>>> singer1.songs
[]
>>>
>>> singer1.songs.append(song1)
>>> singer1.songs.append(song2)
>>> singer1.songs
[<Song id: None, name: 'wsdefed'>, <Song id: None, name: 'idsesf'>]
>>>
>>> song1.singer
<Singer id: None, name: 'wertwe'>
>>>
>>> db.session.commit()
>>>
>>> song1.singer
<Singer id: None, name: 'wertwe'>
>>>
>>>
>>> db.session.add_all([singer1,song1,song2])
>>> song1.singer
<Singer id: None, name: 'wertwe'>
>>> singer1
<Singer id: None, name: 'wertwe'>
>>> db.session.commit()
>>>
>>> singer1
<Singer id: 3, name: 'wertwe'>
>>> song1.singer
<Singer id: 3, name: 'wertwe'>
>>>

4.1backref不指定uselist的效果

 

songs = db.relationship('Song', backref='singer')

 

4.1.1生效的效果：

>>> db.drop_all()
>>> db.create_all()
>>> Song.query.all()
[]
>>> Singer.query.all()
[]
>>>
>>> singer1 = Singer(name = 'sddddssdf')
>>> singer1
<Singer id: None, name: 'sddddssdf'>
>>>
>>>
>>> singer1.songs
[]
>>> song1 = Song(name = 'sdfsdf')
>>> song1
<Song id: None, name: 'sdfsdf', singer_id: None>

4.2backref指定uselist的效果

（uselist=false指定为标量，把集量强制转化为标量）

 

songs = db.relationship('Song',uselist=False,backref=db.backref('singer',uselist=False))

 

生效的效果（需要指定参数uselist的时候用backref）

 

4.2.1Append为啥会报错

 

>>> Singer.query.all()
[]
>>> Song.query.all()
[]
>>> singer1 = Singer(name = 'werwer')
>>> song1 = Song(name = 'weiuwer')
>>> song2 = Song(name = 'werwer')
>>> db.session.add_all([singer1,song1,song2])
>>> db.session.commit()
>>> singer1
<Singer id: 1, name: 'werwer'>
>>> song1.singer = singer1
>>> song2.singer = singer1


>>> singer1.songs.append(song1)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
AttributeError: 'Song' object has no attribute 'append'
>>>
因为song是标量属性

>>> singer1.songs  #只返回了一个，因为被覆盖了，被指定的标量，标量只能有一个
<Song id: 2, name: 'werwer', singer_id: 1>
>>> song1.singer
<Singer id: 1, name: 'werwer'>
>>> song2.singer
<Singer id: 1, name: 'werwer'>

>>> db.session.commit()
>>>
>>> song1.singer
<Singer id: 1, name: 'werwer'>
>>> song2.singer
<Singer id: 1, name: 'werwer'>

 

五、一对一关系（国家和首都）

 

 六、多对多关系

 

 

from app import Student,Teacher,db
>>> Student.query.all()
[]
>>> Teacher.query.all()
[]


>>> s1 = Student(name = 'werwer')
>>> s2 = Student(name = 'werwer')
>>> t1 = Teacher(name = 'werwe')
>>> t2 = Teacher(name = 'weur')
>>>
>>> s1.teachers
[]
>>> s1.teachers.append(s1)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\collections.py", line 1169, in append
    item = __set(self, item, _sa_initiator)
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\collections.py", line 1134, in __set
    item = executor.fire_append_event(item, _sa_initiator)
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\collections.py", line 753, in fire_append_event
    return self.attr.fire_append_event(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\attributes.py", line 1429, in fire_append_event
    value = fn(state, value, initiator or self._append_token)
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\attributes.py", line 1766, in emit_backref_from_collection_append_event
    child_impl = child_state.manager[key].impl
KeyError: 'students'
>>>
>>>

 

 

 

>>> s1 = Student(name = 'werwer')
>>> s2 = Student(name = 'werwer')
>>> t1 = Teacher(name = 'werwe')
>>> t2 = Teacher(name = 'weur')
>>>
>>> s1.teachers
[]
>>>
>>> s1.teachers.append(t1)
>>> s1.teachers.append(t2)
>>> db.session.add_all([t1,t2,s1,s2])
>>> db.session.commit()
Traceback (most recent call last):
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\engine\base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\engine\default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: student.name

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "<string>", line 2, in commit
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\session.py", line 1431, in commit
    self._transaction.commit(_to_root=self.future)
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\session.py", line 829, in commit
    self._prepare_impl()
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\session.py", line 808, in _prepare_impl
    self.session.flush()
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\session.py", line 3363, in flush
    self._flush(objects)
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\session.py", line 3502, in _flush
    with util.safe_reraise():
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\session.py", line 3463, in _flush
    flush_context.execute()
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 456, in execute
    rec.execute(self)
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 630, in execute
    util.preloaded.orm_persistence.save_obj(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\persistence.py", line 245, in save_obj
    _emit_insert_statements(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\orm\persistence.py", line 1238, in _emit_insert_statements
    result = connection._execute_20(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\engine\base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\sql\elements.py", line 326, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\engine\base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\engine\base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\engine\base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\engine\base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\renyc\.virtualenvs\FlaskTest-BZucsfHb\lib\site-packages\sqlalchemy\engine\default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: student.name
[SQL: INSERT INTO student (name, grade) VALUES (?, ?)]
[parameters: ('werwer', None)]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

 

>>> s2 = Student(name = 'jksfhakdsf')
>>> db.session.rollback()
>>> db.session.add_all([s1,s2,t1,t2])
>>> db.session.commit()
>>> s1.teachers
[<Teacher id: 1,name: 'werwe',office: None>, <Teacher id: 2,name: 'weur',office: None>]
>>> t1.students
[<Student id: 1, name: 'werwer', grade: None>]
>>> t1.students.append(s2)
>>> t1.students
[<Student id: 1, name: 'werwer', grade: None>, <Student id: 2, name: 'jksfhakdsf', grade: None>]
>>> db.session.commit()
>>>

 

6.1最终效果（会新生成一个assodation表）

 

 

七、关系定义：

1、先把一对多关系的定义过程、实现过程、双向关系实现，backref简化定义，理解透彻

2、多对一、一对一原理跟一对多基本一样，区别在于有个标量属性和集量的属性区别

3、理解多对多关系中的关系表的定义，和关联原理