对象关系映射(Object Relation Map, ORM)可以将一个类映射为关系模式(数据表). 使用ORM比直接书写SQL在安全性,可读性上都有很大优势.

Working with Related Objects

下面的示例展示如何使用ORM定义一个关系模式并进行实例化.

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKeyimport MySQLdbfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()class User(Base):    __tablename__ = 'user'    user_id = Column('user_id', Integer, primary_key=True)    name = Column('name', String(20))    fullname = Column('fullname', String(20))        def __repr__(self):        return "
    
     " % (self.user_id, self.name, self.fullname)def get_session():     engine = create_engine("mysql://root:248536@localhost:3306/test?charset=utf8", encoding="utf-8", echo=True)    mysql_session_maker = sessionmaker(bind=engine)    session = mysql_session_maker()    return session     if __name__ == '__main__':    session = get_session()    user = User(user_id=1, name='finley', fullname='finley ?')    session.add(user)    session.commit()    user = User(user_id=2, name='finley2', fullname='finley ?')    session.add(user)    session.rollback()    user = User(user_id=3, name='finley3', fullname='finley ?')    session.add(user)    session.commit()    session.close()
    

终端回显:

INFO sqlalchemy.engine.base.Engine BEGIN (implicit)INFO sqlalchemy.engine.base.Engine INSERT INTO user (user_id, name, fullname) VALUES (%s, %s, %s)INFO sqlalchemy.engine.base.Engine (1, 'finley', 'finley ?')INFO sqlalchemy.engine.base.Engine COMMITINFO sqlalchemy.engine.base.Engine BEGIN (implicit)INFO sqlalchemy.engine.base.Engine INSERT INTO user (user_id, name, fullname) VALUES (%s, %s, %s)INFO sqlalchemy.engine.base.Engine (3, 'finley3', 'finley ?')INFO sqlalchemy.engine.base.Engine COMMIT

上述示例展示了通过ORM和session进行事务操作(implicit transcation).

通过metadata创建表:

Base.metadata.create_all(engine)

rollback()可以回滚到上次commit.

通过session进行事务性操作, 注意那些复杂的工厂.

Add & Remove

生成实例后用session进行添加或者删除操作:

user = User(user_id=1, name='finley', fullname='finley ?')session.add(user)session.commit()

删除操作类似, 注意User实例通过查询数据库获得:

user = session.query(User).filter(User.user_id == 1)[0]session.delete(user)session.commit()

Query

首先做一个全查询:

result = session.query(User)session.commit()session.close()# print resultprint(result)for instance in result:    print(instance)

返回的结果:

INFO sqlalchemy.engine.base.Engine   SELECT user.user_id AS user_user_id, user.name AS user_name, user.fullname AS user_fullname FROM userINFO sqlalchemy.engine.base.Engine ()
    
     
    

也可以很方便的指定要查询的列:

result = session.query(User.user_id, User.name)

结果:

(1L, u'finley')(3L, u'finley3')

ORM使用filter来筛选记录:

>>> result = session.query(User.user_id, User.name).filter(User.user_id == 1).all()>>>session.commit()>>>session.close()>>>>>>print(result) [(1L, u'finley')]

query对象使用和select类似的运算符, filter可以链式调用并支持order_by()等功能.

更多黑魔法请参见

Relationship

Many to One

Relationship用来维护两个数据表之间的关系, 含有Relationship的表需要使用外键指定参照关系:

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKeyimport MySQLdbfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import relationship, backrefBase = declarative_base()class User(Base):    __tablename__ = 'user'    user_id = Column('user_id', Integer, primary_key=True)    name = Column('name', String(20))    fullname = Column('fullname', String(20))    def __repr__(self):        return "
    
     " % (self.user_id, self.name, self.fullname)class Address(Base):    __tablename__ = 'address'    address_id = Column('address_id', Integer, primary_key=True)    user_id = Column(Integer, ForeignKey('user.user_id'))    user = relationship("User", backref=backref('addresses'))    def __repr__(self):        return "
     
      " % (self.address_id, self.user_id)def get_session():     engine = create_engine("mysql://root:248536@localhost:3306/test?charset=utf8", encoding="utf-8", echo=True)    mysql_session_maker = sessionmaker(bind=engine)    session = mysql_session_maker()    return sessionif __name__ == '__main__':    session = get_session()    # add by user    user = User(user_id=1, name='finley', fullname='finley ?')    user.addresses = [        Address(address_id=1, user_id=1),        Address(address_id=2, user_id=1)    ]    session.add(user);    # add by address    address = Address(address_id=3, user_id=1)    session.add(address)    session.commit()    # query relationship    result = session.query(User).filter(User.user_id == 1)    for i in result:        print(i.addresses)    session.close()
     
    

反向索引backref使得User实例可以查询与其关联的address:

user.addresses

添加关系可以直接操作Address表:

address = Address(address_id=3, user_id=1)session.add(address) # session.remove(address)session.commit()

也可以通过User的backref:

user = User(user_id=1, name='finley', fullname='finley ?')    user.addresses = [        Address(address_id=1, user_id=1),        Address(address_id=2, user_id=1)    ]    session.add(user);

删除操作类似, 不过因为Foreign Key约束的存在, 需要费点事.

(我不会说自己用删除user记录重新添加的方法的)

Many to Many