• 【python】-- pymsql 外键


     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. 一本书可以有好几个作者一起出版
    2. 一个作者可以写好几本书

    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)

      

  • 相关阅读:
    Elasticsearch倒排索引结构
    Redis(二)冰叔带你了解Redis-哨兵模式和高可用集群解析
    jenkins构建配置
    jenkins配置email
    jenkins配置maven
    jenkins配置java
    jenkins配置git
    centos安装jenkins
    centos安装tomcat
    centos安装jdk
  • 原文地址:https://www.cnblogs.com/Keep-Ambition/p/8094151.html
Copyright © 2020-2023  润新知