Follower me on GitHub

1  ORM模型的简单性简化了数据库查询过程。使用ORM查询工具,用户可以访问期望数据,而不必理解数据库的底层结构

以下是SQL语句: region_table = Table( ‘region’, metadata, Column(‘id’, Integer, primary_key=True), Column(‘name’, Unicode(255))) 相应的类: class Region(object):

def __init__(self, name): self.name = name

def __repr__(self): return ‘<Region %s>’ % self.name

看一下在交互模式下:

>>> dir(Region) [‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__format__’, ‘__getattribute__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__weakref__’] >>> mapper(Region,region_table)  #ORM映射 <Mapper at 0x84bdb2c; Region> >>> dir(Region) [‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__format__’, ‘__getattribute__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__weakref__’, ‘_sa_class_manager’, ‘id’, ‘name’] #增加了很多属性 >>> Region.id <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x84c238c> >>> Region.name <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x84c254c>

>>> r0 = Region(name=”Northeast”) >>> r1 = Region(name=”Southwest”) >>> r0 <Region Northeast>  #类能显示这样的数据是因为类定义了__repr__方法 >>> r1 <Region Southwest> >>> from sqlalchemy.orm import clear_mappers >>> clear_mappers() #取消映射 >>> Region.name #不再有这个属性 Traceback (most recent call last): File “<stdin>”, line 1, in <module> AttributeError: type object ‘Region’ has no attribute ‘name’ >>> dir(Region)  #回到了原来的只有类属性 [‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__format__’, ‘__getattribute__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__weakref__’]

>>> r0 = Region(name=”Northeast”)  #从这里开始理解ORM做了什么 >>> r1 = Region(name=”Southwest”) #实现了2个类的实例

>>> metadata.create_all(engine) #创建table

>>> Session = sessionmaker()  #通过sessionmaker产生一个会话 >>> Session.configure(bind=engine) #绑定到数据库连接 >>> session = Session()  #产生会话实例,让对象可以被载入或保存到数据库,而只需要访问类却不用直接访问数据库 >>> session.bind.echo = True #显示打印信息

>>> session.add(r1) #把r0,r12个实例加到会话中 >>> session.add(r0) >>> print r0.id  #因为还没有保存,数据为空 None >>> session.flush() #提交数据到数据库 2012-07-18 10:24:07,116 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2012-07-18 10:24:07,116 INFO sqlalchemy.engine.base.Engine INSERT INTO region (name) VALUES (?) 2012-07-18 10:24:07,116 INFO sqlalchemy.engine.base.Engine (‘Southwest’,) 2012-07-18 10:24:07,117 INFO sqlalchemy.engine.base.Engine INSERT INTO region (name) VALUES (?) 2012-07-18 10:24:07,117 INFO sqlalchemy.engine.base.Engine (‘Northeast’,) >>> print r0.id #id因为子增长,出现了 2 >>> r0.name = ‘Northwest’ >>> session.flush() #修改提交 2012-07-18 10:24:50,644 INFO sqlalchemy.engine.base.Engine UPDATE region SET name=? WHERE region.id = ? 2012-07-18 10:24:50,644 INFO sqlalchemy.engine.base.Engine (‘Northwest’, 2) >>> print r0.name #数据库中的数据被update成了新值 Northwest >>> dir(Region) [‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__format__’, ‘__getattribute__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__weakref__’] >>> mapper(Region, region_table, include_properties=[‘id’]) #使用 include_properties只映射某些字段,同样还有exclude_properties <Mapper at 0x84c26cc; Region> >>> dir(Region) [‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__format__’, ‘__getattribute__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__weakref__’, ‘_sa_class_manager’, ‘id’]  #只多了一个”id”

>>> clear_mappers() >>> dir(Region) [‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__format__’, ‘__getattribute__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__weakref__’] >>> mapper(Region, region_table, column_prefix=’_’)  #映射后自定义修改新属性的前缀 <Mapper at 0x84f73ac; Region> >>> dir(Region) [‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__format__’, ‘__getattribute__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__weakref__’, ‘_id’, ‘_name’, ‘_sa_class_manager’] #id和name等前面都有了”_”

