SQLAlchemy using notes--sqlalchemy ORM (ii)

Reference:
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

Establish a relationship between tables to build out

Add a user's foreign key to address

 fromSQLAlchemy Import ForeignKey, Column, String, Integer fromSqlalchemy.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) )

The Backref parameter usage form in relationship:

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

You can use user.addresses to get address from user and use address.users address to get user

Backref will add a relationship between user and address, which is essentially:

From SQLAlchemy ImportInteger, 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 in the

Add to

>>> jack.addresses = [...                 Address(email_address=‘[email protected]‘),...                 Address(email_address=‘[email protected]‘)]

Get

>>> jack.addresses[1]<Address(email_address=‘[email protected]‘)>>>> jack.addresses[1].user<User(name=‘jack‘, fullname=‘Jack Bean‘, password=‘gjffdd‘)>

Commit

session.add(jack)session.commit()

Address is automatically added

One to many relationship

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

Many to one relationship

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

One to one relationship

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 relationship

Need an intermediate table and add secondary parameter in Relatonship

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)

This way, when the child is added to delete parent or parent add Delete child, you do not need to work on the intermediate table, add delete directly.

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

You can also use a class to create an intermediate table so that you can save some additional information in the intermediate table. But you can't do it the same way as before. The intermediate table is automatically manipulated.

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( -) 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 operation

You can use Query.join ()

>>> session.query(User).join(Address)....         filter(Address.email_address==‘[email protected]‘)....         all()[<User(name=‘jack‘, fullname=‘Jack Bean‘, password=‘gjffdd‘)>]

You can use join directly on user because there is only one outside of user and Address, other join forms:

query.join(Address, User.id==Address.user_id)    explicit conditionquery.join(User.addresses)                       lefttorightquery.join(Address, User.addresses)              withexplicit targetquery.join(‘addresses‘)                          # same, using a string

Using external links

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

When multiple entity points are used in query, use the join default join to chase the left side,
For example:

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

If you want to customize the table using join, you can use the Select_form

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

Alias aliases

If you want to join yourself, you can use aliases

from sqlalchemy.orm import aliasedadalias1 = aliased(Address)adalias2 = aliased(Address)forin     session.query(User.name, adalias1.email_address, adalias2.email_address).    join(adalias1, User.addresses).    join(adalias2, User.addresses).    filter(adalias1.email_address==‘[email protected]‘).    filter(adalias2.email_address==‘[email protected]‘):    print(username, email1, email2)

Working with sub-queries

See examples of official documents directly:

>>> from sqlalchemy.sql import func>>> stmt = Session.query (address.user_id, Func.count (' * ')....Label' Address_count '))....Group_by (address.user_id). Subquery () >>> forU, CountinchSession.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

Using exists

See examples of official documents:

forin session.query(User.name).filter(stmt):...     print(name)jack

Equivalent to:

forin session.query(User.name)....         filter(User.addresses.any()):...     print(name)jack

User.addresses can use = =,! =, any, and so on in the filter like other properties in user.

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, Span class= "hljs-string" > ' addresses ' )  

SQLAlchemy using notes--sqlalchemy ORM (ii)