sqlalchemy如何分表

       

背景

话说sqlalchemy真是一个非常好用的库，python orm基本上是舍我其谁了，文档还非常全面，基本上没有什么硬伤，现在也冲出了1.0版本，未来更加值得期待。
我最早用django orm，不过很快就觉得很多功能不够用，我当时用的版本是1.3.1，没有bulk insert也没有锁，没有这两个功能，好多应用就没法用django开发了。之后开始接触sqlalchemy，一直用到现在，总的体会是只有你想不到没有它做不到。

我们项目里有一个需求，就是数据按月分表，比如:2014年6月数据就存在record_201406表中, 其他月数据按此方法类推。这个需求如果是用sqlalchemy来获取数据，我们怎么做呢？

一般方法有什么问题？

一般情况下，我们很自然想到使用如下方法：

1 2 3 4 5

class RecodeDao_201406(Base): __tablename__ = 'record_201406' id = Column(INT(11), primary_key=True) ...

或者简化点：

1 2 3 4

class RecodeDao_201406(Base): __table__ = Table('record_201406', Base.metadata, autoload=True)

这样实现确实没问题，但回到需求上，既然是按月分表，难不成我要每个月写一个这样的model？每月上次线？当然不行，那我们怎么解决呢？

官网解决方法，有什么问题？

有经验的同学可能发现，这个不就是水平sharding么？这么说不完全对，看一下sharding的wiki定义：

A database shard is a horizontal partition of data in a database or search engine. Each individual partition is referred to as a shard or database shard. Each shard is held on a separate database server instance, to spread load.

我们这个需求只涉及单数据库，就不算sharding了，可以称为partitioning（分区），然而强大的sqlalchemy这两个情况都考虑到了，并且官网都提供了example，我们挑对应场景的partitioning出来看看:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class TBase(object): """Base class is a 'mixin'. Guidelines for declarative mixins is at: http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#mixin-classes """ id = Column(Integer, primary_key=True) data = Column(String(50)) def __repr__(self): return "%s(data=%r)" % ( self.__class__.__name__, self.data ) class T1Foo(TBase, Base): __tablename__ = 't1' class T2Foo(TBase, Base): __tablename__ = 't2' timestamp = Column(DateTime, default=func.now()) engine = create_engine('sqlite://', echo=True) Base.metadata.create_all(engine) sess = sessionmaker(engine)() sess.add_all([T1Foo(data='t1'), T1Foo(data='t2'), T2Foo(data='t3'), T1Foo(data='t4')]) print sess.query(T1Foo).all() print sess.query(T2Foo).all()

使用了继承的方法，抽象的好，但我们之前的问题解决了吗？没有。还是需要预定义好所有表的model类，才能正确使用，迫不得已，我们只能自己想办法了。

函数方法解决

经过一番探索，我得出了如下方法:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

class_registry = {} DbBase = declarative_base(bind=engine, class_registry=class_registry) def get_model(modelname, tablename, metadata=DbBase.metadata): """ args: modelname:新model名，string类型 tablename:数据库中表名 usage: RecordDao = get_model("RecordDao_201406", "record_201406") """ if modelname not in class_registry: model = type(modelname, (DbBase,), dict( __table__ = Table(tablename, metadata, autoload=True) )) else: model = class_registry[modelname] return model

每次想获取对应月表数据的model，调用get_model方法即可。这个方法一直沿用到现在，虽然有点丑陋，但却是解决了以上问题。直到sqlalchemy 0.9.1版本推出Automap

Automap方法

sqlalchemy文档完备，具体可点击Automap，它可以自动映射数据库的表，通过数据表名映射model，简单直接，实现起来如下：

1 2 3 4 5 6 7 8

from sqlalchemy.ext.automap import automap_base AutoBase = automap_base() # reflect the tables AutoBase.prepare(engine, reflect=True) tablename = "record_201406" RecordDao = getattr(AutoBase.classes, tablename)

这样就可以了，很清晰。但是这个方法有一个缺点，Automap的映射虽然是自动的，但是只有在启动的时候生效，也就是说如果新建一个数据表，而没有告诉Automap，那这个表是找不到的。在实际使用中，可以捕获AttributeError异常，并再次调用AutoBase.prepare(engine, reflect=True) 刷新映射关系。