外键：

    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://blog.csdn.net/qq_42606051/article/details/81143935

https://www.jb51.net/article/49789.htm

https://segmentfault.com/a/1190000004618621

https://www.cnblogs.com/alex3714/articles/5978329.html

 

http://www.mamicode.com/info-detail-1478263.html