[PEAK] How do I configure access into Postgresql?

[Phillip J. Eby]

At 08:14 PM 11/9/03 +1100, Victor Ng wrote:

>How do I go about setting up a peak.ini file to use pyscopg (or
>pyPgSQL) instead of pgdb?  I'd like to use pyscopg because I seem to
>get much better performance out of that particular database driver.
>That and I also can't seem to get pgdb (as of Postgres 7.3.3) to build
>properly under OSX.
>
>My code is straight from the wiki - the only modification I've made is
>to peak.storage.SQL where I've changed PGSQLConnection to use psycopg
>instead of pgdb.

No need to do that.  Simply subclass the driver and specify a different 
DRIVER = setting, then add an .ini entry to support your new driver with an 
appropriate URL scheme.


>Any ideas what's going on here?

 From the traceback, it appears there's a problem creating the typeMap 
attribute.  I would guess that this is because either:

1) you haven't changed the 'supportedTypes' attribute to match the types 
supplied by psycopg, or
2) psycopg doesn't use the same kinds of DBAPI type objects as pgdb, or
3) both of the above

The 'pgdb'-based driver assumes that the underlying DBAPI module uses type 
objects that have a 'values' attribute listing the actual values used for 
that type in cursor 'description' attributes.  If psycopg doesn't do this, 
then the typemap logic won't work properly.

The 'typeMap' attribute of a db connection needs to be a mapping from 
cursor 'description' type values to type converter objects.  PEAK does this 
so that you can have result column values automatically converted to 
application-level types.  These are specified under 'drivername.sql_types' 
properties, e.g. 'pgdb.sql_types' for 'pgdb', and if you create a driver 
the way I suggest, yours will be looked for under 'psycopg.sql_types'.

If 'psycopg' uses either of the two standard methods for specifying DBAPI 
types, it should be easy to make a class that does the right thing.  Check 
and see if this code:

from psycopg import STRING
print STRING.values

gives you back a list or tuple, or gives you an error.  If it gives you an 
error, try 'print STRING' and see if it gives you a number or string value 
back.  If printing STRING.values works, then you have the right base class 
to use, you just need to make sure that 'supportedTypes' is correct (see 
below).  If STRING.values results in an error, but STRING prints a number 
or string, you can probably subclass SQLConnection instead of 
ValueBasedTypeConn.  If neither way works, I may not be able to offer any 
suggestions, except that you'll need to write your own typeMap generator, 
or go without type conversion by making the typeMap an empty mapping.

Last, look at the module contents of psycopg and see what all-uppercase 
names are defined there, such as STRING, NUMBER, etc.  Make your driver 
subclass have a 'supportedTypes' attribute that names those types.  Chances 
are good that psycopg and pgdb differ in the type names they export, so 
this may be what's causing your problem.


>I'm also a little bit fuzzy on how to use the DataManager's in PEAK -
>is there a working example somewhere?

See 'examples/bulletins' in CVS; it doesn't do much, but it's an example.