继续上一篇SQLAlchemy的学习之旅。
多对多表的创建
表Host和表HostUser通过表HostToHostUser关联在一起
代码语言：javascript复制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://yli:yli@sydnagios:3306/mydb", max_overflow=5)
Base = declarative_base()
class HostToHostUser(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): # metaclass,Host.table对象
__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, backref='h')
host_user = relationship('HostUser', secondary=HostToHostUser.__table__, backref='h')
class HostUser(Base):
__tablename__ = 'host_user'
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)
init_db()
Session = sessionmaker(bind=engine)
session = Session()
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(hostname='c4',port='22',ip='1.1.1.4'),
Host(hostname='c5',port='22',ip='1.1.1.5'),
])
session.commit()
session.add_all([
HostUser(username='root'),
HostUser(username='db'),
HostUser(username='nb'),
HostUser(username='sb'),
])
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=2),
HostToHostUser(host_id=2,host_user_id=4),
HostToHostUser(host_id=2,host_user_id=3),
])
session.commit()结果如下
例1. 获取主机1的所有用户,原理和1对多的一样，通过relationship快速定位到对应的表
代码语言：javascript复制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://yli:yli@sydnagios:3306/mydb", max_overflow=5)
Base = declarative_base()
class HostToHostUser(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')
class Host(Base): # metaclass,Host.table对象
__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))
#def init_db():
# Base.metadata.create_all(engine)
#
# def drop_db():
# Base.metadata.drop_all(engine)
#init_db()
Session = sessionmaker(bind=engine)
session = Session()
host_obj = session.query(Host).filter(Host.hostname=='c1').first()
print(host_obj.nid)
print(host_obj.hostname)
# 第三表对应的对象（逆向查询）
print(host_obj.h)
# 循环获取的第三表对应的对象
for item in host_obj.h:
print(item.host_user,item.host_user.nid,item.host_user.username)
--------------
"C:Program FilesPython3python.exe" "C:/Users/yli/Documents/Tencent Files/38144205/FileRecv/s13课上代码/s13课上代码/s13day13课上代码/s13day13_课上代码/ORM—de,p.py"
1
c1
[<__main__.HostToHostUser object at 0x000002678C0A3CC0>, <__main__.HostToHostUser object at 0x000002678C0B53C8>, <__main__.HostToHostUser object at 0x000002678C0B5438>]
<__main__.HostUser object at 0x000002678C0B5748> 1 root
<__main__.HostUser object at 0x000002678C0B5908> 2 db
<__main__.HostUser object at 0x000002678C0B5AC8> 3 nb
Process finished with exit code 0例2，另外一种方式关联多表可以在group表上面
代码语言：javascript复制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://yli:yli@sydnagios:3306/mydb", max_overflow=5)
Base = declarative_base()
class HostToHostUser(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')
class Host(Base): # metaclass,Host.table对象
__tablename__ = 'host'
nid = Column(Integer, primary_key=True,autoincrement=True)
hostname = Column(String(32))
port = Column(String(32))
ip = Column(String(32))
#这次是在Host表上做关联，指定通过HostToHostUser表给HostUser关联起来
host_user = relationship('HostUser', secondary=HostToHostUser.__table__, backref='h')
class HostUser(Base):
__tablename__ = 'host_user'
nid = Column(Integer, primary_key=True,autoincrement=True)
username = Column(String(32))
# host = relationship("Host", backref='h')
# host_user = relationship("HostUser", backref='u')
def init_db():
Base.metadata.create_all(engine)
#
def drop_db():
Base.metadata.drop_all(engine)
# init_db()
# # drop_db()
Session = sessionmaker(bind=engine)
session = Session()
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
print(host_obj.host_user)
for item in host_obj.host_user:
print(item.username)
------------
[<__main__.HostUser object at 0x000001D422BCEBA8>, <__main__.HostUser object at 0x000001D422BCE550>, <__main__.HostUser object at 0x000001D422BCE630>]
root
db
nb上面两张方式，个人觉得第一种更容易理解，第二种写代码更省事
