10

I was looking at the question and 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.

Improve this question

3 Answers 3

Reset to default
11

The queries are equivalent in Postgresql.

Bind is oracle lingo. When you use it will save the query plan so the next execution will be a little faster. prepare does the same thing in Postgres.

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

psycopg2 supports an internal 'bind', not prepare with cursor.executemany() and cursor.execute()

(But don't call it bind to pg people. Call it prepare or they may not know what you mean:)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

7

IMPORTANT UPDATE : I've seen into source of all python libraries to connect to PostgreSQL in FreeBSD ports and can say, that only py-postgresql does real prepared statements! But it is Python 3+ only.

also py-pg_queue is funny lib implementing official DB protocol (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 reciple how to transparently replace SQL queries with prepared and with usage of %(name)s

Improve this answer

6 Comments

@Eir, i think in psycopg2 bind variables are specified like %(name)s, no?
I will be very carefil with this form of SQL and possibly will not use it in production.
@eir, think I was wrong with the my question. They both does the same stuff, but like what @nate and @forest has said, its specific to oracle.
All of the examples here do essentially the same thing in psycopg2: They pass a query and its parameters to the connector, which combines them and sends the resulting strings to the server. (To be fair, it's hard to tell this is happening unless you examine psycopg2's source code.) This is not what happens when you use "bind variables" (an Oracle phrase). For "bind variable" behavior, you need to send the query and parameters to the server separately so the server's query planner has the opportunity to optimize with or without specific parameter values.
'bind variables' and 'prepared statements' are same
|
3

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.)

As long as you're using DB-API calls, you probably ought to consider cursor.executemany() instead of repeatedly calling 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.

Improve this answer

7 Comments

cursor.executemany() is 'bind' for pyscopg2 initd.org/psycopg/docs/cursor.html#cursor.executemany
You would think so, but if you look at the source code you'll see that psycopg2 uses the same code for execute() and executemany(). It does its own parameter binding within the library, rather than preparing a statement and sending it to the server for binding.
Therefore, psycopg2's executemany() might be marginally faster than execute() by remaining in compiled code instead of switching back to the python interpreter many times, but it's still not doing what is implied when you use the Oracle term "bind variables."
Unfortunately, the psycopg2 docs and the PostgreSQL docs use the word "prepare" to mean different things, which is confusing. postgresql.org/docs/8.0/interactive/libpq-exec.html
This dba-oracle.com/t_bind_variables.htm says that oracle is preparing each statement and keeping track of it by a hash index of the string. So then you would have to call prepare manually in pg for equivalency, right?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.