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.
