SQLAlchemy使用笔记－－SQLAlchemy ORM（二）

参考：
http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#building-a-relationship
http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#working-with-related-objects

建立表之间带关系

建立外建

在address添加user的外键

from sqlalchemy import ForeignKey, Column, String, Integerfrom sqlalchemy.orm import relationshipclass User(Base):    __tablename__ = 'users'    id = Column(Integer, primary_key=True)    name = Column(String)    fullname = Column(String)    password = Column(String)class Address(Base):    __tablename__ = 'addresses'    id = Column(Integer, primary_key=True)    email_address = Column(String, nullable=False)    user_id = Column(Integer, ForeignKey('users.id'))    user = relationship('User', backref=backref('addresses', order_by=id))

relationship中的backref参数使用形式：

backref="addresses" #直接使用表名的字符串backref=backref('addresses') #使用backref函数backref=backref('addresses', order_by=id)) #brackref函数可以添加参数，详见http://docs.sqlalchemy.org/en/rel_1_0/orm/backref.html#backref-arguments

可以使用user.addresses 从user获取address 和，使用address.users 虫address获取user

backref 会在User跟Address上都加上关系，它本质是：

from sqlalchemy import Integer, ForeignKey, String, Columnfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import relationshipBase = declarative_base()class User(Base):    __tablename__ = 'user'    id = Column(Integer, primary_key=True)    name = Column(String)    addresses = relationship("Address", back_populates="user")class Address(Base):    __tablename__ = 'address'    id = Column(Integer, primary_key=True)    email = Column(String)    user_id = Column(Integer, ForeignKey('user.id'))    user = relationship("User", back_populates="addresses")

relationship中的

添加

>>> jack.addresses = [...                 Address(email_address='[e-mail]'),...                 Address(email_address='[e-mail]')]

获取

>>> jack.addresses[1]<Address(email_address='[e-mail]')>>>> jack.addresses[1].user<User(name='jack', fullname='Jack Bean', password='gjffdd')>

commit

session.add(jack)session.commit()

address 会自动的添加

one to many 关系

class Parent(Base):    __tablename__ = 'parent'    id = Column(Integer, primary_key=True)    children = relationship("Child", backref="parent")class Child(Base):    __tablename__ = 'child'    id = Column(Integer, primary_key=True)    parent_id = Column(Integer, ForeignKey('parent.id'))

many to one 关系

class Parent(Base):    __tablename__ = 'parent'    id = Column(Integer, primary_key=True)    child_id = Column(Integer, ForeignKey('child.id'))    child = relationship("Child", backref="parents")class Child(Base):    __tablename__ = 'child'    id = Column(Integer, primary_key=True)

one to one 关系

from sqlalchemy.orm import backrefclass Parent(Base):    __tablename__ = 'parent'    id = Column(Integer, primary_key=True)    child_id = Column(Integer, ForeignKey('child.id'))    child = relationship("Child", backref=backref("parent", uselist=False))class Child(Base):    __tablename__ = 'child'    id = Column(Integer, primary_key=True)

many to many 关系

需要一个中间表和在relatonship 添加secondary参数

association_table = Table('association', Base.metadata,    Column('left_id', Integer, ForeignKey('left.id')),    Column('right_id', Integer, ForeignKey('right.id')))class Parent(Base):    __tablename__ = 'left'    id = Column(Integer, primary_key=True)    children = relationship("Child",                    secondary=association_table,                    backref="parents")class Child(Base):    __tablename__ = 'right'    id = Column(Integer, primary_key=True)

这样在child添加删除parent或者parent添加删除child时，无需对中间表进行操作，直接添加删除即可。

parent.children.append(child)child.parents.append(parent)

也可以 使用类来创建中间表，这样可以在中间表中保存一些其他的信息。但是就不能想前面一样自动对中间表进行操作。

class Association(Base):    __tablename__ = 'association'    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)    extra_data = Column(String(50))    child = relationship("Child", back_populates="parents")    parent = relationship("Parent", back_populates="children")class Parent(Base):    __tablename__ = 'left'    id = Column(Integer, primary_key=True)    children = relationship("Association", back_populates="parent")class Child(Base):    __tablename__ = 'right'    id = Column(Integer, primary_key=True)    parents = relationship("Association", back_populates="child")

join 操作

可以使用Query.join()

>>> session.query(User).join(Address).\...         filter(Address.email_address=='[e-mail]').\...         all()[<User(name='jack', fullname='Jack Bean', password='gjffdd')>]

在User上可以直接使用join(Address) 因为只有一个外建在User和Address之间，其他join形式：

query.join(Address, User.id==Address.user_id)    # explicit conditionquery.join(User.addresses)                       # specify relationship from left to rightquery.join(Address, User.addresses)              # same, with explicit targetquery.join('addresses')                          # same, using a string

使用外链接

query.outerjoin(User.addresses)   # 默认是左外连接。

当query中有多个实体点使用，使用join默认join追左边的那个，
例如：

query = session.query(User, Address).join(User) # 报错query = session.query(Address， User).join(User) # 正确

如果想自定使用join那个表，可以使用select_form

query = Session.query(User, Address).select_from(Address).join(User)

alias 别名

如果想join自己，可以使用别名

from sqlalchemy.orm import aliasedadalias1 = aliased(Address)adalias2 = aliased(Address)for username, email1, email2 in \    session.query(User.name, adalias1.email_address, adalias2.email_address).\    join(adalias1, User.addresses).\    join(adalias2, User.addresses).\    filter(adalias1.email_address=='[e-mail]').\    filter(adalias2.email_address=='[e-mail]'):    print(username, email1, email2)

使用子查询

直接看官方文档的例子：

>>> from sqlalchemy.sql import func>>> stmt = session.query(Address.user_id, func.count('*').\...         label('address_count')).\...         group_by(Address.user_id).subquery()>>> for u, count in session.query(User, stmt.c.address_count).\...     outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id):...     print(u, count)<User(name='ed', fullname='Ed Jones', password='f8s7ccs')> None<User(name='wendy', fullname='Wendy Williams', password='foobar')> None<User(name='mary', fullname='Mary Contrary', password='xxg527')> None<User(name='fred', fullname='Fred Flinstone', password='blah')> None<User(name='jack', fullname='Jack Bean', password='gjffdd')> 2

使用EXISTS

看官方文档的例子：

>>> from sqlalchemy.sql import exists>>> stmt = exists().where(Address.user_id==User.id)SQL>>> for name, in session.query(User.name).filter(stmt):...     print(name)jack

等价于：

>>> for name, in session.query(User.name).\...         filter(User.addresses.any()):...     print(name)jack

user.addresses 可以像user中其他属性一样在filter使用==、!=、any等等。

query.filter(Address.user == someuser)query.filter(Address.user != someuser)query.filter(Address.user == None)query.filter(User.addresses.contains(someaddress))query.filter(User.addresses.any(Address.email_address == 'bar'))# also takes keyword arguments:query.filter(User.addresses.any(email_address='bar'))query.filter(Address.user.has(name='ed'))session.query(Address).with_parent(someuser, 'addresses')