Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > psycopg2 or pygresql?

Reply
Thread Tools

psycopg2 or pygresql?

 
 
exhuma.twn
Guest
Posts: n/a
 
      09-19-2007
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!"

 
Reply With Quote
 
 
 
 
jay graves
Guest
Posts: n/a
 
      09-19-2007
On Sep 19, 10:06 am, "exhuma.twn" <(E-Mail Removed)> 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

 
Reply With Quote
 
 
 
 
Steve Holden
Guest
Posts: n/a
 
      09-19-2007
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/Coo...n/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

 
Reply With Quote
 
exhuma.twn
Guest
Posts: n/a
 
      09-19-2007
On Sep 19, 5:47 pm, Steve Holden <(E-Mail Removed)> 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/Coo...n/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.

 
Reply With Quote
 
Steve Holden
Guest
Posts: n/a
 
      09-19-2007
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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
psycopg2 / psycopg2.ProgrammingError: syntax error at or near"E'mytable'" mrdrew Python 5 04-05-2010 05:27 PM
psycopg2 / psycopg2.DataError: invalid input syntax for type ASh Python 10 03-31-2010 10:50 AM
Psycopg2 date problems: "Can't adapt" mvanier@cs.caltech.edu Python 0 01-12-2006 01:57 AM
Psycopg2 date problems: "Can't adapt" Steve Holden Python 2 10-21-2005 03:15 PM
Psycopg2 mirror availability Andrew James Python 5 11-29-2004 09:08 AM



Advertisments