Re: [sqlalchemy] Prepared Statements in Postgresql
Hi, just to confirm, the executemany() method in pg8000 does use prepared statements. Cheers, Tony. On Friday, 15 January 2010 17:16:09 UTC, Michael Bayer 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 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.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Prepared Statements in Postgresql
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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Prepared Statements in Postgresql
On Feb 12, 2014, at 4:07 PM, Claudio Freire klaussfre...@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. signature.asc Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] Prepared Statements in Postgresql
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 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Prepared Statements in Postgresql
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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
