Python 13rd Day

SQLAlchemy - Object-Relational Mapper

First create Base class:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

declarative_base is a factory function, that returns a base class (actually a metaclass), and the entities are going to inherit from it.

The entities are classes, which derive from the Base class.

场景：

An image consist of a UUID and its associated number of likes. 一张图片由一个 UUID 和相关联的 like 组成

Each image can be associated with many tags, a tag can be associated with many images. 每张图片都可关联很多 tags, 每个 tag 都可以关联很多图片

That's a many-to-many relationship, so we need a mapping table. 图片和 tag 是多对多的关系，需要一个 mappping table 来定义对应关系

Finally each image can have multiple comments, a one-to-many relation with a foreign key on the comments side. 每张图片可以有很多 comments, 一对多关系由外键关联到 comments side.

涉及到的表：

Tag <=多对多=> Image <=一对多=> Comment

from datetime import datetime, timedelta
from sqlalchemy import Table, Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.orm import relationship, backref

tags = Table('tag_image', Base.metadata,
    Column('tag_id', Integer, ForeignKey('tags.id')),
    Column('image_id', Integer, ForeignKey('images.id'))
)

class Image(Base):

    __tablename__ = 'images'

    id          =   Column(Integer, primary_key=True)
    uuid        =   Column(String(36), unique=True, nullable=False)
    likes       =   Column(Integer, default=0)
    created_at  =   Column(DateTime, default=datetime.utcnow)
    # the backref parameter which adds the image properties to the tags and comments entities
    tags        =   relationship('Tag', secondary=tags, backref = backref('images', lazy='dynamic'))
    comments    =   relationship('Comment', backref='image', lazy='dynamic')

    def __repr__(self):
        str_created_at = self.created_at.strftime("%Y-%m-%d %H:%M:%S")
        return "<Image (uuid='%s', likes='%d', created_at=%s)>" % (self.uuid, self.likes, str_created_at)

class Tag(Base):

    __tablename__ = 'tags'

    id      =   Column(Integer, primary_key=True)
    name    =   Column(String(255), unique=True, nullable=False)

    def __repr__(self):
        return "<Tag (name='%s')>" % (self.name)

class Comment(Base):

    __tablename__ = 'comments'

    id          =   Column(Integer, primary_key=True)
    text        =   Column(String(2000))
    image_id    =   Column(Integer, ForeignKey('images.id'))

    def __repr__(self):
        return "<Comment (text='%s')>" % (self.text)

How-to-Query:

# Get a list of tags:
for name in session.query(Tag.name).order_by(Tag.name):
    print name

# How many tags do we have?
session.query(Tag).count()

# Get all images created yesterday:
session.query(Image) \
    .filter(Image.created_at < datetime.utcnow().date()) \
    .all()

# Get all images, that belong to the tag 'car' or 'animal', using a subselect:
session.query(Image) \
    .filter(Image.tags.any(Tag.name.in_(['car', 'animal']))) \
    .all()

# This can also be expressed with a join:
session.query(Image) \
    .join(Tag, Image.tags) \
    .filter(Tag.name.in_(['car', 'animal'])) \
    .all()

正向查找 & 反向查找

一对多：

用户表 (User)

nid	username	group_id
1	gary	1
2	leon	2

组表 (Group)

nid	caption
1	dba
2	dev

代码：

class Group(Base):
    __tablename__ = 'group'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    caption = Column(String(32))

    def __repr__(self):
        # return str
        temp = "%s - %s" % (self.nid, self.caption)
        return temp


class User(Base):
    __tablename__ = 'user'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(32))
    # foreign key
    group_id = Column(Integer, ForeignKey('group.nid'))
    # add relationship to Group table
    group = relationship("Group", backref='uuu') # uuu for 反向查找

    def __repr__(self):
        # return str
        temp = "%s - %s - %s" % (self.nid, self.username, self.group_id)
        return temp

查找组为 dba 的所有用户，

一般查询（正向）

# 正向查找组是 dba 的所有用户
ret  = session.query(User).join(Group, isouter=True).filter(Group.caption == 'dba').all()
for obj in ret:
    print(obj.username, obj.group.caption) # gary dba

正向查询 query 用户表并通过 join 关联 Group 表，利用 relationship 创建的 group 虚拟字段连接 group 表

反向查询

# 反向查找
# 无需去 User 表查询, 而是直接去 Group 表中查询 uuu 字段, 反向取得 dba 的组成员
obj = session.query(Group).filter(Group.caption == 'dba').first() # caption 为 dba 的一行数据
print(obj.uuu[0].username, obj.caption) # gary dba

反向查找直接查询 Group 表获得 caption 为 dba 的行，然后通过 uuu 字段关联 User 表获得对应用户名

多对多：

表关系

代码：

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):
    # parent
    __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 子表 (正向), h 在子表 (Host) 里指向父表 (反向)
    host = relationship("Host", backref='h')
    # HostToHostUser.host_user 与 HostUser 对应, 指向 HostUser 子表 (正向), u 在子表 (HostUser) 里指向父表 (反向)
    host_user = relationship("HostUser", backref='u')

获取能够登陆 c1 主机的所有用户，

一般查找（正向）：

从 Host 表中获取主机名为 c1 的行
从关系表中获取与主机 nid 对应的用户 nid
根据得到的用户 nid 从用户表中查询到用户名

代码：

# 找 c1 主机的主机 id
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()

# 允许登陆 c1 主机的用户 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) # 把列表转成 tuple

# 根据用户 id 用户查询 username
users = session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
# print(users) # [('root',), ('db',), ('nb',)]
u = zip(*users)
print(list(u)[0])  # ('root', 'db', 'nb')

反向查询：

class HostToHostUser(Base):
    # parent
    __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 子表 (正向), h 在子表 (Host) 里指向父表 (反向)
    host = relationship("Host", backref='h')
    # HostToHostUser.host_user 与 HostUser 对应, 指向 HostUser 子表 (正向), u 在子表 (HostUser) 里指向父表 (反向)
    host_user = relationship("HostUser", backref='u')

从 Host 表中获取主机名为 c1 的行
利用虚拟字段 Host.h 反向得到关系表中的相关行
利用 HostToHostUser.host_user 正向关联用户表

代码：

host_obj = session.query(Host).filter(Host.hostname=='c1').first()
for item in host_obj.h: # 关系表中的多个对象, 原理是利用主机表里的 h 虚拟字段指向关系表里主机 id 为 1 的三行数据, 这是反向查询
    # print(item.host_user.username) 
    # print(item.host_user_id)
    print(item.host_user.nid, item.host_user.username)  # item.host_user 通过外键与 HostUser 关联, 这是正向查询

posted @ 2016-08-05 20:18 garyyang 阅读(174) 评论(0) 收藏举报

刷新页面返回顶部

garyyang

Python 13rd Day

SQLAlchemy - Object-Relational Mapper

正向查找 & 反向查找

公告