>>> clear_mappers() >>> dir(Region) [‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__format__’, ‘__getattribute__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__weakref__’] >>> mapper(Region, region_table, properties=dict( …     region_name=region_table.c.name,  #想把name的属性定义为region_name,因为c.name就是用Table创建的表结构的特定实例的name属性 …     region_id=region_table.c.id)) <Mapper at 0x8509d2c; Region> >>> dir(Region) [‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__format__’, ‘__getattribute__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__weakref__’, ‘_sa_class_manager’, ‘region_id’, ‘region_name’]  #id改名为region_id

>>> class Region(object):  #重新定义类 …     def __init__(self, name): …         self.name = name …     def __repr__(self): …         return ‘<Region %s>’ % self.name …     def _get_name(self): #这个_get和_set是为了让内置的property调用 …         return self._name …     def _set_name(self, value): …         assert value.endswith(‘Region’), \ …             ‘Region names must end in “Region”’ …         self._name = value …     name=property(_get_name, _set_name) #通过property的定义,当获取成员x的值时,就会调用_get_name函数(第一个参数),当给成员x赋值时,就会调用_set_name函数(第二个参数),当删除x时,就会调用delx函数(这里没有设置) … >>> from sqlalchemy.orm import synonym >>> mapper(Region, region_table, column_prefix=’_’, properties=dict( …     name=synonym(‘_name’))) #首先检验_name的属性是否满足 <Mapper at 0x84f7acc; Region> >>> s0 = Region(‘Southeast’)  #没有正确结尾 Traceback (most recent call last): File “<stdin>”, line 1, in <module> File “<string>”, line 4, in __init__ File “/usr/lib/python2.7/site-packages/SQLAlchemy-0.7.8-py2.7-linux-i686.egg/sqlalchemy/orm/state.py”, line 98, in initialize_instance return manager.original_init(*mixed[1:], **kwargs) File “<stdin>”, line 3, in __init__ File “<string>”, line 1, in __set__ File “<stdin>”, line 10, in _set_name AssertionError: Region names must end in “Region” >>> s0 = Region(‘Southeast Region’) #正常

>>> segment_table = Table( …     ‘segment’, metadata, …     Column(‘id’, Integer, primary_key=True), …     Column(‘lat0’, Float), …     Column(‘long0’, Float), …     Column(‘lat1’, Float), …     Column(‘long1’, Float))

>>> metadata.create_all(engine) #创建表 >>> class RouteSegment(object): #一个含有begin和end的类 …     def __init__(self, begin, end): …         self.begin = begin …         self.end = end …     def __repr__(self): …         return ‘<Route %s to %s>’ % (self.begin, self.end) … >>> class MapPoint(object): …     def __init__(self, lat, long): …         self.coords = lat, long …     def __composite_values__(self): #返回比较后的列表或者元祖 …         return self.coords …     def __eq__(self, other): …         return self.coords == other.coords …     def __ne__(self, other): …         return self.coords != other.coords …     def __repr__(self): …         return ‘(%s lat, %s long)’ % self.coords … …

>>> from sqlalchemy.orm import composite >>> mapper(RouteSegment, segment_table, properties=dict( …     begin=composite(MapPoint,  #创建多个属性 …         segment_table.c.lat0, …         segment_table.c.long0), …     end=composite(MapPoint, …         segment_table.c.lat1, segment_table.c.long1))) <Mapper at 0x86203cc; RouteSegment> >>> work=MapPoint(33.775562,-84.29478) >>> library=MapPoint(34.004313,-84.452062) >>> park=MapPoint(33.776868,-84.389785) >>> routes = [ …     RouteSegment(work, library), …     RouteSegment(work, park), …     RouteSegment(library, work), …     RouteSegment(library, park), …     RouteSegment(park, library), …     RouteSegment(park, work)]

>>> for rs in routes: …     session.add(rs) … >>> session.flush() >>> q = session.query(RouteSegment) >>> print RouteSegment.begin==work segment.lat0 = :lat0_1 AND segment.long0 = :long0_1 >>> q = q.filter(RouteSegment.begin==work) >>> for rs in q: …     print rs … 2012-07-18 11:12:29,360 INFO sqlalchemy.engine.base.Engine SELECT segment.id AS segment_id, segment.lat0 AS segment_lat0, segment.long0 AS segment_long0, segment.lat1 AS segment_lat1, segment.long1 AS segment_long1 FROM segment WHERE segment.lat0 = ? AND segment.long0 = ? 2012-07-18 11:12:29,360 INFO sqlalchemy.engine.base.Engine (33.775562, -84.29478) <Route (33.775562 lat, -84.29478 long) to (34.004313 lat, -84.452062 long)> <Route (33.775562 lat, -84.29478 long) to (33.776868 lat, -84.389785 long)>

>>> from sqlalchemy.orm import PropComparator >>> class MapPointComparator(PropComparator): #自定义运算符继承PropComparator类 …     def __lt__(self, other):  #自定义小于运算结果 …         return and_(*[a<b for a, b in …             zip(self.prop.columns, …                 other.__composite_values__())]) … >>> mapper(RouteSegment, segment_table, properties=dict( …     begin=composite(MapPoint, …                     segment_table.c.lat0, segment_table.c.long0, …                     comparator=MapPointComparator),  #定义使用自定义的运算类 …     end=composite(MapPoint, …                   segment_table.c.lat1, segment_table.c.long1, …                   comparator=MapPointComparator))) <Mapper at 0x85b2bac; RouteSegment> >>> product_table = Table( … ‘product’, metadata, … Column(‘sku’, String(20), primary_key=True), … Column(‘msrp’, Numeric), … Column(‘image’, BLOB)) >>> from sqlalchemy.orm import deferred >>> mapper(Product, product_table, properties=dict( …     image=deferred(product_table.c.image)))  #deferred意思是延迟,就是在实现 mapper 时,可以指定某些字段是 Deferred 装入的,这样象通常一样取出数据时,这些字段并不真正的从数据库中取出,只有在你真正需要时才取出,这样可以减少资源的占用和提高效率,只有在读取 image时才会取出相应的数据 <Mapper at 0x862a40c; Product>

>>> metadata.remove(product_table)  #因为已经常见了表,先删除 >>> product_table = Table( …     ‘product’, metadata, …     Column(‘sku’, String(20), primary_key=True), …     Column(‘msrp’, Numeric), …     Column(‘image1’, Binary), …     Column(‘image2’, Binary), …     Column(‘image3’, Binary))

>>> clear_mappers() #已经映射,先取消 >>> mapper(Product, product_table, properties=dict( …     image1=deferred(product_table.c.image1, group=’images’), …     image2=deferred(product_table.c.image2, group=’images’), …     image3=deferred(product_table.c.image3, group=’images’))) #Deferred字段可以通过在 properties 中指定 group参数来表示编组情况。这样当一个组的某个

#字段被取出时, 同组的其它字段均被取出 <Mapper at 0x85b8c4c; Product>

>>> q = product_table.join(  被映射的是join了product_summary_table到product_table的结果 … product_summary_table, … product_table.c.sku==product_summary_table.c.sku).alias(‘full_product’) >>> class FullProduct(object): pass … >>> mapper(FullProduct, q) <Mapper at 0x86709cc; FullProduct>

mapper函数的一些参数:

always_refresh =False:返回查询旧会修改内存中的值,但是populate_existing优先级高

allow_column_override =False:允许关系属性将具有相同的名称定义为一个映射列,否则名称冲突,产生异常

2 ORM的关系

1 1:N relations (1对多)

>>> mapper(Store, store_table) <Mapper at 0x84fba4c; Store> >>> from sqlalchemy.orm import relation >>> mapper(Region, region_table, properties=dict( …     stores=relation(Store))) #让2个表关联,给Region添加一个属性stores,通过它联系Store来修改Store <Mapper at 0x84f76ac; Region>

>>> r0 = Region(‘test’)

>>> session.add(r0) #先生成一条数据 >>> session.commit() 2012-07-18 13:56:26,858 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2012-07-18 13:56:26,859 INFO sqlalchemy.engine.base.Engine INSERT INTO region (name) VALUES (?) 2012-07-18 13:56:26,859 INFO sqlalchemy.engine.base.Engine (‘test’,) 2012-07-18 13:56:26,859 INFO sqlalchemy.engine.base.Engine COMMIT >>> rgn = session.query(Region).get(1)  #获取这条数据 2012-07-18 13:56:37,250 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2012-07-18 13:56:37,251 INFO sqlalchemy.engine.base.Engine SELECT region.id AS region_id, region.name AS region_name FROM region WHERE region.id = ? 2012-07-18 13:56:37,251 INFO sqlalchemy.engine.base.Engine (1,) >>> s0 = Store(name=’3rd and Juniper’) #创建一个实例 >>> rgn.stores.append(s0) #通过Region的依赖建立新的Store(其中的一个字段region_id值来着region的id字段) 2012-07-18 13:56:51,611 INFO sqlalchemy.engine.base.Engine SELECT store.id AS store_id, store.region_id AS store_region_id, store.name AS store_name FROM store WHERE ? = store.region_id 2012-07-18 13:56:51,611 INFO sqlalchemy.engine.base.Engine (1,) >>> session.flush() #保存数据库 2012-07-18 13:57:02,131 INFO sqlalchemy.engine.base.Engine INSERT INTO store (region_id, name) VALUES (?, ?) 2012-07-18 13:57:02,131 INFO sqlalchemy.engine.base.Engine (1, ‘3rd and Juniper’) 注:假如2个表之间有多个外部依赖关系,需要使用primaryjoin指定:

mapper(Region, region_table, properties=dict( stores=relation(Store, primaryjoin=(store_table.c.region_id  #判断关系来着region_id和region的id ==region_table.c.id))))

2 M:N relations(多对多)

上面有SQL语句:我复制过来:

category_table = Table( ‘category’, metadata, Column(‘id’, Integer, primary_key=True), Column(‘level_id’, None, ForeignKey(‘level.id’)), Column(‘parent_id’, None, ForeignKey(‘category.id’)), Column(‘name’, String(20))) product_table = Table( ‘product’, metadata, Column(‘sku’, String(20), primary_key=True), Column(‘msrp’, Numeric)) product_category_table = Table( ‘product_category’, metadata, Column(‘product_id’, None, ForeignKey(‘product.sku’), primary_key=True), Column(‘category_id’, None, ForeignKey(‘category.id’), primary_key=True))

可以看出来product_category_table和category_table 是多对多的关系.

>>> mapper(Category, category_table, properties=dict( …     products=relation(Product, …     secondary=product_category_table))) <Mapper at 0x859c8cc; Category> >>> mapper(Product, product_table, properties=dict( …     categories=relation(Category, …     secondary=product_category_table))) <Mapper at 0x859c5cc; Product>

>>> r0=Product(‘123’,’234’)

>>> session.add(r0) >>> session.flush() 2012-07-18 14:18:06,599 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2012-07-18 14:18:06,618 INFO sqlalchemy.engine.base.Engine INSERT INTO product (sku, msrp) VALUES (?, ?) 2012-07-18 14:18:06,618 INFO sqlalchemy.engine.base.Engine (‘123’, 234.0) >>> session.query(Product).get(‘123’).categories

>>> clear_mappers() >>> mapper(Category, category_table, properties=dict( …     products=relation(Product, secondary=product_category_table, … primaryjoin=(product_category_table.c.category_id  #primaryjoin是要被映射的表和连接表的条件 …                                    == category_table.c.id), … secondaryjoin=(product_category_table.c.product_id  #secondaryjoin是连接表和想加入的表的条件 …                                      == product_table.c.sku)))) <Mapper at 0x84ff7cc; Category> >>> mapper(Product, product_table, properties=dict( …     categories=relation(Category, secondary=product_category_table, … primaryjoin=(product_category_table.c.product_id …                                      == product_table.c.sku), … secondaryjoin=(product_category_table.c.category_id …                                        == category_table.c.id)))) <Mapper at 0x859cb8c; Product> 1:1 relations(一对一):特殊的(1:N) 还是上面的SQL:

product_table = Table( ‘product’, metadata, Column(‘sku’, String(20), primary_key=True), Column(‘msrp’, Numeric)) product_summary_table = Table( ‘product_summary’, metadata, Column(‘sku’, None, ForeignKey(‘product.sku’), primary_key=True), #只有一个外联到product Column(‘name’, Unicode(255)), Column(‘description’, Unicode))

>>> mapper(Product, product_table, properties=dict( …     summary=relation(ProductSummary))) KeyboardInterrupt >>> mapper(ProductSummary, product_summary_table) <Mapper at 0x84fbe6c; ProductSummary> >>> mapper(Product, product_table, properties=dict( …     summary=relation(ProductSummary))) <Mapper at 0x85bee6c; Product> >>> prod = session.query(Product).get(‘123’) []  #product_summary_table因为product_table儿存在,浪费了

>>> mapper(ProductSummary, product_summary_table) <Mapper at 0x84f7dec; ProductSummary> >>> mapper(Product, product_table, properties=dict( …     summary=relation(ProductSummary,uselist=False)))  #使用uselist=False就不会这样了 <Mapper at 0x860584c; Product> >>> prod = session.query(Product).get(‘123’) >>> print prod.summary None >>> mapper(ProductSummary, product_summary_table) <Mapper at 0x859ca0c; ProductSummary> >>> mapper(Product, product_table, properties=dict( …     summary=relation(ProductSummary, uselist=False, …     backref=’product’))) #自定义自己表的函数 <Mapper at 0x860e90c; Product> >>> prod = session.query(Product).get(‘123’) >>> prod.summary = ProductSummary(name=”Fruit”, description=”Some … Fruit”) >>> print prod.summary <ProductSummary Fruit> >>> print prod.summary.product #他的属性就是prod,可就是表本身 <Product 123> >>> print prod.summary.product is prod True

>>> mapper(Level, level_table, properties=dict( …     categories=relation(Category, backref=’level’))) <Mapper at 0x860590c; Level> >>> mapper(Category, category_table, properties=dict( …     products=relation(Product, …         secondary=product_category_table))) <Mapper at 0x860ec8c; Category> >>> mapper(Product, product_table, properties=dict( …     categories=relation(Category, …         secondary=product_category_table))) <Mapper at 0x860e7ec; Product> >>> lvl = Level(name=’Department’) >>> cat = Category(name=’Produce’, level=lvl) >>> session.add(lvl) >>> session.flush() 2012-07-18 14:44:02,005 INFO sqlalchemy.engine.base.Engine INSERT INTO level (parent_id, name) VALUES (?, ?) 2012-07-18 14:44:02,005 INFO sqlalchemy.engine.base.Engine (None, ‘Department’) 2012-07-18 14:44:02,020 INFO sqlalchemy.engine.base.Engine INSERT INTO category (level_id, parent_id, name) VALUES (?, ?, ?) 2012-07-18 14:44:02,020 INFO sqlalchemy.engine.base.Engine (1, None, ‘Produce’) >>> prod = session.query(Product).get(‘123’) >>> print prod.categories [] >>> print cat.products 2012-07-18 14:44:25,517 INFO sqlalchemy.engine.base.Engine SELECT product.sku AS product_sku, product.msrp AS product_msrp FROM product, product_category WHERE ? = product_category.category_id AND product.sku = product_category.product_id 2012-07-18 14:44:25,517 INFO sqlalchemy.engine.base.Engine (1,) [] >>> prod.categories.append(cat) >>> print prod.categories [<Category Department.Produce>] >>> print cat.products  #backref自动更新,在多对多的情况,可以使用relation函数两次,但是2个属性没有保持同步 []  #解决方法:

>>> mapper(Level, level_table, properties=dict( …categories=relation(Category, backref=’level’))) >>> mapper(Category, category_table, properties=dict( …products=relation(Product, secondary=product_category_table, … backref=’categories’)))  #在Product也设置backref,就会保持同步 >>> mapper(Product, product_table) >>> lvl = Level(name=’Department’) >>> cat = Category(name=’Produce’, level=lvl) >>> session.save(lvl) >>> prod = session.query(Product).get(‘123’) >>> print prod.categories [] >>> print cat.products [] >>> prod.categories.append(cat) >>> print prod.categories [<Category Department.Produce>] >>>print cat.products [<Product 123>]

>>> from sqlalchemy.orm import backref >>> clear_mappers() >>> mapper(ProductSummary, product_summary_table, properties=dict( … product=relation(Product, … backref=backref(‘summary’, uselist=False))))  #还可以使用backref函数做一样的事情 <Mapper at 0x860aaec; ProductSummary> >>> mapper(Product, product_table) <Mapper at 0x85bee6c; Product>

4 Self-Referential 自我参照映射 level_table = Table( ‘level’, metadata, Column(‘id’, Integer, primary_key=True), Column(‘parent_id’, None, ForeignKey(‘level.id’)), #这个外联其实还是这个类的id,也就是映射了自己的对象 Column(‘name’, String(20))) >>> mapper(Level, level_table, properties=dict( … children=relation(Level))) #不同层次之间的父子关系,我这里指定得到”子”的属性 <Mapper at 0x860a66c; Level> >>> mapper(Level, level_table, properties=dict( …     children=relation(Level, …     backref=backref(‘parent’, …     remote_side=[level_table.c.id]))))  #remote_side指定’子’的id,local side”就是字段parent_id <Mapper at 0x860e42c; Level> >>> l0 = Level(‘Gender’) >>> l1 = Level(‘Department’, parent=l0) >>> session.add(l0) >>> session.flush() 2012-07-18 15:07:55,810 INFO sqlalchemy.engine.base.Engine INSERT INTO level (parent_id, name) VALUES (?, ?) 2012-07-18 15:07:55,810 INFO sqlalchemy.engine.base.Engine (None, ‘Gender’) #插入l0,他没有父级 2012-07-18 15:07:55,810 INFO sqlalchemy.engine.base.Engine INSERT INTO level (parent_id, name) VALUES (?, ?) 2012-07-18 15:07:55,810 INFO sqlalchemy.engine.base.Engine (2, ‘Department’)

注 我们还能反过来用:

mapper(Level, level_table, properties=dict( parent=relation(Level, remote_side=[level_table.c.parent_id], backref=’children’)))

我们创建一个多引擎的例子:

from sqlalchemy import create_engine
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy import Numeric,Table, MetaData, Column, ForeignKey, Integer, String
engine1 = create_engine('sqlite://')
engine2 = create_engine('sqlite://')
metadata = MetaData()
product_table = Table(
'product', metadata,
Column('sku', String(20), primary_key=True),
Column('msrp', Numeric))
product_summary_table = Table(
'product_summary', metadata,
Column('sku', String(20), ForeignKey('product.sku'), primary_key=True),
Column('name', Unicode(255)),
Column('description', Unicode))
product_table.create(bind=engine1)
product_summary_table.create(bind=engine2)
stmt = product_table.insert()
engine1.execute(
stmt,
[dict(sku="123", msrp=12.34),
dict(sku="456", msrp=22.12),
dict(sku="789", msrp=41.44)])
stmt = product_summary_table.insert()
engine2.execute(
stmt,
[dict(sku="123", name="Shoes", description="Some Shoes"),
dict(sku="456", name="Pants", description="Some Pants"),
dict(sku="789", name="Shirts", description="Some Shirts")])

 

这样就创建了表并且插入了一些数据

dongwm@localhost ~ $ python Python 2.7.3 (default, Jul 11 2012, 10:10:17) [GCC 4.5.3] on linux2 Type “help”, “copyright”, “credits” or “license” for more information. >>> from sqlalchemy import create_engine >>> from sqlalchemy.orm import mapper, sessionmaker >>> from sqlalchemy import Numeric,Table, MetaData, Column, ForeignKey, Integer, String,Unicode >>> engine1 = create_engine(‘sqlite://’) >>> engine2 = create_engine(‘sqlite://’)  #创建多个引擎 >>> metadata = MetaData() >>> product_table = Table( … ‘product’, metadata, … Column(‘sku’, String(20), primary_key=True), … Column(‘msrp’, Numeric)) >>> product_summary_table = Table( … ‘product_summary’, metadata, … Column(‘sku’, String(20), ForeignKey(‘product.sku’), primary_key=True), … Column(‘name’, Unicode(255)), … Column(‘description’, Unicode)) >>> product_table.create(bind=engine1) >>> product_summary_table.create(bind=engine2) >>> stmt = product_table.insert() >>> engine1.execute( … stmt, … [dict(sku=”123”, msrp=12.34), … dict(sku=”456”, msrp=22.12), … dict(sku=”789”, msrp=41.44)]) <sqlalchemy.engine.base.ResultProxy object at 0x84ef9ec> >>> stmt = product_summary_table.insert() >>> engine2.execute(  #用引擎2 插入数据,那么product_summary的数据就在这个引擎 … stmt, … [dict(sku=”123”, name=”Shoes”, description=”Some Shoes”), … dict(sku=”456”, name=”Pants”, description=”Some Pants”), … dict(sku=”789”, name=”Shirts”, description=”Some Shirts”)]) /usr/lib/python2.7/site-packages/SQLAlchemy-0.7.8-py2.7-linux-i686.egg/sqlalchemy/engine/default.py:463: SAWarning: Unicode type received non-unicode bind param value. param.append(processors[key](compiled_params[key])) <sqlalchemy.engine.base.ResultProxy object at 0x84e896c> >>> class Product(object): …     def __init__(self, sku, msrp, summary=None): …         self.sku = sku …         self.msrp = msrp …         self.summary = summary …     def __repr__(self): …         return ‘<Product %s>’ % self.sku … >>> class ProductSummary(object): …     def __init__(self, name, description): …         self.name = name …         self.description = description …     def __repr__(self): …         return ‘<ProductSummary %s>’ % self.name … >>> from sqlalchemy.orm import clear_mappers,backref,relation >>> clear_mappers() >>> mapper(ProductSummary, product_summary_table, properties=dict( …     product=relation(Product, …                      backref=backref(‘summary’, uselist=False)))) <Mapper at 0x84efa4c; ProductSummary> >>> mapper(Product, product_table) <Mapper at 0x84efd0c; Product> >>> Session = sessionmaker(binds={Product:engine1,  #这里绑定了2个引擎,不同orm的引擎不同 …     ProductSummary:engine2}) >>> session = Session() >>> engine1.echo = engine2.echo = True >>> session.query(Product).all() #查询product的数据 2012-07-18 19:00:59,514 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2012-07-18 19:00:59,514 INFO sqlalchemy.engine.base.Engine SELECT product.sku AS product_sku, product.msrp AS product_msrp FROM product 2012-07-18 19:00:59,514 INFO sqlalchemy.engine.base.Engine () /usr/lib/python2.7/site-packages/SQLAlchemy-0.7.8-py2.7-linux-i686.egg/sqlalchemy/types.py:215: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage. d[coltype] = rp = d[‘impl’].result_processor(dialect, coltype) [<Product 123>, <Product 456>, <Product 789>] >>> session.query(ProductSummary).all() #查询ProductSummary 2012-07-18 19:01:07,510 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2012-07-18 19:01:07,510 INFO sqlalchemy.engine.base.Engine SELECT product_summary.sku AS product_summary_sku, product_summary.name AS product_summary_name, product_summary.description AS product_summary_description FROM product_summary 2012-07-18 19:01:07,510 INFO sqlalchemy.engine.base.Engine () [<ProductSummary Shoes>, <ProductSummary Pants>, <ProductSummary Shirts>]

>>> from sqlalchemy.orm.shard import ShardedSession #使用ShardedSession对会话水平分区,根据需求把数据分开 >>> product_table = Table( …     ‘product’, metadata, …     Column(‘sku’, String(20), primary_key=True), …     Column(‘msrp’, Numeric)) >>> metadata.create_all(bind=engine1) >>> metadata.create_all(bind=engine2) >>> class Product(object): …     def __init__(self, sku, msrp): …         self.sku = sku …         self.msrp = msrp …     def __repr__(self): …         return ‘<Product %s>’ % self.sku … >>> clear_mappers() >>> product_mapper = mapper(Product, product_table) >>> def shard_chooser(mapper, instance, clause=None):  #返回包含映射和实例的行的分区ID …     if mapper is not product_mapper: #非设定的orm映射叫做odd …         return ‘odd’ …     if (instance.sku  #数据为偶数也叫做even …         and instance.sku[0].isdigit() …         and int(instance.sku[0]) % 2 == 0): …         return ‘even’ …     else: …         return ‘odd’ #否则叫做odd …

>>> def id_chooser(query, ident):  根据查询和映射类的主键返回对象想通过查询驻留的shard ID列表 …     if query.mapper is not product_mapper: …         return [‘odd’] …     if (ident \ …         and ident[0].isdigit() …         and int(ident[0]) % 2 == 0): …         return [‘even’] …     return [‘odd’] … >>> def query_chooser(query): #返回可选的shard ID列表 …     return [‘even’, ‘odd’] … >>> Session = sessionmaker(class_=ShardedSession) >>> session = Session( …     shard_chooser=shard_chooser, …     id_chooser=id_chooser, …     query_chooser=query_chooser, …     shards=dict(even=engine1, …                 odd=engine2)) >>> products = [ Product(‘%d%d%d’ % (i,i,i), 0.0) …     for i in range(10) ] >>> for p in products: …     session.add(p) … >>> session.flush() >>> for row in engine1.execute(product_table.select()): …     print row … 2012-07-18 19:11:19,811 INFO sqlalchemy.engine.base.Engine SELECT product.sku, product.msrp FROM product 2012-07-18 19:11:19,811 INFO sqlalchemy.engine.base.Engine () (u’000’, Decimal(‘0E-10’)) #偶数数据写在engine1 (u’222’, Decimal(‘0E-10’)) (u’444’, Decimal(‘0E-10’)) (u’666’, Decimal(‘0E-10’)) (u’888’, Decimal(‘0E-10’)) >>> for row in engine2.execute(product_table.select()): …     print row … 2012-07-18 19:11:40,098 INFO sqlalchemy.engine.base.Engine SELECT product.sku, product.msrp FROM product 2012-07-18 19:11:40,099 INFO sqlalchemy.engine.base.Engine () (u’111’, Decimal(‘0E-10’)) #奇数数据写在engine1 (u’333’, Decimal(‘0E-10’)) (u’555’, Decimal(‘0E-10’)) (u’777’, Decimal(‘0E-10’)) (u’999’, Decimal(‘0E-10’)) >>> session.query(Product).all() 2012-07-18 19:12:36,130 INFO sqlalchemy.engine.base.Engine SELECT product.sku AS product_sku, product.msrp AS product_msrp FROM product 2012-07-18 19:12:36,130 INFO sqlalchemy.engine.base.Engine () 2012-07-18 19:12:36,131 INFO sqlalchemy.engine.base.Engine SELECT product.sku AS product_sku, product.msrp AS product_msrp FROM product 2012-07-18 19:12:36,131 INFO sqlalchemy.engine.base.Engine () [<Product 123>, <Product 456>, <Product 789>, <Product 000>, <Product 222>, <Product 444>, <Product 666>, <Product 888>, <Product 111>, <Product 333>, <Product 555>, <Product 777>, <Product 999>]

from sqlalchemy import create_engine from sqlalchemy.orm import mapper, sessionmaker from datetime import datetime from sqlalchemy import Numeric,Table, MetaData, Column, ForeignKey, Integer, String, Unicode, DateTime from sqlalchemy import types from sqlalchemy.databases import sqlite engine1 = create_engine(‘sqlite://’) engine2 = create_engine(‘sqlite://’) metadata = MetaData() product_table = Table( ‘product’, metadata, Column(‘sku’, String(20), primary_key=True), Column(‘msrp’, Numeric)) product_summary_table = Table( ‘product_summary’, metadata, Column(‘sku’, String(20), ForeignKey(‘product.sku’), primary_key=True), Column(‘name’, Unicode(255)), Column(‘description’, Unicode)) product_table.create(bind=engine1) product_summary_table.create(bind=engine2) stmt = product_table.insert() engine1.execute( stmt, [dict(sku=”123”, msrp=12.34), dict(sku=”456”, msrp=22.12), dict(sku=”789”, msrp=41.44)]) stmt = product_summary_table.insert() engine2.execute( stmt, [dict(sku=”123”, name=”Shoes”, description=”Some Shoes”), dict(sku=”456”, name=”Pants”, description=”Some Pants”), dict(sku=”789”, name=”Shirts”, description=”Some Shirts”)])