python学习之路-13

SQLAlchemy ORM框架

连表操作

  • 一对多

    • 创建表 指定约束 ForeignKey
    
    from sqlalchemy import create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
    from sqlalchemy.orm import sessionmaker, relationship
    
    engine = create_engine("mysql+pymysql://tuocigaoshou:Eb^BEF38E9FBC36CA775@111.204.117.99:3306/test", max_overflow=5)
    
    Base = declarative_base()
    
    # 一对多
    class Group(Base):
        __tablename__ = "group"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        caption = Column          # 组名
    
    
    class User(Base):
        __tablename__ = "user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        username = Column(String(32))
        group_id = Column(Integer, ForeignKey("group.nid"))  # 外键
    
    
    Base.metadata.create_all(engine)
    
    • 插入数据
    from sqlalchemy import create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
    from sqlalchemy.orm import sessionmaker, relationship
    
    engine = create_engine("mysql+pymysql://tuocigaoshou:Eb^BEF38E9FBC36CA775@111.204.117.99:3306/test", max_overflow=5)
    
    Base = declarative_base()
    
    
    # 单表
    class Test(Base):
        __tablename__ = "test"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        name = Column(String(32))
    
    
    # 一对多
    class Group(Base):
        __tablename__ = "group"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        caption = Column(String(32))          # 组名
    
    
    class User(Base):
        __tablename__ = "user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        username = Column(String(32))
        group_id = Column(Integer, ForeignKey("group.nid"))  # 外键
    
    
    # Base.metadata.create_all(engine)    # 创建表
    # Base.metadata.drop_all(engine)    # 删除表
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    ### 添加数据
    # 向表中添加单条数据
    session.add(Group("dba"))
    session.add(Group("ddd"))
    
    # 向表中添加多条数据
    session.add_all(
        [
            User(username="a", group_id=1),
            User(username="b", group_id=2),
            User(username="c", group_id=1)
        ]
    )
    
    session.commit()
    
    • 查询数据
    
    from sqlalchemy import create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
    from sqlalchemy.orm import sessionmaker, relationship
    
    engine = create_engine("mysql+pymysql://tuocigaoshou:Eb^BEF38E9FBC36CA775@111.204.117.99:3306/test", max_overflow=5)
    
    Base = declarative_base()
    
    
    ### 创建表结构
    # 单表
    class Test(Base):
        __tablename__ = "test"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        name = Column(String(32))
    
    
    # 一对多
    class Group(Base):
        __tablename__ = "group"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        caption = Column(String(32))          # 组名
    
    
    class User(Base):
        __tablename__ = "user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        username = Column(String(32))
        group_id = Column(Integer, ForeignKey("group.nid"))  # 外键
    
        # 与生成表结构无关,仅用于更方便的进行连表查询
        group = relationship("Group", backref="user")
    
        def __repr__(self):
            temp = "%s - %s - %s" % (self.nid, self.username, self.group_id)
            return temp
    
    
    # Base.metadata.create_all(engine)    # 创建表
    # Base.metadata.drop_all(engine)    # 删除表
    
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    """
    ### 添加数据
    # 向表中添加单条数据
    session.add(Group("dba"))
    session.add(Group("ddd"))
    
    # 向表中添加多条数据
    session.add_all(
        [
            User(username="a", group_id=1),
            User(username="b", group_id=2),
            User(username="c", group_id=1)
        ]
    )
    
    session.commit()
    """
    
    
    # # 查询单表中所有数据
    # ret = session.query(User).all()
    # print(ret)  # 输出User类中__repr__方法中返回的字符串
    # for obj in ret:
    #     print(obj.nid, obj.username, obj.group_id)
    #
    # # 通过指定列查询
    # ret = session.query(User.nid, User.username).all()
    # print(ret)  # 返回一个列表,列表中包含每一行的数据  [(2, 'a'), (3, 'b'), (4, 'c')]
    
    # # 根据指定条件查询
    # ret = session.query(User).filter(User.username == "a").all()
    # obj = ret[0]
    # print(obj.nid, obj.username, obj.group_id)
    
    # 连表查询
    
    # 通过join进行连表查询
    # ret = session.query(User).join(Group)   # 返回执行的sql语句
    # print(ret)
    # print("------")
    # ret = session.query(User).join(Group).all()
    # for obj in ret:
    #     print(obj.nid, obj.username, obj.group_id)
    #
    # ret = session.query(User, Group).join(Group).all()
    # print(ret)
    #
    #
    # ret = session.query(User.username, Group.caption).join(Group).all()
    # print(ret)
    
    # 通过relationship在创建表的类中指定连表的对应关系,然后进行连表查询
    # 正向查询  (正向查找不能够过滤组的字段)
    ret = session.query(User).all()
    for obj in ret:
        # obj代指user表中的每一行数据
        # obj.group代指obj当前对应的group表中数据的对象
        print(obj.nid, obj.username, obj.group_id, obj.group, obj.group.nid, obj.group.caption)
    
    
    # 反向查询
    # 查找所有组名为dba的用户信息
    obj = session.query(Group).filter(Group.caption=="dba").first()
    print(obj.nid, obj.caption, obj.user)    # obj.user 这个地方的user是在User类中使用relationship创建对应关系中 backref对应的值
    
  • 多对多

    • 原始方法和新式方法1
    ### 关系在第三张表中
    
    from sqlalchemy import create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
    from sqlalchemy.orm import sessionmaker, relationship
    
    engine = create_engine("mysql+pymysql://tuocigaoshou:Eb^BEF38E9FBC36CA775@111.204.117.99:3306/test", max_overflow=5)
    
    Base = declarative_base()
    
    
    class Host(Base):
        """
        主机表
        """
        __tablename__ = "host"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        hostname = Column(String(32))
        port = Column(String(32))
        ip = Column(String(32))
    
    
    class HostUser(Base):
        """
        登录主机的用户表
        """
        __tablename__ = "host_user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        username = Column(String(32))
    
    
    class Host_HostUser(Base):
        """
        关系表
        """
        __tablename__ = "host_to_host_user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        host_id = Column(Integer, ForeignKey("host.nid"))
        host_user_id = Column(Integer, ForeignKey("host_user.nid"))
    
        host = relationship("Host", backref="h")
        host_user = relationship("HostUser", backref="u")
    
    # Base.metadata.create_all(engine)    # 创建表
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    # # 向host主机表中添加多条数据
    # session.add_all(
    #     [
    #         Host(hostname="c1", port="22", ip="1.1.1.1"),
    #         Host(hostname="c2", port="22", ip="1.1.1.2"),
    #         Host(hostname="c3", port="22", ip="1.1.1.3"),
    #     ]
    # )
    
    # # 向host_user登录主机用户表中添加多条数据
    # session.add_all(
    #     [
    #         HostUser(username="root"),
    #         HostUser(username="aa"),
    #         HostUser(username="bb"),
    #         HostUser(username="cc"),
    #     ]
    # )
    
    
    # # 向host_to_host_user关系表中添加多条数据
    # session.add_all(
    #     [
    #         Host_HostUser(host_id=1, host_user_id=1),
    #         Host_HostUser(host_id=1, host_user_id=2),
    #         Host_HostUser(host_id=1, host_user_id=3),
    #         Host_HostUser(host_id=1, host_user_id=4),
    #         Host_HostUser(host_id=2, host_user_id=2),
    #         Host_HostUser(host_id=2, host_user_id=4),
    #         Host_HostUser(host_id=3, host_user_id=1),
    #         Host_HostUser(host_id=3, host_user_id=3),
    #     ]
    # )
    
    
    # session.commit()
    
    
    # 需求:找出能够登录主机名为c1的所有用户
    # ### 原始方式
    # # 从host表中将找到主机名为c1的nid
    # host_obj = session.query(Host).filter(Host.hostname == "c1").first()
    #
    # # 通过主机名为c1的nid在关系表中找到用户的id
    # host_2_host_user = session.query(Host_HostUser.host_user_id).filter(Host_HostUser.host_id==host_obj.nid).all()
    #
    # r = zip(*host_2_host_user)
    # users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
    # print(users)
    
    ### 原始方式结合
    ret = session.query(HostUser.username).filter(HostUser.nid.in_(session.query(Host_HostUser.host_user_id).filter(Host_HostUser.host_id == session.query(Host.nid).filter(Host.hostname == 'c1')))).all()
    print(ret)
    
    ### 新式方法
    # 通过在Host_HostUser类中使用relationship建立与Host类和HostUser类建立关系,然后使用反向查询和正向询进行查找
    host_obj = session.query(Host).filter(Host.hostname=="c1").first()
    for item in host_obj.h:   # host_obj.h 反向查询
        print(item.host_user.username)  # item.host_user.username  正向查询
    
    • 新式方法2
    ### 关系在第1张表中 secondary=Host_HostUser.__table__ 要将关系表放在最上面 如果不使用lambda则应该将关系表放在最上面
    
    from sqlalchemy import create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
    from sqlalchemy.orm import sessionmaker, relationship
    
    engine = create_engine("mysql+pymysql://tuocigaoshou:Eb^BEF38E9FBC36CA775@111.204.117.99:3306/test", max_overflow=5)
    
    Base = declarative_base()
    
    
    class Host_HostUser(Base):
        """
        关系表
        """
        __tablename__ = "host_to_host_user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        host_id = Column(Integer, ForeignKey("host.nid"))
        host_user_id = Column(Integer, ForeignKey("host_user.nid"))
    
    
    class Host(Base):
        """
        主机表
        """
        __tablename__ = "host"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        hostname = Column(String(32))
        port = Column(String(32))
        ip = Column(String(32))
    
        host_user = relationship("HostUser",
                                 secondary=Host_HostUser.__table__,
                                 backref="h")
    
    
    class HostUser(Base):
        """
        登录主机的用户表
        """
        __tablename__ = "host_user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        username = Column(String(32))
    
    
    # Base.metadata.create_all(engine)    # 创建表
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    # # 向host主机表中添加多条数据
    # session.add_all(
    #     [
    #         Host(hostname="c1", port="22", ip="1.1.1.1"),
    #         Host(hostname="c2", port="22", ip="1.1.1.2"),
    #         Host(hostname="c3", port="22", ip="1.1.1.3"),
    #     ]
    # )
    
    # # 向host_user登录主机用户表中添加多条数据
    # session.add_all(
    #     [
    #         HostUser(username="root"),
    #         HostUser(username="aa"),
    #         HostUser(username="bb"),
    #         HostUser(username="cc"),
    #     ]
    # )
    
    
    # # 向host_to_host_user关系表中添加多条数据
    # session.add_all(
    #     [
    #         Host_HostUser(host_id=1, host_user_id=1),
    #         Host_HostUser(host_id=1, host_user_id=2),
    #         Host_HostUser(host_id=1, host_user_id=3),
    #         Host_HostUser(host_id=1, host_user_id=4),
    #         Host_HostUser(host_id=2, host_user_id=2),
    #         Host_HostUser(host_id=2, host_user_id=4),
    #         Host_HostUser(host_id=3, host_user_id=1),
    #         Host_HostUser(host_id=3, host_user_id=3),
    #     ]
    # )
    
    # session.commit()
    
    # 需求:找出能够登录主机名为c1的所有用户
    ### 新式方法二
    
    host_obj = session.query(Host).filter(Host.hostname=="c1").first()
    print(host_obj.host_user)
    
    • 新式方法三
    ### secondary="host_to_host_user" 关系表无顺序要求 如果不使用lambda则应该将关系表放在最上面
    
    from sqlalchemy import create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
    from sqlalchemy.orm import sessionmaker, relationship
    
    engine = create_engine("mysql+pymysql://tuocigaoshou:Eb^BEF38E9FBC36CA775@111.204.117.99:3306/test", max_overflow=5)
    
    Base = declarative_base()
    
    
    class Host(Base):
        """
        主机表
        """
        __tablename__ = "host"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        hostname = Column(String(32))
        port = Column(String(32))
        ip = Column(String(32))
    
        host_user = relationship("HostUser",
                                 secondary="host_to_host_user",
                                 backref="h")
    
    
    class HostUser(Base):
        """
        登录主机的用户表
        """
        __tablename__ = "host_user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        username = Column(String(32))
    
    
    class Host_HostUser(Base):
        """
        关系表
        """
        __tablename__ = "host_to_host_user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        host_id = Column(Integer, ForeignKey("host.nid"))
        host_user_id = Column(Integer, ForeignKey("host_user.nid"))
    
    
    # Base.metadata.create_all(engine)    # 创建表
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    ### 新式方法三
    
    host_obj = session.query(Host).filter(Host.hostname=="c1").first()
    print(host_obj.host_user)
    
    • 新式方法四
    ### secondary=lambda : Host_HostUser.__table__  如果不使用lambda则应该将关系表放在最上面
    	
    from sqlalchemy import create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
    from sqlalchemy.orm import sessionmaker, relationship
    
    engine = create_engine("mysql+pymysql://tuocigaoshou:Eb^BEF38E9FBC36CA775@111.204.117.99:3306/test", max_overflow=5)
    
    Base = declarative_base()
    
    
    class Host(Base):
        """
        主机表
        """
        __tablename__ = "host"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        hostname = Column(String(32))
        port = Column(String(32))
        ip = Column(String(32))
    
        host_user = relationship("HostUser",
                                 secondary=lambda : Host_HostUser.__table__,
                                 backref="h")
    
    
    class HostUser(Base):
        """
        登录主机的用户表
        """
        __tablename__ = "host_user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        username = Column(String(32))
    
    
    class Host_HostUser(Base):
        """
        关系表
        """
        __tablename__ = "host_to_host_user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        host_id = Column(Integer, ForeignKey("host.nid"))
        host_user_id = Column(Integer, ForeignKey("host_user.nid"))
    
    
    # Base.metadata.create_all(engine)    # 创建表
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    ### 新式方法四
    
    host_obj = session.query(Host).filter(Host.hostname=="c1").first()
    print(host_obj.host_user)
    
    • 新式方法五
    ### 关系表需要使用Table()进行创建
    ### relationship 中secondary=Host_HostUser, 如果不使用lambda则应该将关系表放在最上面
    
    from sqlalchemy import create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, Table
    from sqlalchemy.orm import sessionmaker, relationship
    
    engine = create_engine("mysql+pymysql://tuocigaoshou:Eb^BEF38E9FBC36CA775@111.204.117.99:3306/test", max_overflow=5)
    
    Base = declarative_base()
    
    
    Host_HostUser = Table('host_to_host_user', Base.metadata,
                  Column('nid', Integer, primary_key=True, autoincrement=True),
                  Column('host_id', Integer, ForeignKey("host.nid")),
                  Column('host_user_id', Integer, ForeignKey("host_user.nid")),
    )
    
    
    
    class Host(Base):
        """
        主机表
        """
        __tablename__ = "host"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        hostname = Column(String(32))
        port = Column(String(32))
        ip = Column(String(32))
    
        host_user = relationship("HostUser",
                                 secondary=Host_HostUser,
                                 backref="h")
    
    
    class HostUser(Base):
        """
        登录主机的用户表
        """
        __tablename__ = "host_user"
        nid = Column(Integer, primary_key=True, autoincrement=True)
        username = Column(String(32))
    
    
    # Base.metadata.create_all(engine)    # 创建表
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    ### 新式方法五
    
    host_obj = session.query(Host).filter(Host.hostname=="c1").first()
    print(host_obj.host_user)
    

paramiko模块

posted on 2016-08-06 11:21  奋斗中的码农  阅读(256)  评论(0编辑  收藏  举报

导航