python之如何使用带有 PostgreSQL 的 SQLAlchemy 选择共享

我正在尝试 SELECT FOR SHARE 表中的一组行，以便它们被锁定直到事务结束。我正在使用 SQLAlchemy 0.7.9 在 PostgreSQL 9.1.6 数据库中执行此操作。这是有问题的 python 代码:

NUM_TERMS = 10 
conn = engine.connect() 
get_terms = select([search_terms.c.term_id, search_terms.c.term], 
                   and_(search_terms.c.lock==False, 
                   search_terms.c.status==False), 
                   order_by=search_terms.c.term, 
                   limit=NUM_TERMS, for_update="read") 
trans = conn.begin() 
try: 
    search_terms = conn.execute(get_terms).fetchall() 
    for term in search_terms: 
        lock_terms = update(search_terms).\ 
                     where(search_terms.c.term_id==term.term_id).\ 
                     values(lock=True) 
        conn.execute(lock_terms) 
    if trans.commit(): 
        <do things with the search terms> 
except: 
    trans.rollback() 

问题是上面的选择代码生成的 SQL 查询不是用于共享，而是用于更新:

SELECT search_terms.term_id, search_terms.term 
FROM search_terms 
WHERE search_terms.lock = :lock_1 AND search_terms.status = :status_1      
ORDER BY search_terms.term 
LIMIT :param_1 FOR UPDATE 

根据SQLAlchemy API docs ，在“for_update”参数说明下:

With the Postgresql dialect, the values “read” and "read_nowait" translate to FOR SHARE and FOR SHARE NOWAIT, respectively.

按照上面的说法，编译出来的SQL语句应该是FOR SHARE，其实不是。我的代码哪里出错了？

请您参考如下方法:

无法重现:

from sqlalchemy import * 
 
m = MetaData() 
t = Table('t', m, Column('x', Integer)) 
 
s = select([t], for_update="read") 
 
from sqlalchemy.dialects import postgresql 
print s.compile(dialect=postgresql.dialect()) 
 
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) 
with e.begin() as conn: 
    m.create_all(conn) 
    conn.execute(s) 

输出显示，无论是作为独立编译还是在 Postgresql 对话中，我们都得到 FOR SHARE。在 0.7.9 和 0.8.0b2 中测试。如果您可以提供完整的可运行测试用例，那可能会更清楚:

SELECT t.x  
FROM t FOR SHARE 
2012-12-20 23:53:33,670 INFO sqlalchemy.engine.base.Engine select version() 
2012-12-20 23:53:33,671 INFO sqlalchemy.engine.base.Engine {} 
2012-12-20 23:53:33,672 INFO sqlalchemy.engine.base.Engine select current_schema() 
2012-12-20 23:53:33,672 INFO sqlalchemy.engine.base.Engine {} 
2012-12-20 23:53:33,674 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 
2012-12-20 23:53:33,674 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s 
2012-12-20 23:53:33,675 INFO sqlalchemy.engine.base.Engine {'name': u't'} 
2012-12-20 23:53:33,676 INFO sqlalchemy.engine.base.Engine SELECT t.x  
FROM t FOR SHARE 
2012-12-20 23:53:33,676 INFO sqlalchemy.engine.base.Engine {} 
2012-12-20 23:53:33,676 INFO sqlalchemy.engine.base.Engine COMMIT