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.com<javascript:> > . > > 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.
