flask--表的外键约束--表关系

表关系

基础框架

from sqlalchemy import create_engine, and_, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, Text, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship, backref
import random

HOSTNAME = '127.0.0.1'
DATABASE = 'demo0502'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'root'

DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

Base = declarative_base(engine)

一对一

其实市一对多的特殊情况，从以上的一对多的例子中不难发现，一对应得是User表，而多对应得是Address，也就是说一个User对象有多个Address。因此要将一对多转换成一对一，只要设置一个User对象对应一个Address对象即可。只要在User表中得addresses字段上添加userlist=False就可以达到一对一得效果。

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)
    extend = relationship("UserExtend", uselist = False)

class UserExtend(Base):
    __tablename__ = 'user_extend'

    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    # 外键
    uid = Column(Integer, ForeignKey("user.id"))
    user = relationship("User") # , backref = "extend"

# Base.metadata.drop_all()
Base.metadata.create_all()
session = sessionmaker(bind=engine)()

# user = User(username="happy")
# extend1 = UserExtend(school='btbu')
# extend2 = UserExtend(school='hd')
# extend1.user = user
# extend2.user = user
# session.add(extend1)
# session.add(extend2)
# session.commit()

# 一对一采用这种添加方式，上面得方式仍不可取
# user.extend.append(extend1)
# # user.extend.append(extend2)
# # session.add(extend1)
# # session.add(extend2)
# # session.commit()

user = User(username='happy')
extend = UserExtend(school='BTBU')
extend.user = user
session.add(extend)
session.commit()

多对多

多对多需要一个中间表来作为连接，同理在sqlalchemy中得ORM也需要一个中间表，加入现在有一个Teacher和一个Classes表，即老师和班级，一个老师可以教多个班级，一个班级可以有多个老师，是一种典型得多对多得关系，那么通过sqlalchemy得ORM得实现方式。

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, Text, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship, backref
import random
from sqlalchemy import Table

HOSTNAME = '127.0.0.1'
DATABASE = 'demo0502'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'root'

DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

Base = declarative_base(engine)

# 中间表得定义
teacher_classes = Table(
    "teacher_classes",
    Base.metadata,
    Column('teacher_id', Integer, ForeignKey('teacher.id')),
    Column('classes_id', Integer, ForeignKey('classes.id'))
)

class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))

    classes = relationship('Classes', backref='teachers', secondary= teacher_classes)

    def __str__(self):
        return "Teacher(name:%s)" % self.name

class Classes(Base):
    __tablename__ = 'classes'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    def __str__(self):
        return "Teacher(name:%s)" % self.name

# Base.metadata.drop_all()
Base.metadata.create_all()
session = sessionmaker(bind=engine)()

# teacher1 = Teacher(name='happy')
# # teacher2 = Teacher(name='gd')
# #
# # classes1 = Classes(name='1班')
# # classes2 = Classes(name='2班')
# #
# # teacher1.classes.append(classes1)
# # teacher1.classes.append(classes2)
# #
# # teacher2.classes.append(classes1)
# # teacher2.classes.append(classes2)
# #
# # session.add(teacher1)
# # session.add(teacher2)
# # session.commit()

# 老师对应得班级
# teacher = session.query(Teacher).first()
# print(teacher)
# for i in teacher.classes:
#     print(i)

# 班级对应得老师
classes = session.query(Classes).first()
for i in classes.teachers:
    print(i)

要创建一个多对多得关系表，需要一个中间表，通过Table来创建一个中间表。上例中第一个参数teacher_classes代表得是中间表得表明，第二个参数是Base得元类，第三个参数和第四个参数就是要连接得两个表，其中Column第一个参数是表示得是连接表得外键名，第二个参数表示这个外键得类型，第三个参数表示要外键得表明和字段。