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