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

Search Discussions

  • Erik Jones at Apr 15, 2008 at 3:53 pm

    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
  • Paul Boddie at Apr 17, 2008 at 11:26 am

    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
  • Karsten Hilbert at Apr 18, 2008 at 12:12 pm

    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
  • Paul Boddie at Apr 18, 2008 at 6:56 pm

    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
  • Steve Crawford at Apr 15, 2008 at 4:21 pm

    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
  • Karsten Hilbert at Apr 15, 2008 at 5:16 pm

    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
  • Filip Rembiałkowski at Apr 15, 2008 at 4:38 pm
    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
  • Greg Smith at Apr 15, 2008 at 6:42 pm

    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
  • Micah Yoder at Apr 24, 2008 at 4:43 pm

    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?
  • Harald Armin Massa at Apr 24, 2008 at 4:59 pm
    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!

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedApr 15, '08 at 3:28p
activeApr 24, '08 at 4:59p
posts11
users9
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase