SQLAlchemy黄金体验

session的生命期

引用

Web Server          Web Framework        User-defined Controller Call
--------------      --------------       ------------------------------
web request    ->
                    call controller ->   # call Session().  this establishes a new,
                                         # contextual Session.
                                         session = Session()

                                         # load some objects, save some changes
                                         objects = session.query(MyClass).all()

                                         # some other code calls Session, it's the
                                         # same contextual session as "sess"
                                         session2 = Session()
                                         session2.add(foo)
                                         session2.commit()

                                         # generate content to be returned
                                         return generate_content()
                    Session.remove() <-
web response   <-

参考链接:
http://docs.sqlalchemy.org/en/latest/orm/session.html#sqlalchemy.orm.session.sessionmaker

遇到下边这个异常应该如何解决？

引用

TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30。

You are probably not removing the session after the request has been served.
Check out SQLAlchemy documentation on Session.remove.
使用完Seesion后，应该立即把它删除。
示例代码如下：

    
    def load(self, class_alias, filterExpr):
        klass = pyamf.load_class(class_alias).klass
        session = Schema().session
        result = session.query(klass).filter(filterExpr).all()
        session.remove()
        return result

参考链接：http://stackoverflow.com/questions/2314844/limiting-pyamf-flex-sessions-or-setting-a-timeout

数据库路径需要转成ascii码，否则回sqlalchemy会抛出下边的错误
sqlalchemy.exc.DBAPIError: (error) argument for 's' must be a string None None

如何在建表的时候为字段指定默认值？
使用PassiveDefault(),这是数据库级别的默认值。
如果希望默认值仅在Insert的时候使用的话，使用default

Field(Unicode(256), default=u'default name', nullable=False)

使用@hybrid_method时为啥SQLAlchemy抛出这么一个错误：sqlalchemy.exc.ArgumentError: filter() argument must be of type sqlalchemy.sql.ClauseElement or string
hybrid_method不要直接返回字符串或者布尔值，而是应该返回表达式，如：

return self.name == categoryName

过滤条件中,in_和contains的区别

引用

contains(other, **kwargs)
    Implement the ‘contains’ operator.
    In a column context, produces the clause LIKE '%<other>%'

引用

in_(other)
    Implement the in operator.
    In a column context, produces the clause a IN other. “other” may be a     tuple/list of column expressions, or a select() construct.

SQLAlchemy的children默认使用的List如何将其改为Dictionary?
通过为relationship设置collection_class属性，示例代码如下：

children = relationship("Category", lazy="joined", join_depth =1, backref = backref('parent', remote_side=[id]), collection_class=attribute_mapped_collection('name'),)

将小数保存到数据库时如何指定保存小数点后边的数字的个数？
通过设置Numeric的第2个参数,示例代码如下：

unitPrice = Column(Numeric(5, 2))