postgresql 关于postgresql绑定变量的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4263508/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
question about postgresql bind variables
提问by goh
I was looking at the questionand decided to try using the bind variables. I use
我正在查看问题并决定尝试使用绑定变量。我用
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()
and
和
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()
Looking at the time taken for the two sets, above it seems like there isn't much time difference. In fact, the second one takes longer. Can someone correct me if I've made a mistake somewhere? using psycopg2 here.
看两集所用的时间，上面好像没有太大的时差。事实上，第二个需要更长的时间。如果我在某处犯了错误，有人可以纠正我吗？在这里使用 psycopg2。
采纳答案by nate c
The queries are equivalent in Postgresql.
查询在 Postgresql 中是等效的。
Bind is oracle lingo. When you use it will save the query plan so the next execution will be a little faster. preparedoes the same thing in Postgres.
绑定是 Oracle 行话。当您使用它时，它会保存查询计划，以便下次执行会更快一点。prepare在 Postgres 中做同样的事情。
http://www.postgresql.org/docs/current/static/sql-prepare.html
http://www.postgresql.org/docs/current/static/sql-prepare.html
psycopg2 supports an internal 'bind', not preparewith cursor.executemany()and cursor.execute()
psycopg2支撑一个内部“绑定”，而不是prepare与cursor.executemany()和cursor.execute()
(But don't call it bind to pg people. Call it prepare or they may not know what you mean:)
（但不要称之为绑定到 pg 人。称之为准备或他们可能不知道你的意思:)
回答by Eir Nym
IMPORTANT UPDATE: I've seen into source of all python libraries to connect to PostgreSQL in FreeBSD ports and can say, that only py-postgresqldoes real prepared statements! But it is Python 3+ only.
重要更新：我已经看到了在 FreeBSD 端口中连接到 PostgreSQL 的所有 python 库的源代码，并且可以说，只有py-postgresql执行真正的准备语句！但它只是 Python 3+。
also py-pg_queue is funny lib implementing official DB protocol (python 2.4+)
py-pg_queue 也是一个有趣的库，实现了官方数据库协议（python 2.4+）
You've missed answer for that question about prepared statements to use as many as possible. "Binded variables" are better form of this, let's see:
您错过了关于尽可能多地使用准备好的语句的问题的答案。“绑定变量”是更好的形式，让我们看看：
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
so your test should be this:
所以你的测试应该是这样的：
sql = 'insert into abc2 (intfield, textfield) values (:x , :y)'
for i in range (10000):
cur.execute(sql, x=i, y='test')
or this:
或这个：
def _data(n):
for i in range (n):
yield (i, 'test')
sql = 'insert into abc2 (intfield, textfield) values (? , ?)'
cur.executemany(sql, _data(10000))
and so on.
等等。
UPDATE:I've just found interest reciplehow to transparently replace SQL queries with prepared and with usage of %(name)s
更新：我刚刚发现了一个有趣的方法，即如何使用准备好的和使用 %(name)s 透明地替换 SQL 查询
回答by ?s??o?
As far as I know, psycopg2 has never supported server-side parameter binding ("bind variables" in Oracle parlance). Current versions of PostgreSQL do support it at the protocol level using prepared statements, but only a few connector libraries make use of it. The Postgres wiki notes this here. Here are some connectors that you might want to try: (I haven't used these myself.)
据我所知，psycopg2 从未支持服务器端参数绑定（Oracle 用语中的“绑定变量”）。当前版本的 PostgreSQL 确实使用准备好的语句在协议级别支持它，但只有少数连接器库使用它。Postgres wiki在此处记录了这一点。以下是您可能想尝试的一些连接器：（我自己没有使用过这些连接器。）
As long as you're using DB-API calls, you probably ought to consider cursor.executemany() instead of repeatedly calling cursor.execute().
只要您使用 DB-API 调用，您就可能应该考虑 cursor.executemany() 而不是重复调用 cursor.execute()。
Also, binding parameters to their query in the server (instead of in the connector) is not always going to be faster in PostgreSQL. Note this FAQ entry.
此外，将参数绑定到服务器中的查询（而不是连接器中）在 PostgreSQL 中并不总是更快。请注意此常见问题解答条目。
