SqlAlchemy ORM

 is then specified on the parent, as referencing a collection of items represented by the child

from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

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

 

To establish a bidirectional relationship in one-to-many, where the “reverse” side is a many to one, specify an additional relationship() and connect the two using therelationship.back_populates parameter:

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

Child will get a parent attribute with many-to-one semantics.

Alternatively, the backref option may be used on a single relationship() instead of usingback_populates:

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

附，原生sql join查询

几个Join的区别 http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins 

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

select host.id,hostname,ip_addr,port,host_group.name from host right join host_group on host.id = host_group.host_id

in SQLAchemy

session.query(Host).join(Host.host_groups).filter(HostGroup.name==‘t1‘).group_by("Host").all()

group by 查询

select name,count(host.id) as NumberOfHosts from host right join host_group on host.id= host_group.host_id group by name;

in SQLAchemy

from sqlalchemy import func session.query(HostGroup, func.count(HostGroup.name )).group_by(HostGroup.name).all()   #another example session.query(func.count(User.name), User.name).group_by(User.name).all() SELECT count(users.name) AS count_1, users.name AS users_name FROM users GROUP BY users.name

 

文档采用：http://www.cnblogs.com/alex3714/articles/5248247.html 

 

 

 

 

 

 

 

SqlAlchemy ORM

标签：转换   let   外键   html   cti   ext   false   ring   parent