关于PostgreSQL绑定变量的问题

Question

我在看这个问题的时候，决定试试使用绑定变量。我用的是

sql = 'insert into abc2 (interfield,textfield) values (%s,%s)'
a = time.time()
for i in range(10000):
    #just a wrapper around cursor.execute
    db.executeUpdateCommand(sql,(i,'test'))

db.commit()

还有

sql = 'insert into abc2 (intfield,textfield) values (%(x)s,%(y)s)'
for i in range(10000):
    db.executeUpdateCommand(sql,{'x':i,'y':'test'})

db.commit()

从上面两个方法的耗时来看，似乎没有太大的时间差别。实际上，第二种方法反而花的时间更长。如果我哪里搞错了，请大家指正一下。我这里用的是psycopg2。

Answer 1

据我所知，psycopg2这个库从来没有支持服务器端的参数绑定（在Oracle中叫“绑定变量”）。不过，现在的PostgreSQL版本在协议层面上是支持这个功能的，使用的是预处理语句，但只有少数连接库会利用这个功能。Postgres的维基上有提到这个内容，可以在这里查看。以下是一些你可以尝试的连接库：（我自己没有用过这些。）

• pg8000
• python-pgsql
• py-postgresql

如果你在使用DB-API的调用，建议考虑使用cursor.executemany()，而不是一次又一次地调用cursor.execute()。

另外，在PostgreSQL中，将参数绑定到服务器的查询中（而不是在连接器中）并不总是会更快。可以参考这个常见问题解答。

Answer 2

---

重要更新： 我查看了所有在FreeBSD端口中连接PostgreSQL的Python库的源代码，可以告诉你，只有py-postgresql真正支持预处理语句！不过它只适用于Python 3及以上版本。

还有py-pg_queue这个库也挺有趣，它实现了官方的数据库协议（支持Python 2.4及以上）。

---

你可能错过了关于预处理语句的问题，想要尽可能多地使用它们。“绑定变量”是更好的形式，让我们来看一下：

sql_q = 'insert into abc (intfield, textfield) values (?, ?)'  # common form 
sql_b = 'insert into abc2 (intfield, textfield) values (:x , :y)' # should have driver and db support

所以你的测试应该是这样的：

sql = 'insert into abc2 (intfield, textfield) values (:x , :y)'
for i in range (10000):
    cur.execute(sql, x=i, y='test')

或者这样：

def _data(n):
    for i in range (n):
         yield (i, 'test')
sql = 'insert into abc2 (intfield, textfield) values (? , ?)'    
cur.executemany(sql, _data(10000))

等等。

更新： 我刚刚发现了一个有趣的例子，它展示了如何透明地将SQL查询替换为预处理语句，并使用%(name)s。

Answer 3

在Postgresql中，这些查询是等价的。

“Bind”是Oracle的术语。当你使用它时，它会保存查询的执行计划，这样下次执行时会稍微快一点。Postgres中的prepare做的就是这个事情。

http://www.postgresql.org/docs/current/static/sql-prepare.html

psycopg2支持一种内部的“bind”，但不是用prepare，而是通过cursor.executemany()和cursor.execute()来实现。

(不过在Postgres的人面前不要叫它bind，应该叫它prepare，不然他们可能不明白你在说什么：)