Python 学习笔记 - SQLAlc

继续上一篇sqlAlchemy的学习之旅。

多对多表的创建

表Host和表HostUser通过表HostToHostUser关联在一起

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快速定位到对应的表

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 Files\python3\python.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表上面

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

上面两张方式，个人觉得第一种更容易理解，第二种写代码更省事