python sqlalchemy原理_Python 学习笔记 - SQLAlchemy(下)

继续上一篇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 FilesPython3python.exe" "C:/Users/yli/Documents/Tencent Files/38144205/FileRecv/s13课上代码/s13课上代码/s13day13课上代码/s13day13_课上代码/ORM—de,p.py"

1

c1

[<__main__.hosttohostuser>, <__main__.hosttohostuser>, <__main__.hosttohostuser>]

<__main__.hostuser> 1 root

<__main__.hostuser> 2 db

<__main__.hostuser> 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>, <__main__.hostuser>, <__main__.hostuser>]

root

db

nb

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