SQLAlchemy 对象关系

建层次结构数据代码

class PostCategoryHierarchy(Base):    __tablename__="postCategoryHierarchy"    id = Column(Integer, primary_key=True)    code = Column(String(2))    parentCode = Column(String(2))    name = Column(Unicode)    def __init__(self, code, parentCode, name):       self.code = code       self.parentCode = parentCode       self.name = nameIndex('uix',      PostCategoryHierarchy.code,      PostCategoryHierarchy.parentCode,      PostCategoryHierarchy.name,      unique=True)def populate():    session = DBSession()    session.add_all([          PostCategoryHierarchy('01','01','服务'),          PostCategoryHierarchy('02','01','照顾'),          PostCategoryHierarchy('03','01','搬运'),          PostCategoryHierarchy('04','01','金融')])    session.flush()    transaction.commit()

以上例子只是数据库有层次, 代码这边其实就没有. SqlAlchemy 其实支持类继承方式来映射Hierarchy

Mapping Class Inheritance Hierarchies

SQLAlchemy supports three forms of inheritance:

1. single table inheritance, where several types of classes are stored in one table,
2. concrete table inheritance, where each type of class is stored in its own table,
3. and joined table inheritance, where the parent/child classes are stored in their own tables that are joined together in a select.

Whereas support for single and joined table inheritance is strong, concrete table inheritance is a less common scenario with some particular problems so is not quite as flexible.

单表继承

Single Table Inheritance

Single table inheritance is where the attributes of the base class as well as all subclasses are represented within a single table. A column is present in the table for every attribute mapped to the base class and all subclasses; the columns which correspond to a single subclass are nullable. This configuration looks much like joined-table inheritance except there’s only one table. In this case, a type column is required, as there would be no other way to discriminate between classes. The table is specified in the base mapper only; for the inheriting classes, leave their table parameter blank:

employees_table = Table('employees', metadata,    Column('employee_id', Integer, primary_key=True),    Column('name', String(50)),    Column('manager_data', String(50)),    Column('engineer_info', String(50)),    Column('type', String(20), nullable=False))employee_mapper = mapper(Employee, employees_table, \    polymorphic_on=employees_table.c.type, polymorphic_identity='employee')manager_mapper = mapper(Manager, inherits=employee_mapper, polymorphic_identity='manager')engineer_mapper = mapper(Engineer, inherits=employee_mapper, polymorphic_identity='engineer')

Note that the mappers for the derived classes Manager and Engineer omit the specification of their associated table, as it is inherited from the employee_mapper. Omitting the table specification for derived mappers in single-table inheritance is required.

表间外键关联的迷思

我被SQLAlchemy的文档搞晕了, 那个backref的教学文档相当的万恶, 现在我来简化和小结一下:

SQLAlchemy的外键定义设计为数据库级别和类级别

例子的数据表关系:
User (id, name) 1->* Email Address Address(id, email_address,user_id)
一个User 有多个Email Address. 在Address中的user_id是外键, 和User的id 关联

数据库级别要写这么一句,

user_id = Column(Integer, ForeignKey('users.id'))

为了在类/对象处理时方便用对象语法, 在 Address的类级别得这么写:

user = relation("User", order_by="User.id", backref="addresslist")

这么写的结果是在user这边可以使用 .addresslist属性来引用Address类, 同时还可以再引用回来: .addresslist[x].user

class User(Base):    __tablename__="users"    id = Column(Integer, primary_key=True)    name = Column(String)    fullname = Column(String)    password = (String)     def __init__(self, name, fullname, password):        self.name = name        self.fullname = fullname        self.password = password     def __repr__(self):        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

class Address(Base):    __tablename__ = 'addresses'    id = Column(Integer, primary_key=True)    email_address = Column(String, nullable=False)    # ForeignKey relationship between tables at the database level    user_id = Column(Integer, ForeignKey('users.id'))     # The relationship between classes is defined separately using the relation() function    user = relation("User", order_by="User.id", backref="addresseslist")     def __init__(self, email_address):        self.email_address = email_address     def __repr__(self):        return "<Address('%s')>" % self.email_address

操作对象的例子:

jack = User('jack', 'Jack Bean', 'pass')

从User类引用addresslist属性:

jack.addresseslist = [Address(email_address='[e-mail]'), Address(email_address='[e-mail]')]
print jack.addresslist[1]
<Address('[e-mail]')>

再引用回来:

print jack.addresslist[1].user
<User('jack','Jack Bean', 'pass')>

原文提到了双方向关系由backref来建立, 并且"纯粹在内存中维护关系": When using a bidirectional relationship, elements added in one direction automatically become visible in the other direction. This is the basic behavior of the backref keyword, which maintains the relationship purely in memory, without using any SQL

出处：http://iwrite.wikidot.com/technical:sqlalchemy