SQLAlchemy using notes--sqlalchemy ORM (ii)

References:
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 foreign key to user in 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) )

Relationship Backref in the form of use:

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

Ability to 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

Join

>>> 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 will join the initiative itself.

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 join secondary 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 eliminates the need to manipulate the intermediate table when a child is added to delete parent or parent to delete child. Simply add and delete to it.

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

You can also use a class to create an intermediate table, which allows you to save some other information in the intermediate table. But you don't want to be the same as before. Take the initiative to manipulate the intermediate table.

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

Ability to 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 (address) because there is only one built-in between 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,
Like what:

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

Suppose you want to customize the table using join. Ability to use 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

Look directly at the official documentation sample:

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

Look at an official document sample:

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)