对象关系映射(Object Relation Map, ORM)可以将一个类映射为关系模式(数据表). 使用ORM比直接书写SQL在安全性,可读性上都有很大优势.
Working with Related Objects
下面的示例展示如何使用ORM定义一个关系模式并进行实例化.
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
import MySQLdb
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = 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 "<User(user_id='%d', name='%s'), fullname='%s'>" % (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 COMMIT
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 (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 result
print(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 user
INFO sqlalchemy.engine.base.Engine ()
<User(user_id='1', name='finley'), fullname='finley ?'>
<User(user_id='3', name='finley3'), fullname='finley ?'>
也可以很方便的指定要查询的列:
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, ForeignKey
import MySQLdb
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
Base = 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 "<User(user_id='%d', name='%s', fullname='%s')>" % (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 "<Address(address_id='%d', user_id='%d')>" % (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 session
if __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记录重新添加的方法的)