#！/usr/bin/env python# -*- coding: utf-8 -*-# Author: wanghuafengfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:123456@192.168.100.188:3306/s13", 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"))    # 只用于查询    #uuu代表虚拟列：[1 wang, 3 xiaoming, 4 xiaoxiao]    #relationship与ForeignKey一般在一起    group = relationship("Group", backref=‘uuu‘)    #只是对print对象的时候有用    def __repr__(self):        #return "<nid=%s, username=%s, group_id=%s>" % (self.nid, self.username, self.group_id)        temp = "%s - %s - %s" % (self.nid, self.username, self.group_id)        return temp#创建表def init_db():    Base.metadata.create_all(engine)def drop_db():    Base.metadata.drop_all(engine)init_db()#创建组Session = sessionmaker(bind=engine)session = Session()session.add(Group(caption = ‘dba‘))session.add(Group(caption = ‘dbd‘))session.commit()#只获取用户ret = session.query(User).filter(User.username==‘wang‘).all()print(ret)ret = session.query(User).all()obj = ret[0]print(obj)print(obj.nid)print(obj.username)print(obj.group_id)ret = session.query(User.username).all()print(ret)#左连接isouter=True#同时取两个表session.query(User, Group)sql = session.query(User, Group).join(Group, isouter=True)sql = session.query(User.username, Group.caption).join(Group, isouter=True)print(sql)ret = session.query(User.username, Group.caption).join(Group, isouter=True).all()#select * from user left join group on user.group_id = group.nidprint(ret)#新方式（正向查询）:relationship在这个表里并查询该表的数据ret = session.query(User).all()for obj in ret:    # obj.group：obj代表user表的每一行数据    # obj.group为group对象    print(obj.nid, obj.username, obj.group_id, obj.group, obj.group.nid, obj.group.caption)#列出组中的所有人# ret = session.query(User.username, Group.caption).join(Group, isouter=True).filter(Group.caption == ‘dba‘).all()# print(ret)#新方式（反向查询）：relationship不在这个表里并查询其他表的数据obj = session.query(Group).filter(Group.caption == ‘dba‘).first()print(obj.nid, obj.caption)print(obj.uuu)

#！/usr/bin/env python# -*- coding: utf-8 -*-# Author: wanghuafengfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:123456@192.168.100.188:3306/s13", 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 HostToHostUser(Base):    __tablename__ = ‘host_to_hostuser‘    #增加nid，方便以后删除    nid = Column(Integer, primary_key=True, autoincrement=True)    host_id = Column(Integer, ForeignKey(‘host.nid‘))    host_user_id = Column(Integer, ForeignKey(‘host_user.nid‘))#创建表def init_db():    Base.metadata.create_all(engine)def drop_db():    Base.metadata.drop_all(engine)init_db()Session = sessionmaker(bind=engine)session = Session()#循环插入数据session.add_all([    Host(hostname=‘c1‘, port=‘22‘, ip=‘1.2.1.2‘),    Host(hostname=‘c2‘, port=‘22‘, ip=‘1.2.1.3‘),    Host(hostname=‘c3‘, port=‘22‘, ip=‘1.2.1.1‘),    Host(hostname=‘c4‘, port=‘22‘, ip=‘1.2.1.4‘),    Host(hostname=‘c5‘, port=‘22‘, ip=‘1.2.1.5‘),])session.commit()session.add_all([    HostUser(username=‘root‘),    HostUser(username=‘mysql‘),    HostUser(username=‘svn‘),    HostUser(username=‘git‘),    HostUser(username=‘oracle‘),])session.commit()session.add_all([    HostToHostUser(host_id=‘1‘, host_user_id=1),    HostToHostUser(host_id=‘1‘, host_user_id=2),    HostToHostUser(host_id=‘1‘, host_user_id=3),    HostToHostUser(host_id=‘2‘, host_user_id=4),    HostToHostUser(host_id=‘2‘, host_user_id=5),    HostToHostUser(host_id=‘2‘, host_user_id=1),    HostToHostUser(host_id=‘3‘, host_user_id=1),    HostToHostUser(host_id=‘3‘, host_user_id=2),    HostToHostUser(host_id=‘3‘, host_user_id=3),    HostToHostUser(host_id=‘4‘, host_user_id=4),    HostToHostUser(host_id=‘4‘, host_user_id=5),    HostToHostUser(host_id=‘4‘, host_user_id=1),    HostToHostUser(host_id=‘5‘, host_user_id=4),    HostToHostUser(host_id=‘5‘, host_user_id=5),    HostToHostUser(host_id=‘5‘, host_user_id=1),])session.commit()#多对多操作数据#获取主机1中的所有用户host_obj = session.query(Host).filter(Host.hostname == ‘c1‘).first()#host_obj.nid(找到主机id)host_2_host_user = session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id == host_obj.nid).all()print(host_2_host_user)#[(1,), (2,), (3,)]r = zip(*host_2_host_user)#print(list(r)[0])#[1, 2, 3]users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()print(users)

#！/usr/bin/env python# -*- coding: utf-8 -*-# Author: wanghuafengfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:123456@192.168.100.188:3306/s13", 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 HostToHostUser(Base):    __tablename__ = ‘host_to_hostuser‘    #增加nid，方便以后删除    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‘)Session = sessionmaker(bind=engine)session = Session()#获取主机1中的所有用户#主机host_obj = session.query(Host).filter(Host.hostname==‘c1‘).first()#host_to_hostuser表中的对象#print(host_obj.h)for item in host_obj.h:    print(item.host_user, item.host_user.nid, item.host_user.username)