外键：
MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。
1、字段指定了外键，则字段值必须在外键字段值中。
2、作为外键的字段，值必须唯一，可以使用primary_key或unique约束键值唯一。
3、指定外键的字段和被指定外键的字段，字段类型必须相同或相似。
指定外键的方法：
在Column中添加ForeignKey('表名.字段名')
# 指定外键：from sqlalchemy import create_engin, Column, String, Integer, ForeignKeyfrom sqlalchemy.ext.declarative import declarative_baseengin = create_engin('mysql+pymysql://david:Yaotiao&shunv666@192.168.2.120/test',encoding='utf-8')Base = declarative_base()class Aroduct(Base): __tablename__ = 'aroducts' id = Column(Integer, primary_key=True) name = Column(String(32),unique=True) # 作为外键，使用unique保证唯一 price = Column(Integer) def __repr__(self): return "name:{},price:{}".format(self.name,self.price)class Broduct(Base): __tablename__ = 'broducts' id = Column(Integer, primary_key=True) name = Column(String(32), ForeignKey('aroducts.name')) # 添加外键Base.metadata.create_all(engine) # 创建两个表
向aroducts批量添加数据：
def add_data(product_name): product_list = [] for i in range(90,100): product = Aroduct(name = product_name+str(i),price = i) product_list.append(product) session.add_all(product_list)add_data('华为')# 添加后，查看aroducts数据库里的数据是：'''mysql> select * from aroducts;+----+----------+-------+| id | name | price |+----+----------+-------+| 1 | 华为90 | 90 || 2 | 华为91 | 91 || 3 | 华为92 | 92 || 4 | 华为93 | 93 || 5 | 华为94 | 94 || 6 | 华为95 | 95 || 7 | 华为96 | 96 || 8 | 华为97 | 97 || 9 | 华为98 | 98 || 10 | 华为99 | 99 |+----+----------+-------+''' 向broducts添加数据：需要导入一个sessionmaker
from sqlalchemy.orm import sessionmakerSession = sessionmaker(bind = engin)session =Session() # b1 = Broduct(name = 'huawei') # huawei不在上面表的name中，所以会报错b1 = Broduct(name = '华为91')session.add(b1)# 添加后查询结果mysql> select * from broducts;+----+----------+| id | name |+----+----------+| 1 | 华为91 |+----+----------+
relationship：
使用时，需要导入relationship模块
指定关联关系，实现两表之间字段互访。指定两表relationship时，两表之间必须存在ForeignKey关系。
relationship不会改变Mysql的结构和属性，只是在relationship中查询使用，所以即使在表生成后，也可以添加relationship.
只要存在ForeignKey关系的两个表，relationship可以加到任意表里。
格式： 字段1 = relationship('外部表class对象', secondary = '关联表名', backref='字段2')
说明：
字段1：本表通过“字段1”，查询外部表的字段
外部表Class对象：sqlalchemy映射的表类
secondary：多对多关系中，生成的三方表。
backref：反向查询字段名，相当于在外部表添加一个字段，外部表通过这个字段查询本表字段。
lazy： 1. 默认值为select, 他直接会导出所有的结果对象合成一个列表
2. dynamic，他会生成一个继承与Query的AppenderQuery对象，可以用于继续做过滤操作。 当需要对映射的结果集继续筛选的时候，可以在relationship指定lazy参数为'dynamic' 3. 其他的还有很多参数，例如joined，连接查询，但是涉及到查询性能
from sqlalchemy.orm import relationshipclass Broduct(Base): __tablename__ = 'broducts' id = Column(Integer, primary_key=True) name = Column(String(32), ForeignKey('aroducts.name')) # 添加外键 query_a = relationship('Aroduct', backref='query_b') # 在Broduct ORM对象里添加a 字段，定义relationship # 通过Broduct查询Aroduct中的price值b = session.query(Broduct).filter(Broduct.name=='华为91').first() # print(b.query_a.price) # 调用query_a，查询aroducts表里的 price的值'''# 显示结果91'''# 通过通过Aroduct查询Broduct中的idb = session.query(Aroduct).filter(Aroduct.name=='华为91').first() # 如果是all()，以列表显示，不能直接使用b.属性调用数据。print(b.query_b[0].id) # 因为使用ForeignKey的一方，系统默认为是多的一方，# 所以反向查询关联字段query_b时，得到的是一个List，所以查询值时要加上索引# 使用all()all_show = session.query(Aroduct).filter(Aroduct.name=='华为91').all() # 如果是all()，以列表显示，不能直接使用b.属性调用数据。print(all_show[0].query_b[0].id) '''1'''
sqlalchemy关系模型：
1、一对多
一个班级可以有很多学生
class Grade(Base): __tablename__ = 'grades' id = Column(Integer) name = Column(String(32)) identifier = Column(String(64),primary_key=True) def __repr__(self): return 'id:{},name:{},identifier:{}'.format(self.id, self.name,self.identifier) class User(Base): __table__ = 'users' id = Column(Integer) name = Column(String(32)) qq = Column(String(64),primary_key=True) grade_id = Column(String(64), ForeignKey('grades.identifier')) grade = relationship('Grade', backref = 'user') def __repr__(self): return 'id:{},name:{},qq:{},grade_id:{},grade:{}'.format(self.id,self.name,self.qq,self.grade_id)
2、一对多(多外键关联)
当一个表里多个字段关联同一个外键。
所有用户数据都放在teachers表里，一个班级（grades表）需要一个班主任，和一个讲师。
这样出现了当前grades表的不同字段，外键关联到teacher表的同一个字段。
class Grade(Base): __tablename__ = 'grades' id = Column(Integer) name = Column(String(32)) identifier = Column(String(64),primary_key=True) # class_teacher和lecturer指定同一个字段做为外键。 class_teacher = Column(String(64), ForeignKey('teachers.qq')) lecturer = Column(String(64),ForeignKey('teachers.qq')) # 正常使用relationship创建查询关系 # teacher = relationship('Teacher') # v_lecturer = relationship('Teacher') # 截止以上的语句，表结构可以创建完成 # 但是添加数据时会提示：多外键关联错误，参照本文后面的“错误提示-6” # 所以，如果要创建查询关系，给relationship加上参数，作用是指定每个查询对应的外键字段 teacher = relationship('Teacher', foreign_keys=[class_teacher]) v_lecurer = relationship('Teacher', foreign_keys=[lecturer]) # 这样再向teachers表里插数据就正常了 def __repr__(self): return 'id:{},name:{},identifier:{}'.format(self.id, self.name,self.identifier) class Teacher(Base): __table__ = 'teachers' id = Column(Integer) name = Column(String(32)) qq = Column(String(64),primary_key=True) # 设定为主键 def __repr__(self): return 'id:{},name:{},qq:{},grade_id:{},grade:{}'.format(self.id,self.name,self.qq)
3、多对多
现在系统要求升级了，不仅班级里有多个学生，一个学生可以报多个班级
# 多对多需要使用中间表，中间表需要导入Table类。# 使用ondelete='CASCADE'参数，可以避免删除有关联的项。from sqlalchemy import Table# 1、创建一个中间表，外键为grades表和user表的主键。grd_to_user = Table('grd_to_user', Base.metadata, Column('id',Integer,primary_key = True), Column('grd_identifier',String(64),ForeignKey('grades.identifier')) Column('user_qq',String(64),ForeignKey('users.qq')) )class Grade(Base): __tablename__ = 'grades' id = Column(Integer) name = Column(String(32)) identifier = Column(String(64),primary_key=True) # 创建关联关系，secondary='grd_to_user'指向中间表。 user = relationship('User',secondary='grd_to_user',backref='grd') class User(Base): __tablename__ = 'users' id = Column(Integer) name = Column(String(32)) qq = Column(String(64),primary_key=True) # 2、插入数据：# 中间表的数据会自动生成，不需要手动添加。# grades表和users表数据添加方法。grd1 = Grade(name='Python基础','Python-20181225')grd2 = Grade(name='Linux基础','Linux-20181225')grd3 = Grade(name='Docker基础','Docker-20181225')user1 = User(name='王大拿','112233')user2 = User(name='赵四','112244')user3 = User(name='刘能','112255')user4 = User(name='刘大脑袋','112266')user5 = User(name='谢广坤','112277')grd1.user = [user1,user2,user3]grd2.user = [user1,user2,user3,user4,user5]grd3.user = [user3,user4,user5]# 创建表结构Base.metadata.create_all(engin)# 把所有数据添加到数据库session.add_all([grd1,grd3,grd2,user1,user2,user3,user4,user5])# 确认提交保存session.commit()# 3、查询数据：# 查询'王大拿'报了哪几个班？search_user1 = session.query(User).filter(User.name == '王大拿').all()# 结果是对象列表，显示：for i in search_user1: # 从查询结果列表里读取对象名 for j in i.grd: # 利用relationship的backref反查字段，查询课程名 print(j.name) # 显示结果'''Linux基础 Python基础''' '''Mysql中查询方法：mysql> select grades.* from users inner join grd_to_user on users.qq = grd_to_user.user_qq inner join grades on grd_to_user.grd_identifier = grades.identifier and users.name='王大拿';+------+--------------+-----------------+| id | name | identifier |+------+--------------+-----------------+| NULL | Linux基础 | Linux-20181225 || NULL | Python基础 | Python-20181225 |+------+--------------+-----------------+2 rows in set (0.00 sec)'''
常见错误提示：
# 1、sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1822, "Failed to add the foreign key constraint. Missing index for constraint 'broducts_ibfk_1' in the referenced table 'aroducts'")
# 指定的外键不是另一个表里的primary_key或unique
# 2、sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'broducts.name' could not find table 'Products' with which to generate a foreign key to target column 'name'
# 外键表名错误
# 3、sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey 'aroducts.ssd' on table 'broducts': table 'aroducts' has no column named 'ssd'
# 外键的字段名错误
# 4、sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1215, 'Cannot add foreign key constraint')
# 外键与本字段数据类型不一致
# 也有可能数据库里面存在一名字一样的表，这个表的字段类型与新建表不一样
# 5、sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1216, 'Cannot add or update a child row: a foreign key constraint fails')
# 向表里存数据，因为有外键关联，但存的数据在外键中没有
# 6、sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'test.aroducts' doesn't exist")
# 存数据的时候提示，找不到表名，数据库里没有这张表
# 7、sqlalchemy.exc.InvalidRequestError: Table 'user' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object.
解决方法：在所有的models里面添加:__table_args__ = {"useexisting": True}或删除Base.matedata #8、sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, "Duplicate entry '192.168.2.120' for key 'ip'") [SQL: 'INSERT INTO hostinfo (ip, hostname, port) VALUES (%(ip)s, %(hostname)s, %(port)s)'] [parameters: {'ip': '192.168.2.120', 'hostname': 'bbshost1', 'port': 8800}] (Background on this error at:
原因是有重复输入的IP了，这句是主要的。1062, "Duplicate entry '192.168.2.120' for key 'ip'"
查看表结构定义的是不是有问题，unique和primarykey字段的值都是唯一的，我是因为ip和port字段做联合唯一，写错了。
应该在__table_args__里定义联合唯一。
需要注意的是，如果已在mysql里创建表了，那么，不仅要改变sqlalchemy table的字段定义，还必须要改变mysql表的字段定义。或者重新创建表。
参考文章：
多对多：