sqlalchemy 链表操作---->多对多

  1. 云栖社区>
  2. 博客>
  3. 正文

sqlalchemy 链表操作---->多对多

科技小能手 2017-11-12 14:45:00 浏览478
展开阅读全文

多对多表结构创建


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://root:root@192.168.100.201:3306/web", max_overflow=5)

Base = declarative_base(engine)

Session = sessionmaker(bind=engine)

dbSession = Session()


# 多对多

class HostToHostUser(Base):

    __tablename__ = 'host_to_hostuser'

    nid = Column(Integer, primary_key=True, autoincrement=True)

    host_id = Column(Integer, ForeignKey('host.nid'))

    hostuser_id = Column(Integer, ForeignKey('hostuser.nid'))


    # 多对多操作

    host = relationship('Host', backref='h')

    hostuser = relationship('HostUser', backref='hu')


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=HostToHostUser.__table__, backref='h')


class HostUser(Base):

    __tablename__ = 'hostuser'

    nid = Column(Integer, primary_key=True, autoincrement=True)

    username = Column(String(32))



def init_db():

    Base.metadata.create_all(engine)


def drop_db():

    Base.metadata.drop_all(engine)


向表内插入数据

# ##################################################################

# dbSession.add_all([

#     Host(hostname='lala1', port='22', ip='1.1.1.1'),

#     Host(hostname='lala2', port='22', ip='1.1.1.2'),

#     Host(hostname='lala3', port='22', ip='1.1.1.3'),

#     HostToHostUser(host_id=1, hostuser_id=1),

#     HostToHostUser(host_id=1, hostuser_id=2),

#     HostToHostUser(host_id=1, hostuser_id=3),


# ])

# dbSession.commit()

##################################################################

查询数据

# 首先查询hostname 为 lala1 的 host

host_obj = dbSession.query(Host).filter(Host.hostname=='lala1').first()

print host_obj

# 通过反向查询,查到hostuser 的对象

for item in host_obj.h:

    print (item.hostuser.username)


最后总结:

多对多表结构,只需在主表上创建到子表的虚拟关系:

host_user = relationship('HostUser', secondary=HostToHostUser.__table__, backref='h')

然后在中间表中,关联两张表的外键,再通过relationship + backref参数创建虚拟关系,实现正反向查询




本文转自 菜鸟的征程 51CTO博客,原文链接:http://blog.51cto.com/songqinglong/1956840

网友评论

登录后评论
0/500
评论
科技小能手
+ 关注