Thread: Which Python library - psycopg2 or pygresql?
So I thought, "lets learn a bit of Python", and I stumbled upon a choice of these two libraries. Whch would you suggest? How do they differ? By the looks of descriptions I am slightly inclined towards psycopg2, but I would feel better if I talked with people who actually used these libraries. Regards, Dawid PS: I don't want to start a flame war! I just feel I need a bit of knowledge-push to get me going. ;-)
On Apr 15, 2008, at 10:27 AM, Dawid Kuroczko wrote: > So I thought, "lets learn a bit of Python", and I stumbled upon > a choice of these two libraries. Whch would you suggest? > How do they differ? > > By the looks of descriptions I am slightly inclined towards > psycopg2, but I would feel better if I talked with people > who actually used these libraries. Most definitely psycopg2, it's pretty much the standard dbapi compliant Postgres driver library for Python. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
Dawid Kuroczko wrote: > So I thought, "lets learn a bit of Python", and I stumbled upon > a choice of these two libraries. Whch would you suggest? > How do they differ? > Well, pygresql seems unmaintained since mid 2006 and the psycopg2 site is currently and regularly down. Neither inspires confidence. As to differences, here's one: Using pygresql ... result=db.query('select false as booltest') boolean_result = result.dictresult()[0]['booltest'] print boolean_result if boolean_result: print "The result was true" else: print "The result was false" This prints: f The result was true Huh? Seems that pygresql treats boolean as character 't' or 'f', python evaluates both as 'true' and hilarity ensues. (Yes, I just spent some "quality time" tracking a bug in a script that used pygresql and had a loop with a test of a boolean column.) Using psycopg2: ... cur.execute('select false as booltest') boolean_result = cur.fetchall()[0][0] print boolean_result if boolean_result: print "The result was true" else: print "The result was false" This prints: False The result was false There was a brief discussion at the PG users group last week and the bias was toward psycopg2. Cheers, Steve
Just a side note: pyPgSQL is broken with standard_conforming_strings = on (see groups.google.com/group/trac-dev) 2008/4/15, Dawid Kuroczko <qnex42@gmail.com>: > So I thought, "lets learn a bit of Python", and I stumbled upon > a choice of these two libraries. Whch would you suggest? > How do they differ? > > By the looks of descriptions I am slightly inclined towards > psycopg2, but I would feel better if I talked with people > who actually used these libraries. > > Regards, > Dawid > > PS: I don't want to start a flame war! I just feel I need a bit > of knowledge-push to get me going. ;-) > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Filip Rembiałkowski
On Tue, Apr 15, 2008 at 09:21:19AM -0700, Steve Crawford wrote: >> So I thought, "lets learn a bit of Python", and I stumbled upon >> a choice of these two libraries. Whch would you suggest? >> How do they differ? >> > Well, pygresql seems unmaintained since mid 2006 and the psycopg2 site > is currently and regularly down. Neither inspires confidence. The psycopg2 site is (supposedly) actively being worked on since the 2.0.7 release yesterday :-) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Tue, 15 Apr 2008, Dawid Kuroczko wrote: > So I thought, "lets learn a bit of Python", and I stumbled upon > a choice of these two libraries. Whch would you suggest? Use psycopg2. It's better maintained and has a better feature set at this point. I would specifically recommend that you look at all the example programs that come with the software. There's lots of stuff there that is easier to pick up that way than by reading the documentation, if it's even covered in the docs at all. > PS: I don't want to start a flame war! Unless D'Arcy suddenly appears with a new pygresql rev that blows everyone away I think this is a safe topic. Not impossible but not too likely I think. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On 15 Apr, 17:53, e...@myemma.com (Erik Jones) wrote: > On Apr 15, 2008, at 10:27 AM, Dawid Kuroczko wrote: > > > By the looks of descriptions I am slightly inclined towards > > psycopg2, but I would feel better if I talked with people > > who actually used these libraries. > > Most definitely psycopg2, it's pretty much the standard dbapi > compliant Postgres driver library for Python. One caveat: psycopg2 doesn't (or didn't) use cursors in a transparent fashion like pyPgSQL does. If you're traversing potentially large data sets, this will mean that psycopg2 will download all the result data into the client process unless you start introducing explicit DECLARE CURSOR statements in all the right places. Although this might not be an issue if you're determined to only support PostgreSQL and psycopg2, it's worth noting that the behaviour is somewhat counter-intuitive from the perspective of people with experience of other database systems: attempting to fetch a single row (or a limited number of rows) may cause you to discover that the client has acquired all of them and has taken over the job of feeding them to your code, instead of leaving that to the database system. Admittedly, the cause of the lack of such support in psycopg2 is the uncertainty regarding cursor-capable statements in PostgreSQL: pyPgSQL uses potentially awkward and fairly simplistic techniques to guess whether the issued statement can be used with cursors, and I can understand that the psycopg2 developers want to steer away from such practices. Paul
On Thu, Apr 17, 2008 at 04:06:57AM -0700, Paul Boddie wrote: > One caveat: psycopg2 doesn't (or didn't) use cursors in a transparent > fashion like pyPgSQL does. If you're traversing potentially large data > sets, this will mean that psycopg2 will download all the result data > into the client process unless you start introducing explicit DECLARE > CURSOR statements in all the right places. Although this might not be > an issue if you're determined to only support PostgreSQL and psycopg2, > it's worth noting that the behaviour is somewhat counter-intuitive > from the perspective of people with experience of other database > systems: attempting to fetch a single row (or a limited number of > rows) may cause you to discover that the client has acquired all of > them If one wants to operate on one/a range of row(s) but the code fetches "all" rows (for various values of all) then I'd suspect there's something missing in the SQL statement, say, a LIMIT or appropriate WHERE conditions - regardless of whether a cursor is used or not. If you refer to whether server-side cursors are used one must explicitly request them from psycopg2 by using the "name" argument to the connection.Cursor() call. Combine that with a Python generator and one should end up with truly on-demand single-row fetching. Unfortunately, I am not entirely sure how and when psycopg2 uses (database) cursors when no name argument is supplied. IMO the cursor concept of the DB-API is broken anyhow - everything is forced to go through a (DB-API) cursor no matter whether a database-side cursor would be wanted or not and there's no provision for controlling the latter via the API itself. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 18 Apr, 14:12, Karsten.Hilb...@gmx.net (Karsten Hilbert) wrote: > > If one wants to operate on one/a range of row(s) but the > code fetches "all" rows (for various values of all) then I'd > suspect there's something missing in the SQL statement, say, > a LIMIT or appropriate WHERE conditions - regardless of > whether a cursor is used or not. But if you want to process all of the rows, and you don't want the client to suck them all down at once, then you need to use the database system's cursor support. > If you refer to whether server-side cursors are used one > must explicitly request them from psycopg2 by using the > "name" argument to the connection.Cursor() call. Combine > that with a Python generator and one should end up with > truly on-demand single-row fetching. As I noted, the problem is arguably shared between the database system (because cursors don't work with certain statements that you might use, and there's no way of finding out without trying) and the database adapter (because it doesn't try to support the behaviour implied by the DB-API). Inventing names for cursors, although tedious, is the easy part in all this. > Unfortunately, I am not entirely sure how and when psycopg2 > uses (database) cursors when no name argument is supplied. It doesn't. > IMO the cursor concept of the DB-API is broken anyhow - > everything is forced to go through a (DB-API) cursor no > matter whether a database-side cursor would be wanted or not > and there's no provision for controlling the latter via the > API itself. Well, the DB-API doesn't seem to be moving in any real direction these days, anyway. I've wanted and even proposed code for a single parameter standard, and the progress on that matter has been glacial: it's too controversial to do what ODBC and JDBC have been doing for years, apparently. Still, I don't really see that doing the equivalent of a cursor.fetchall for something like cursor.fetchone is appropriate when "all" might be millions of rows, but that's just my view. Paul
On Tuesday 15 April 2008 10:27:14 am Dawid Kuroczko wrote: > Whch would you suggest? > How do they differ? Sorry to bring this back up (I try to keep up with this list but it's hard!), but isn't licensing a concern? If I understand correctly, pygresql is BSD-licensed, but depends on MX which is GPL *incompatible* whereas psycopg is GPL. If that is the case, which one you choose depends strictly on the license of your project. If GPL, use psychopg. If anything else, use pygresql. Is that accurate?
Micah, psycopg2 has a license extensions which allows basically to use psycopg2 binaries without distributing source code as long as there are no modifications to the psycopg2 C code best wishes Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!