外键:
MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。
1、字段指定了外键,则字段值必须在外键字段值中。
2、作为外键的字段,值必须唯一,可以使用primary_key或unique约束键值唯一。
3、指定外键的字段和被指定外键的字段,字段类型必须相同或相似。
指定外键的方法:
在Column中添加ForeignKey('表名.字段名')
# 指定外键:
from sqlalchemy import create_engin, Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
engin = 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 sessionmaker
Session = 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 relationship
class 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中的id
b = 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: http://sqlalche.me/e/gkpj)
原因是有重复输入的IP了,这句是主要的。1062, "Duplicate entry '192.168.2.120' for key 'ip'"
查看表结构定义的是不是有问题,unique和primarykey字段的值都是唯一的,我是因为ip和port字段做联合唯一,写错了。
应该在__table_args__里定义联合唯一。
需要注意的是,如果已在mysql里创建表了,那么,不仅要改变sqlalchemy table的字段定义,还必须要改变mysql表的字段定义。或者重新创建表。
参考文章:
多对多:
https://www.jb51.net/article/49789.htm
http://www.mamicode.com/info-detail-1478263.html
转载于:https://blog.51cto.com/yishi/2335554