pymsql 外键
本片是以上一篇pymsql操作MySQL的补充,主要演示pymysql的外键操作使用
一、一对一外键关联
1、示意图
2、一对一外键关联示例
2.1、创建表结构,插入数据
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, DATE, ForeignKey from sqlalchemy.orm import relationship, sessionmaker connect = create_engine("mysql+pymysql://root:123456@localhost:3306/test", encoding="utf-8", echo=False) # 连接数据库,echo=True =>把所有的信息都打印出来 Base = declarative_base() # 生成ORM基类 class Student(Base): # 学生表 __tablename__ = "student" id = Column(Integer, primary_key=True) name = Column(String(32), nullable=False) register_date = Column(DATE, nullable=False) def __repr__(self): return "<{0} name:{1}>".format(self.id, self.name) class StudentRecord(Base): # 学生学习记录表 __tablename__ = "study_record" id = Column(Integer, primary_key=True) day = Column(Integer, nullable=False) status = Column(String(32), nullable=False) stu_id = Column(Integer, ForeignKey("student.id")) # 关联外键 #关联student表,然后我需要在study_record里通过student这个字段,就可以去查Student类里面所有的字段, # 反过来利用backref="my_study_record"中的my_study_record,在student表里通过my_study_record这个字段反查study_record类里面的所有字段, Student = relationship("Student", backref="my_study_record") def __repr__(self): return "<name:{0} day:{1} stu_id:{2}>".format(self.Student.name, self.day, self.stu_id) Base.metadata.create_all(connect) # 创建学生表和学生记录表,在第一次创建后注释 session_class = sessionmaker(connect) # 创建与数据库的会话session class ,这里返回给session的是个class,不是实例 session = session_class() # 生成session实例 #在两个表中插入数据 stu1 = Student(name="test", register_date="2017-05-30") stu2 = Student(name="test2", register_date="2017-06-30") record1 = StudentRecord(day=1, status="Y", stu_id=1) record2 = StudentRecord(day=2, status="Y", stu_id=1) session.add_all([record1, record2]) session.commit()
2.2、根据关联外键,查对应表中信息
stu_obj = session.query(Student).filter_by(name="test").first() print(stu_obj.my_study_record) # 在学生表里查学生对应的学习记录 record_obj = session.query(StudentRecord).first() print(record_obj.Student.name) # 在学生记录表里面查学习记录对应学生的名字 #输出 [<name:test day:1 stu_id:1>, <name:test day:2 stu_id:1>] test
二、一对多外键关联
案例一创建的是:一对一的外键关系,现在是一对多的外键关系
1、一对多外键关联示例:
1.1、创建表结构
from sqlalchemy import Integer, ForeignKey, String, Column from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship from sqlalchemy import create_engine Base = declarative_base() # orm基类 class Customer(Base): # 消费者表 __tablename__ = "customer" id = Column(Integer, primary_key=True) name = Column(String(64)) #创建两个外键,都指向address.id billing_address_id = Column(Integer, ForeignKey("address.id")) shipping_address_id = Column(Integer, ForeignKey("address.id")) #因为有两个外键,需要通过foreign_keys=[外键字段]告诉sqlalchemy关联哪个外键了,不然就会报错 billing_address = relationship("Address", foreign_keys=[billing_address_id]) shipping_address = relationship("Address", foreign_keys=[shipping_address_id]) class Address(Base): # 收货地址表 __tablename__ = "address" id = Column(Integer, primary_key=True) street = Column(String(64)) city = Column(String(64)) state = Column(String(64)) def __repr__(self): return self.state + "-" + self.city + "-" + self.street connect = create_engine("mysql+pymysql://root:123456@localhost:3306/test", encoding="utf-8", echo=False) # 连接数据库,echo=True =>把所有的信息都打印出来 Base.metadata.create_all(connect) # 创建所有的表
1.2、插入数据(默认规则创建数据表结构在一个.py文件,增删查改等操作不与表结构.py文件在一起)
import many_foreign_key # 导出表结构创建模块 from sqlalchemy.orm import sessionmaker #创建session与数据库会话类,生成session实例 session_class = sessionmaker(bind=many_foreign_key.connect) session = session_class() #创建address address1 = many_foreign_key.Address(street="nanshanqu", city="shenzhen", state="guangdong") address2 = many_foreign_key.Address(street="baoanqu", city="shenzhen", state="guangdong") #创建consumers c1 = many_foreign_key.Customer(name="test", billing_address=address1, shipping_address=address1) c2 = many_foreign_key.Customer(name="test1", billing_address=address2, shipping_address=address1) #添加session提交 session.add_all([address1, address2, c1, c2]) session.commit()
1.3、查询数据
import many_foreign_key from sqlalchemy.orm import sessionmaker obj = session.query(many_foreign_key.Customer).filter_by(name="test1").first() print(obj.name, obj.billing_address, obj.shipping_address) #输出 test1 guangdong-shenzhen-baoanqu guangdong-shenzhen-nanshanqu
三、多对多外键关联
通过数据库模拟设计“图书”与“作者”的关系的表结构,从而来演示多对多外键关联:
- 一本书可以有好几个作者一起出版
- 一个作者可以写好几本书
1、多对多外键关联示例:
1.1、创建表结构
from sqlalchemy import Table, Column, Integer, String, DATE, ForeignKey from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine Base = declarative_base() # 创建orm基类 # 创建一张中间表,用于对book表和author表之前的多对多关联, 中间表建立后由ORM自动维护 book_m2m_author = Table("book_m2m_author", Base.metadata, Column("id", Integer, primary_key=True), Column('books_id', Integer, ForeignKey("books.id")), Column('authors_id', Integer, ForeignKey("authors.id"))) class Book(Base): __tablename__ = "books" id = Column(Integer, primary_key=True) name = Column(String(64)) pub_date = Column(DATE) #关联authors表和中间表book_m2m_author,即可通过books反查出Author表中的数据或者#通过authors反查出books表中的数据 authors = relationship("Author", secondary=book_m2m_author, backref="books") def __repr__(self): return self.name class Author(Base): __tablename__ = "authors" id = Column(Integer,primary_key=True) name = Column(String(32)) def __repr__(self): return self.name connect = create_engine("mysql+pymysql://root:123456@localhost:3306/test?charset=utf8", echo=False) # 连接数据库,echo=True =>把所有的信息都打印出来 Base.metadata.create_all(connect) # 创建所有表
数据关联示意图:
1.2、插入数据
import m2m_foreign_key from sqlalchemy.orm import sessionmaker #创建session与数据库会话类,生成session实例 session_class = sessionmaker(bind=m2m_foreign_key.connect) session = session_class() #创建book信息 b1 = m2m_foreign_key.Book(name="Python", pub_date="2017-08-08") b2 = m2m_foreign_key.Book(name="JAVA", pub_date="2017-10-08") b3 = m2m_foreign_key.Book(name="C", pub_date="2017-11-08") #创建author信息 a1 = m2m_foreign_key.Author(name="test") a2 = m2m_foreign_key.Author(name="test1") a3 = m2m_foreign_key.Author(name="test2") #创建中间表信息 b1.authors = [a1, a3] b2.authors = [a1, a2, a3] session.add_all([b1, b2, b3, a1, a2, a3]) session.commit()
1.3、查询数据
import m2m_foreign_key from sqlalchemy.orm import sessionmaker #创建session与数据库会话类,生成session实例 session_class = sessionmaker(bind=m2m_foreign_key.connect) session = session_class() authors_obj = session.query(m2m_foreign_key.Author).filter_by(name="test").first() print(authors_obj.books) # 通过books反查出books表中的数据 book_obj = session.query(m2m_foreign_key.Book).filter(m2m_foreign_key.Book.id == 2).first() print(book_obj.authors) # 通过authors反查出authors表中的数据 #输出 [Python, JAVA] [test, test1, test2]
1.4、删除数据
删除数据时不用管book_m2m_authors , sqlalchemy会自动帮你把对应的数据删除
import m2m_foreign_key from sqlalchemy.orm import sessionmaker #创建session与数据库会话类,生成session实例 session_class = sessionmaker(bind=m2m_foreign_key.connect) session = session_class() #通过书删除作者 author_obj = session.query(m2m_foreign_key.Author).filter_by(name="test1").first() book_obj = session.query(m2m_foreign_key.Book).filter_by(name="JAVA").first() book_obj.authors.remove(author_obj) # 从一本书里删除一个作者 session.commit() #删除作者,会把这个作者跟所有书的关联关系数据也自动删除 author_obj =session.query(m2m_foreign_key.Author).filter_by(name="test").first() # print(author_obj.name , author_obj.books) s.delete(author_obj) s.commit()
注:处理中文
sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8,否则数据库的连接就不是utf8的编码格式
eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)