Prepared Statements in Postgresql

[mozillalives]

Hello Everyone,

I am new to both sqlalchemy and elixir, but I have been using them for
the past couple of weeks and I really like them. But I have a question
about prepared statements for Postgresql.

For one specific application, I am doing a bunch of inserts
(200,000+). From what I can tell, it looks like these are not prepared
statements. I rewrote the code to issue prepared statements and this
cuts the insertion time in half, but the code is crude. My question's
are:

Is there a way to tell sqlalchemy or the engine (which would be
psycopg2, correct?) to use prepared statements?

I've noticed some opinions online indicating that psycopg2 does not
have prepared statement support (e.g. -
http://www.cerias.purdue.edu/site/blog/post/beware_sql_injections_due_to_missing_prepared_statement_support/)
- can I plug another engine into sqlalchemy that does?

If I can't do any of the above and just need to prepare the statements
manually, is there at least a method in sqlalchemy to properly quote
my data before sending it to postgres?

Thanks,
Phil

[Michael Bayer]

mozillalives wrote:
> Hello Everyone,
>
> I am new to both sqlalchemy and elixir, but I have been using them for
> the past couple of weeks and I really like them. But I have a question
> about prepared statements for Postgresql.
>
> For one specific application, I am doing a bunch of inserts
> (200,000+). From what I can tell, it looks like these are not prepared
> statements. I rewrote the code to issue prepared statements and this
> cuts the insertion time in half, but the code is crude. My question's
> are:

how did you use prepared statements in Python if you don't know that
psycoopg2 uses prepared statements ? was this in another language or did
you implement a raw socket connection to your database ?

>
> Is there a way to tell sqlalchemy or the engine (which would be
> psycopg2, correct?) to use prepared statements?

to efficiently execute the same statement many times, use the
"executemany" style of execution - the tutorial describes this at
http://www.sqlalchemy.org/docs/05/sqlexpression.html#executing-multiple-statements
. I don't think that psycopg2 actually uses "prepared" statements for
this purpose but I am not sure. The DBAPI executemany() method is used.

>
> I've noticed some opinions online indicating that psycopg2 does not
> have prepared statement support (e.g. -
> http://www.cerias.purdue.edu/site/blog/post/beware_sql_injections_due_to_missing_prepared_statement_support/)

the comment at the bottom of that post ultimately references a psycopg2
message from 2007 so you'd need to ask the psycopg2 folks for updated
information. However psycopg2 can do an "executemany" with great
efficiency as it is using methodologies for which you'd have to ask them,
so if they don't use PG's actual "prepared" mechanism, its probably
unnecessary. psycopg2 is an extremely mature and high performing product.

> - can I plug another engine into sqlalchemy that does?

there's the pg8000 engine which may or may not do this. But its written
in pure python, is not as fast as psycopg2, and is very new and not widely
used since its author doesn't seem to promote it very much (but it is a
very well written library).

>
> If I can't do any of the above and just need to prepare the statements
> manually, is there at least a method in sqlalchemy to properly quote
> my data before sending it to postgres?

Despite some of the fud-like links mentioned on that blog, SQLAlchemy, as
it says on the website since the day we launched 5 years ago, always uses
bind parameters, in all cases, for all literal values, everywhere. We do
not and have never "quoted" anything within SQLA as that is left up to the
services provided by the DBAPI. DBAPI does not have "prepared statement"
API. It has "executemany()", for which the underlying implementation may
or may not use prepared statements + server-level bind processing as an
implementation detail. Psycopg2 handles the quoting in this case.
cx_oracle, OTOH, uses Oracle's native data binding facilities provided by
OCI. DBAPI abstracts this detail away.

>
> Thanks,
> Phil
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>

[mozillalives]

Thanks for your quick response Michael.

To answer your question, this is how I was issuing the queries

conn.execute("PREPARE insert_statement(text) AS ...")
conn.execute("EXECUTE insert_statement('%s')" % val)

And I'm sorry if it seemed that I was attacking sqlalchemy, I just
wasn't sure what it did and how it works with psycopg2. From what you
wrote it seems that my question is more for the psycopg2 group than
here.

Thanks for helping me out.

Phil

On Jan 15, 12:16 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> mozillalives wrote:
> > Hello Everyone,
>
> > I am new to both sqlalchemy and elixir, but I have been using them for
> > the past couple of weeks and I really like them. But I have a question
> > about prepared statements for Postgresql.
>
> > For one specific application, I am doing a bunch of inserts
> > (200,000+). From what I can tell, it looks like these are not prepared
> > statements. I rewrote the code to issue prepared statements and this
> > cuts the insertion time in half, but the code is crude. My question's
> > are:
>
> how did you use prepared statements in Python if you don't know that
> psycoopg2 uses prepared statements ?  was this in another language or did
> you implement a raw socket connection to your database ?
>
>
>
> > Is there a way to tell sqlalchemy or the engine (which would be
> > psycopg2, correct?) to use prepared statements?
>
> to efficiently execute the same statement many times, use the

> "executemany" style of execution - the tutorial describes this athttp://www.sqlalchemy.org/docs/05/sqlexpression.html#executing-multip...

> .   I don't think that psycopg2 actually uses "prepared" statements for
> this purpose but I am not sure.  The DBAPI executemany() method is used.
>
>
>
> > I've noticed some opinions online indicating that psycopg2 does not
> > have prepared statement support (e.g. -

> >http://www.cerias.purdue.edu/site/blog/post/beware_sql_injections_due...)

[Michael Bayer]

mozillalives wrote:
> Thanks for your quick response Michael.
>
> To answer your question, this is how I was issuing the queries
>
> conn.execute("PREPARE insert_statement(text) AS ...")
> conn.execute("EXECUTE insert_statement('%s')" % val)
>
> And I'm sorry if it seemed that I was attacking sqlalchemy, I just
> wasn't sure what it did and how it works with psycopg2. From what you
> wrote it seems that my question is more for the psycopg2 group than
> here.
>
> Thanks for helping me out.

Oh I wasn't upset with you, I was annoyed at the somewhat confused nature
of the comments in that blog post. Prepared statements are fine but they
are not necessary in order to use bind parameters. I think this
confusion arises based on people's experience with JDBC which does have
this requirement.

[Tony Locke]

Hi, just to confirm, the executemany() method in pg8000 does use prepared statements.

Cheers,

Tony.

[Claudio Freire]

On Wed, Feb 12, 2014 at 5:03 PM, Tony Locke <tlo...@tlocke.org.uk> wrote:
>>
>> I've noticed some opinions online indicating that psycopg2 does not
>> have prepared statement support (e.g. -
>>
>> http://www.cerias.purdue.edu/site/blog/post/beware_sql_injections_due_to_missing_prepared_statement_support/)
>
> the comment at the bottom of that post ultimately references a psycopg2
> message from 2007 so you'd need to ask the psycopg2 folks for updated
> information. However psycopg2 can do an "executemany" with great
> efficiency as it is using methodologies for which you'd have to ask them,
> so if they don't use PG's actual "prepared" mechanism, its probably
> unnecessary. psycopg2 is an extremely mature and high performing product.

What it doesn't support is libpq's wire protocol for prepared
statements. But you can prepare and execute statements by issuing the
corresponding SQL (that will use the wire protocol for SQL execution,
which is a tad less efficient but still more efficient than separate
queries).

psycopg2's executemany isn't much more sophisticated than multiple
separate queries since it internally does exactly that. It may be a
tad faster since it's done in C, but I doubt the difference is
significant.

But, if you want an executemany that works in psycopg2 as it would in
pg8000, you can PREPARE and then executemany the EXECUTE queries.

[Michael Bayer]

On Feb 12, 2014, at 4:07 PM, Claudio Freire <klauss...@gmail.com> wrote:

>
> But, if you want an executemany that works in psycopg2 as it would in
> pg8000, you can PREPARE and then executemany the EXECUTE queries.

I’ve worked a lot with pg8000 including that I’ve given them very broad architectural changes towards the goal of greater performance, but still as a pure Python driver unless you’re using pypy, it still has dramatically more overhead than psycopg2 on the Python side. So it’s kind of a tossup if “prepared + pure Python” vs. “non-prepared but very optimized C” is better in individual cases.