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

建立表之间带关系

建立外键

在address添加user的外键

<code class="hljs haskell">from sqlalchemy import ForeignKey, Column, String, Integer
from sqlalchemy.orm import relationship
 
 
class 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))</code>

relationship中的backref参数使用形式：

<code class="hljs bash">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</code>

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

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

<code class="hljs mathematica">from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
 
Base = 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")</code>

relationship中的

添加

<code class="hljs r">>>> jack.addresses = [
...                 Address(email_address='jack@google.com'),
...                 Address(email_address='j25@yahoo.com')]</code>

获取

<code class="hljs scss">>>> jack.addresses[1]
 
>>> jack.addresses[1].user
<user(name='jack', fullname="Jack Bean" password="gjffdd"></user(name='jack',></address(email_address='j25@yahoo.com')></code>

commit

1 2	`<code` `class="hljs avrasm">session.add(jack)` `session.commit()</code>`

address 会自动的添加

one to many 关系

<code class="hljs mathematica">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'))</code>

many to one 关系

<code class="hljs mathematica">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)</code>

one to one 关系

<code class="hljs python">from sqlalchemy.orm import backref
 
class 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)</code>

many to many 关系

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

<code class="hljs mathematica">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)</code>

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

1 2	`<code` `class="hljs avrasm">parent.children.append(child)` `child.parents.append(parent)</code>`

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

<code class="hljs mathematica">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")</code>

join 操作

可以使用Query.join()

<code class="hljs r">>>> session.query(User).join(Address).\
...         filter(Address.email_address=='jack@google.com').\
...         all()
[<user(name='jack', fullname="Jack Bean" password="gjffdd">]</user(name='jack',></code>

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

<code class="hljs vbscript">query.join(Address, User.id==Address.user_id)    # explicit condition
query.join(User.addresses)                       # specify relationship from left to right
query.join(Address, User.addresses)              # same, with explicit target
query.join('addresses')                          # same, using a string</code>

使用外链接

1	`<code` `class="hljs avrasm">query.outerjoin(User.addresses) # 默认是左外连接。</code>`

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

1 2	`<code` `class="hljs avrasm">query = session.query(User, Address).join(User) # 报错` `query = session.query(Address， User).join(User) # 正确</code>`

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

1	`<code` `class="hljs avrasm">query = Session.query(User, Address).select_from(Address).join(User)</code>`

alias 别名

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

<code class="hljs cs">from sqlalchemy.orm import aliased
 
adalias1 = 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=='jack@google.com').\
    filter(adalias2.email_address=='j25@yahoo.com'):
    print(username, email1, email2)</code>

使用子查询

直接看官方文档的例子：

<code class="hljs r">>>> 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</user(name='jack',></user(name='fred',></user(name='mary',></user(name='wendy',></user(name='ed',></code>

使用EXISTS

看官方文档的例子：

<code class="hljs r">>>> 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</code>

等价于：

<code class="hljs r">>>> for name, in session.query(User.name).\
...         filter(User.addresses.any()):
...     print(name)
jack</code>

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

<code class="hljs livecodeserver">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')</code>

posted @ 2015-12-23 11:52 雅思敏阅读(378) 评论(0) 收藏举报

刷新页面返回顶部

雅思敏

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