psycopg2 or pygresql?

Discussion in 'Python' started by exhuma.twn, Sep 19, 2007.

  1. exhuma.twn

    exhuma.twn Guest

    Plain and simple. What would you use?

    So far I have written everything with psycopg2. One thing that annoys
    me is that I cannot easily access the column names from a query. I
    know that this is not part of the DBAPI2 so I cannot expect the model
    to suport it.

    I quite like the "mogrify" method of psycopg2 very much. It's very
    useful for debugging.

    And before someone says: "Use the DictCursor-factory to access column
    names". Yes I can do that. But a dict does not guarantee the same
    order of keys as the columns were specified in the query.

    The reason: I wrote a very simple Qt-Widget (inherited from
    QTableWidget) that takes a SQL query and displays the results. And I
    would like to have the columns ordered the same way as I specified in
    the query *with* a header-label. For now I hava a method on the widget
    which takes a query, *and* a list of labels.

    I can live with that though. Although it itches me.

    Would pygresql solve my dilemma? Or would you rather say: "Don't use
    pygresql!" ;)
     
    exhuma.twn, Sep 19, 2007
    #1
    1. Advertising

  2. exhuma.twn

    jay graves Guest

    On Sep 19, 10:06 am, "exhuma.twn" <> wrote:
    > And before someone says: "Use the DictCursor-factory to access column
    > names". Yes I can do that. But a dict does not guarantee the same
    > order of keys as the columns were specified in the query.


    But you can iterate over the cursor.description sequence and then look
    up the column value (by name or index). I haven't specifically tried
    this with your setup but I have used it with various DBAPI2 compliant
    modules. (SQLite, pyodbc, win32 odbc, etc) There are lots of goodies
    in the cursor.description but each implementation varies on what it
    provides. Check out the PEP for more info.

    http://www.python.org/dev/peps/pep-0249/

    HTH.
    Jay
     
    jay graves, Sep 19, 2007
    #2
    1. Advertising

  3. exhuma.twn

    Steve Holden Guest

    exhuma.twn wrote:
    > Plain and simple. What would you use?
    >
    > So far I have written everything with psycopg2. One thing that annoys
    > me is that I cannot easily access the column names from a query. I
    > know that this is not part of the DBAPI2 so I cannot expect the model
    > to suport it.
    >

    Yes it is.

    Execute a SELECT * FROM TABLE WHERE 1=0 and then examine
    cursor.description. This allows you to introspect on database structure.

    > I quite like the "mogrify" method of psycopg2 very much. It's very
    > useful for debugging.
    >
    > And before someone says: "Use the DictCursor-factory to access column
    > names". Yes I can do that. But a dict does not guarantee the same
    > order of keys as the columns were specified in the query.
    >

    See below.

    > The reason: I wrote a very simple Qt-Widget (inherited from
    > QTableWidget) that takes a SQL query and displays the results. And I
    > would like to have the columns ordered the same way as I specified in
    > the query *with* a header-label. For now I hava a method on the widget
    > which takes a query, *and* a list of labels.
    >
    > I can live with that though. Although it itches me.
    >
    > Would pygresql solve my dilemma? Or would you rather say: "Don't use
    > pygresql!" ;)
    >

    Here's a psycopg2-based session:

    >>> curs

    <cursor object at 0x00B823C8; closed: 0>
    >>> curs.execute("SELECT * FROM Person WHERE 1=0")
    >>> curs.description

    (('psnid', 23, None, 4, None, None, None), ('psnfirstname', 1043, None,
    50, None, None, None), ('psnlastname', 1043, None, 50, None, None,
    None), ('psndear', 1043, None, 50, None, None, None), ('psntitle', 1043,
    None, 50, None, None, None), ('psnworkphone', 1043, None, 30, None,
    None, None), ('psnworkextension', 1043, None, 20, None, None, None),
    ('psnhomephone', 1043, None, 30, None, None, None), ('psnmobilephone',
    1043, None, 30, None, None, None), ('psnfaxnumber', 1043, None, 30,
    None, None, None), ('psnemailname', 1043, None, 50, None, None, None),
    ('psnreferredby', 1043, None, 50, None, None, None), ('psnlocid', 23,
    None, 4, None, None, None), ('psnnotes', 1043, None, -1, None, None, None))

    I believe if you use specific column names in your query they will
    appear in the order given in the query also.

    I use this technique in

    http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/81189

    to print arbitrary query outputs.

    regards
    Steve
    --
    Steve Holden +1 571 484 6266 +1 800 494 3119
    Holden Web LLC/Ltd http://www.holdenweb.com
    Skype: holdenweb http://del.icio.us/steve.holden

    Sorry, the dog ate my .sigline
     
    Steve Holden, Sep 19, 2007
    #3
  4. exhuma.twn

    exhuma.twn Guest

    On Sep 19, 5:47 pm, Steve Holden <> wrote:
    > exhuma.twn wrote:
    > > Plain and simple. What would you use?

    >
    > > So far I have written everything with psycopg2. One thing that annoys
    > > me is that I cannot easily access the column names from a query. I
    > > know that this is not part of the DBAPI2 so I cannot expect the model
    > > to suport it.

    >
    > Yes it is.
    >
    > Execute a SELECT * FROM TABLE WHERE 1=0 and then examine
    > cursor.description. This allows you to introspect on database structure.
    >
    > > I quite like the "mogrify" method of psycopg2 very much. It's very
    > > useful for debugging.

    >
    > > And before someone says: "Use the DictCursor-factory to access column
    > > names". Yes I can do that. But a dict does not guarantee the same
    > > order of keys as the columns were specified in the query.

    >
    > See below.
    >
    > > The reason: I wrote a very simple Qt-Widget (inherited from
    > > QTableWidget) that takes a SQL query and displays the results. And I
    > > would like to have the columns ordered the same way as I specified in
    > > the query *with* a header-label. For now I hava a method on the widget
    > > which takes a query, *and* a list of labels.

    >
    > > I can live with that though. Although it itches me.

    >
    > > Would pygresql solve my dilemma? Or would you rather say: "Don't use
    > > pygresql!" ;)

    >
    > Here's a psycopg2-based session:
    >
    > >>> curs

    > <cursor object at 0x00B823C8; closed: 0>
    > >>> curs.execute("SELECT * FROM Person WHERE 1=0")
    > >>> curs.description

    > (('psnid', 23, None, 4, None, None, None), ('psnfirstname', 1043, None,
    > 50, None, None, None), ('psnlastname', 1043, None, 50, None, None,
    > None), ('psndear', 1043, None, 50, None, None, None), ('psntitle', 1043,
    > None, 50, None, None, None), ('psnworkphone', 1043, None, 30, None,
    > None, None), ('psnworkextension', 1043, None, 20, None, None, None),
    > ('psnhomephone', 1043, None, 30, None, None, None), ('psnmobilephone',
    > 1043, None, 30, None, None, None), ('psnfaxnumber', 1043, None, 30,
    > None, None, None), ('psnemailname', 1043, None, 50, None, None, None),
    > ('psnreferredby', 1043, None, 50, None, None, None), ('psnlocid', 23,
    > None, 4, None, None, None), ('psnnotes', 1043, None, -1, None, None, None))
    >
    > I believe if you use specific column names in your query they will
    > appear in the order given in the query also.
    >
    > I use this technique in
    >
    > http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/81189
    >
    > to print arbitrary query outputs.
    >
    > regards
    > Steve
    > --
    > Steve Holden +1 571 484 6266 +1 800 494 3119
    > Holden Web LLC/Ltd http://www.holdenweb.com
    > Skype: holdenweb http://del.icio.us/steve.holden
    >
    > Sorry, the dog ate my .sigline



    Apparently I am deaf dumb and blind.... :( Sorry. I grepped several
    times through the PEP for various other reasons, and this little bit
    escaped me.

    Thanks for being nice guys and answering anyhow. Much appreciated.
     
    exhuma.twn, Sep 19, 2007
    #4
  5. exhuma.twn

    Steve Holden Guest

    exhuma.twn wrote:
    [...]
    >
    > Apparently I am deaf dumb and blind.... :( Sorry. I grepped several
    > times through the PEP for various other reasons, and this little bit
    > escaped me.
    >

    You forgot "stupid" ;-)

    > Thanks for being nice guys and answering anyhow. Much appreciated.
    >


    A pleasure.

    been-known-to-act-stupid-myself-ly y'rs - steve
    --
    Steve Holden +1 571 484 6266 +1 800 494 3119
    Holden Web LLC/Ltd http://www.holdenweb.com
    Skype: holdenweb http://del.icio.us/steve.holden

    Sorry, the dog ate my .sigline
     
    Steve Holden, Sep 19, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. trewornan

    pygresql

    trewornan, Jun 28, 2003, in forum: Python
    Replies:
    1
    Views:
    791
    Skip Montanaro
    Jun 28, 2003
  2. Benjamin Arai
    Replies:
    0
    Views:
    277
    Benjamin Arai
    Feb 1, 2004
  3. Radu Stanciu
    Replies:
    2
    Views:
    349
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=
    Feb 18, 2004
  4. ASh
    Replies:
    10
    Views:
    2,444
    Anton Shishkov
    Mar 31, 2010
  5. mrdrew
    Replies:
    5
    Views:
    2,762
    Dennis Lee Bieber
    Apr 5, 2010
Loading...

Share This Page