使用sqlalchemy如何按请求动态绑定到数据库引擎

Question

我有一个基于Pylons的网页应用，它通过Sqlalchemy（版本0.5）连接到Postgres数据库。为了安全起见，我没有像大多数简单网页应用那样使用一个通用的Postgres用户（比如“webapp”），而是要求用户输入自己的Postgres用户名和密码，然后用这些信息来建立连接。这样做可以充分利用Postgres的安全性。

事情变得更加复杂的是，我们需要连接两个不同的数据库。虽然它们现在在同一个Postgres集群中，但将来可能需要迁移到不同的主机上。

我们使用了sqlalchemy的声明式包，不过我觉得这和问题没有太大关系。

大多数关于sqlalchemy的例子展示了简单的方法，比如在应用启动时只设置一次Metadata，使用一个通用的数据库用户名和密码，这个信息在整个网页应用中都可以使用。通常是通过Metadata.bind = create_engine()来实现，有时甚至在数据库模型文件的模块级别就设置好了。

我的问题是，如何能在用户登录后再建立连接，并且（当然）在后续的请求中重用这些连接，或者用相同的凭证重新建立连接。

我们觉得这个方法是可行的，但我不仅不确定它的安全性，还觉得在这种情况下看起来有点繁琐。

在BaseController的__call__方法中，我们从网页会话中获取用户名和密码，然后为每个数据库调用一次sqlalchemy的create_engine()，接着调用一个例程，这个例程会重复调用Session.bind_mapper()，每个可能在这些连接中引用的表都要调用一次，尽管每个请求通常只会引用一两个表。大致上是这样的：

# in lib/base.py on the BaseController class
def __call__(self, environ, start_response):

    # note: web session contains {'username': XXX, 'password': YYY}
    url1 = 'postgres://%(username)s:%(password)s@server1/finance' % session
    url2 = 'postgres://%(username)s:%(password)s@server2/staff' % session

    finance = create_engine(url1)
    staff = create_engine(url2)
    db_configure(staff, finance)  # see below
    ... etc

# in another file

Session = scoped_session(sessionmaker())

def db_configure(staff, finance):
    s = Session()

    from db.finance import Employee, Customer, Invoice
    for c in [
        Employee,
        Customer,
        Invoice,
        ]:
        s.bind_mapper(c, finance)

    from db.staff import Project, Hour
    for c in [
        Project,
        Hour,
        ]:
        s.bind_mapper(c, staff)

    s.close()  # prevents leaking connections between sessions?

所以create_engine()的调用在每个请求中都会发生……我能理解这样做是有必要的，连接池可能会缓存这些连接并合理处理。

但是在每个请求中对每个表都调用Session.bind_mapper()？这似乎有更好的方法。

显然，由于我们希望有强大的安全性，所以我们不想让高安全性用户建立的连接在后续请求中被低安全性用户意外使用。

Answer 1

我建议你看看连接池，看看能不能为每个用户创建一个连接池。这样的话，当用户的会话过期时，你可以用dispose()来处理这个连接池。

Answer 2

把全局对象（比如映射器、元数据）绑定到用户特定的连接上，这样做并不好。同时，使用范围会话也不是个好主意。我建议每次请求都创建一个新的会话，并配置它使用用户特定的连接。下面的示例假设你为每个数据库使用不同的元数据对象：

binds = {}

finance_engine = create_engine(url1)
binds.update(dict.fromkeys(finance_metadata.sorted_tables, finance_engine))
# The following line is required when mappings to joint tables are used (e.g.
# in joint table inheritance) due to bug (or misfeature) in SQLAlchemy 0.5.4.
# This issue might be fixed in newer versions.
binds.update(dict.fromkeys([Employee, Customer, Invoice], finance_engine))

staff_engine = create_engine(url2)
binds.update(dict.fromkeys(staff_metadata.sorted_tables, staff_engine))
# See comment above.
binds.update(dict.fromkeys([Project, Hour], staff_engine))

session = sessionmaker(binds=binds)()