pypgsql-users — Discussion list for the pyPgSQL user community.

Re: [Pypgsql-users] pypgsql & macosx

[Tony L. <tl...@me...>]

On Saturday, September 14, 2002, at 05:08 AM, Gerhard H=E4ring wrote:

> Could you send a build log? I'd really like to make pyPgSQL work on
> MacOS/X, too.

Thanks Gerhard.

The log is below.

I get the error
  ld: unknown flag: -R/usr/local/pgsql/lib
which I am told now by my fellow GnuMED developers that you can ignore=20=

- but the ensuing install command gives the same error.

[bluey:~/Desktop/Downloads/pypgsql] tlembke% python setup.py build
running build
running build_py
creating build/lib.darwin-6.0-Power Macintosh-2.2
creating build/lib.darwin-6.0-Power Macintosh-2.2/pyPgSQL
copying pyPgSQL/__init__.py -> build/lib.darwin-6.0-Power=20
Macintosh-2.2/pyPgSQL
copying pyPgSQL/PgSQL.py -> build/lib.darwin-6.0-Power=20
Macintosh-2.2/pyPgSQL
creating build/lib.darwin-6.0-Power Macintosh-2.2/pyPgSQL/libpq
copying pyPgSQL/libpq/__init__.py -> build/lib.darwin-6.0-Power=20
Macintosh-2.2/pyPgSQL/libpq
running build_ext
building 'pyPgSQL.libpq.libpqmodule' extension
creating build/temp.darwin-6.0-Power Macintosh-2.2
gcc -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -no-cpp-precomp=20
-fno-common -dynamic -I/usr/local/pgsql/include -I../../../../../=20
-I/Library/Frameworks/Python.framework/Versions/2.2/include/python2.2=20
-c libpqmodule.c -o build/temp.darwin-6.0-Power=20
Macintosh-2.2/libpqmodule.o
gcc -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -no-cpp-precomp=20
-fno-common -dynamic -I/usr/local/pgsql/include -I../../../../../=20
-I/Library/Frameworks/Python.framework/Versions/2.2/include/python2.2=20
-c pgboolean.c -o build/temp.darwin-6.0-Power Macintosh-2.2/pgboolean.o
gcc -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -no-cpp-precomp=20
-fno-common -dynamic -I/usr/local/pgsql/include -I../../../../../=20
-I/Library/Frameworks/Python.framework/Versions/2.2/include/python2.2=20
-c pgint2object.c -o build/temp.darwin-6.0-Power=20
Macintosh-2.2/pgint2object.o
gcc -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -no-cpp-precomp=20
-fno-common -dynamic -I/usr/local/pgsql/include -I../../../../../=20
-I/Library/Frameworks/Python.framework/Versions/2.2/include/python2.2=20
-c pgint8object.c -o build/temp.darwin-6.0-Power=20
Macintosh-2.2/pgint8object.o
gcc -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -no-cpp-precomp=20
-fno-common -dynamic -I/usr/local/pgsql/include -I../../../../../=20
-I/Library/Frameworks/Python.framework/Versions/2.2/include/python2.2=20
-c pgversion.c -o build/temp.darwin-6.0-Power Macintosh-2.2/pgversion.o
pgversion.c: In function `PgVersion_New':
pgversion.c:125: warning: `c1' might be used uninitialized in this=20
function
pgversion.c:125: warning: `c2' might be used uninitialized in this=20
function
pgversion.c:125: warning: `c1' might be used uninitialized in this=20
function
pgversion.c:125: warning: `c2' might be used uninitialized in this=20
function
pgversion.c:125: warning: `c1' might be used uninitialized in this=20
function
pgversion.c:125: warning: `c2' might be used uninitialized in this=20
function
gcc -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -no-cpp-precomp=20
-fno-common -dynamic -I/usr/local/pgsql/include -I../../../../../=20
-I/Library/Frameworks/Python.framework/Versions/2.2/include/python2.2=20
-c pglargeobject.c -o build/temp.darwin-6.0-Power=20
Macintosh-2.2/pglargeobject.o
pglargeobject.c: In function `PgLo_pickle':
pglargeobject.c:1201: warning: unused variable `cnx'
pglargeobject.c:1202: warning: unused variable `fd'
gcc -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -no-cpp-precomp=20
-fno-common -dynamic -I/usr/local/pgsql/include -I../../../../../=20
-I/Library/Frameworks/Python.framework/Versions/2.2/include/python2.2=20
-c pgnotify.c -o build/temp.darwin-6.0-Power Macintosh-2.2/pgnotify.o
gcc -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -no-cpp-precomp=20
-fno-common -dynamic -I/usr/local/pgsql/include -I../../../../../=20
-I/Library/Frameworks/Python.framework/Versions/2.2/include/python2.2=20
-c pgconnection.c -o build/temp.darwin-6.0-Power=20
Macintosh-2.2/pgconnection.o
gcc -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -no-cpp-precomp=20
-fno-common -dynamic -I/usr/local/pgsql/include -I../../../../../=20
-I/Library/Frameworks/Python.framework/Versions/2.2/include/python2.2=20
-c pgresult.c -o build/temp.darwin-6.0-Power Macintosh-2.2/pgresult.o
gcc -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -no-cpp-precomp=20
-fno-common -dynamic -I/usr/local/pgsql/include -I../../../../../=20
-I/Library/Frameworks/Python.framework/Versions/2.2/include/python2.2=20
-c pymemstrdup.c -o build/temp.darwin-6.0-Power=20
Macintosh-2.2/pymemstrdup.o
gcc -Wl,-F. -Wl,-flat_namespace,-U,_environ -bundle -framework Python=20
build/temp.darwin-6.0-Power Macintosh-2.2/libpqmodule.o=20
build/temp.darwin-6.0-Power Macintosh-2.2/pgboolean.o=20
build/temp.darwin-6.0-Power Macintosh-2.2/pgint2object.o=20
build/temp.darwin-6.0-Power Macintosh-2.2/pgint8object.o=20
build/temp.darwin-6.0-Power Macintosh-2.2/pgversion.o=20
build/temp.darwin-6.0-Power Macintosh-2.2/pglargeobject.o=20
build/temp.darwin-6.0-Power Macintosh-2.2/pgnotify.o=20
build/temp.darwin-6.0-Power Macintosh-2.2/pgconnection.o=20
build/temp.darwin-6.0-Power Macintosh-2.2/pgresult.o=20
build/temp.darwin-6.0-Power Macintosh-2.2/pymemstrdup.o=20
-L/usr/local/pgsql/lib -Wl,-R/usr/local/pgsql/lib -lpq -o=20
build/lib.darwin-6.0-Power Macintosh-2.2/pyPgSQL/libpq/libpqmodule.so
ld: unknown flag: -R/usr/local/pgsql/lib
error: command 'gcc' failed with exit status 1

Re: [Pypgsql-users] pypgsql & macosx

[Gerhard <ger...@gm...>]

* Tony Lembke <tl...@me...> [2002-09-09 23:47 +1000]:
> Dear List,
> 
> Has pypgsql successfully been used on a MacOSX system?

As you've received no answer until now, I'm stepping forward despite the
fact that I have no access to MacOS/X.

> I have tried to 'python setup.py build' from source but have stuck
> some errors.

Could you send a build log? I'd really like to make pyPgSQL work on
MacOS/X, too.

-- Gerhard

[Pypgsql-users] pypgsql & macosx

[Tony L. <tl...@me...>]

Dear List,

Has pypgsql successfully been used on a MacOSX system?

I have tried to 'python setup.py build' from source but have stuck some 
errors.

Thanks,

Tony Lembke

[Pypgsql-users] Products and projects using pyPgSQL - RPMs

[Gerhard <ger...@gm...>]

Hey folks,

I've added a section "Products and projects using pyPgSQL" to the
homepage. If you'd like your product/service (commercial or not,
internal or not) or project to be listed there, just drop me a mail.

Also Sean Reifschneider has contributed a .SPEC file that's part of
today's release. Using an updated .SPEC file, Sean has produced RPMs for
Python 2.2 on the Redhat (and his KRUD) Linux distribution. Sean is the
producer of the invaluable Python RPMs in case you've never heard of
him. You can look at the homepage for download links of the RPM and
SRPM.

-- Gerhard

[Pypgsql-users] [ANN] pyPgSQL 2.2 released

[Gerhard <ger...@gm...>]

Announce: pyPgSQL - Version 2.2 is released.
===========================================================================

pyPgSQL v2.2 has been released. It is a bug fix release to version 2.1.

It is available at http://pypgsql.sourceforge.net.

pyPgSQL is a package of two (2) modules that provide a Python DB-API 2.0
compliant interface to PostgreSQL databases. The first module, libpq,
exports the PostgreSQL C API to Python. This module is written in C and
can be compiled into Python or can be dynamically loaded on demand. The
second module, PgSQL, provides the DB-API 2.0 compliant interface and
support for various PostgreSQL data types, such as INT8, NUMERIC, MONEY,
BOOL, ARRAYS, etc. This module is written in Python and works with
PostgreSQL 7.0 or later and Python 2.0 or later.

Note: It is highly recommended that you use PostgreSQL 7.1 or later and
Python 2.1 or later.

PostgreSQL is a sophisticated Object-Relational DBMS, supporting almost all
SQL constructs, including sub-selects, transactions, and user-defined types
and functions. It is the most advanced open-source database available any-
where More information about PostgreSQL can be found at the PostgreSQL home
page at http://www.postgresql.org.

Python is an interpreted, interactive, object-oriented programming lang-
uage. It combines remarkable power with very clear syntax. It has mod-
ules, classes, exceptions, very high level dynamic data types, and dynamic
typing. There are interfaces to many system calls and libraries, as well
as to various windowing systems (X11, Motif, Tk, Mac, MFC). New builtin
modules are easily written in C or C++. Python is also usable as an exten-
sion language for applications that need a programmable interface. Python
is copyrighted but freely usable and distributable, even for commercial
use. More information about Python can be found on the Python home page at
http://www.python.org.

---------------------------------------------------------------------------
ChangeLog:
===========================================================================

Changes since pyPgSQL Version 2.1
=================================

The following source code files were added to Version 2.2 of pyPgSQL:

    pyPgSQL.spec	- RPM spec file, contributed by Sean Reifschneider.

Changes to README
-----------------
* Added note about case-insensitiveness of column access in PgResultSet.

Changes to PgSQL.py
-------------------
* Fixed various problems with the PgResultSet: Column (attribute and
dictionary) access is now case-insensitive.  A __contains__ method was added
and the __setattr__ method was fixed. The get method got an optional default
value parameter.

* Fixed various problems with the PgNumeric type:
  - Added code to allow a float as an argument to the PgNumeric constructor.
  - You can now change the precision/scale of a PgNumeric by:
      a = PgNumeric(pgnumeric, new prec, new scale).
    This can be used to 'cast' a PgNumeric to the proper precision and scale
    before storing it in a field.
  - The arithmatic routines (__add__, __radd__, etc) now ensure that the
    arguments are properly coerced to the correct types.
  - Added support for the augmented arithmetic operations (__iadd__, etc).
  - The math routines would lose precision because the precision/ scale were
    set to be the same as the first operand.  This is no longer the case all
    precision is retained for the +, -, and * operations.

* Fixed problem that occurs when a query on an OID field doesn't return any
rows. [Bug #589370].
	
* Applied patch #569203 and also added __pos__ and __abs__ special methods to
PgNumeric.

* Ensure proper SQL-quoting of long ints.

Changes to PgSQLTestcases.py
----------------------------
* 14 new tests, mostly for PgNumeric and PgResultSet.

[Pypgsql-users] RfC: Inserting data using PostgreSQL functions

[Gerhard <ger...@gm...>]

I've experimented a little with using server-side functions to move SQL
from the client to the server-side. Last time I posted about how to
return cursors from functions and I think I'll try to implement the API
I suggested for pyPgSQL 3.

Now I've tried to come up with a way to get INSERT statements out of
Python sources, too. A function that INSERTS a new record and gives me
back the value of the PRIMARY KEY/SEQUENCE would be ideal. Without the
usual two-step approach of querying the sequence first, then using the
value for the primary key.

This is what my experiments yielded - maybe somebody has comments on
this or other/better approaches?

Btw. is there really no better way than to_char(new_id,
'9999999999999999999999') to convert an int to a varchar?


CREATE TABLE test (
    id serial,
    name varchar(20),
    age int
);

CREATE FUNCTION "test_ins" (varchar[]) RETURNS integer AS '
declare
  new_id integer;
  parms alias for $1;
  i int := 1;
  statement varchar(4000);
begin
  select nextval(''test_id_seq'') into new_id;

  statement := ''insert into test values ('';
  statement := statement || to_char(new_id, ''9999999999999999999999'');
  while parms[i] loop
    statement := statement || '','' || parms[i];
    i := i + 1;
  end loop;
    statement := statement || '');'';

  execute statement;

  return new_id;
end;
' LANGUAGE 'plpgsql';



from pyPgSQL import PgSQL

db = PgSQL.connect()
cursor = db.cursor()
cursor.execute("select test_ins(%s)", ([map(PgSQL._quote, ['Joe Doe', 25])]),)
last_id = cursor.fetchone()[0]
cursor.execute("select * from test where id=%s", (last_id,))
print cursor.fetchone()
db.commit()
db.close()
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

Re: [Pypgsql-users] Re: Pypgsql-users digest, Vol 1 #53 - 6 msgs

[Gerhard <ger...@gm...>]

* terry <tg...@ci...> [2002-07-29 16:39 -0400]:
> I posted the 'other end' of this problem last week on the Python 
> list under "Numeric Data Types".  The result was that there is no 
> intrinsic Python Numeric data type, so simple computations such 
> as:  Total = Qty * UnitPrice  cannot be done with data from 
> Postgres.  Following some of the links showed that there seems to 
> be little interest in making an intrinsic numeric data type.  

MaL wants to produce some for his mxODBC database interface. He has
alrady the startings of one (which is based on GMP, IIRC) in his
experimental package from egenix.com. I myself didn't check it out, yet.

It's not that difficult to produce a fixed point data type yourself.
I'm pretty sure that we can enhance or rewrite PgNumeric so that the
current problems are solved.

If you really want to do calculations using this data type, then a
rewrite in C should provide a boost in performance. This has been on the
pyPgSQL TODO list for about a year now ...

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

[Pypgsql-users] Using 'stored procedures' in pyPgSQL

[Gerhard <ger...@gm...>]

I'm trying to check out the more advanced features of PostgreSQL now,
and having some Oracle background, I want to have Stored Procedures.
PostgreSQL doesn't really have the same thing, but after a little bit of
googling, I found that you can have something even better:
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html

So here's my test code to try this out:


# Begin code
from pyPgSQL import PgSQL

db = PgSQL.connect()
cursor = db.cursor()

try:
    cursor.execute("drop table test")
except:
    pass

cursor.execute("create table test(id int, name varchar(20))")
cursor.executemany("insert into test (id, name) values (%s, %s)",
    [(1, 'foo'), (2, 'bar'), (2, 'baz')])

cursor.execute("""create or replace function myfunc(int) returns refcursor as'
    declare
        queried_id alias for $1;
        ref refcursor;
    begin
        open ref for select id, name from test where id="queried_id";
        return ref;
    end;
    ' language 'plpgsql'""")

cursor.callproc("myfunc", 2)                            # (1)
cursorname = cursor.fetchone()[0]                       # (2)
cursor.execute('fetch all in "%s"' % cursorname)        # (3)

for row in cursor.fetchall():
    print row.id, row.name

db.close()
# End code


The lines (1) to (3) feel a little clunky to me. I'd suggest we enhance
pyPgSQL so this feels more natural, and so we can directly use the
PostgreSQL cursors. I have no concrete API in mind, but being able to do
stuff like this would be cool:

cursor.callproc("myfunc", 2)
sp_cursor = cursor.fetchone()[0] # ref. cursor that stored procedure returns
for row in sp_cursor.fetchall():
    # do stuff

With this API, objects of type PG_REFCURSOR would be wrapped to an
instance of Python Cursor class. This cursor class would work similar to
the DB-API2 cursor class and have methods:
    - fetchone()/fetchmany()/fetchall()
    - __iter__() and next() for iteration
    - seek(val, mode="relative") # PEP 0249 optional method,
      unfortunately, PostgreSQL can only do "relative", not "absolute"

Opinions? Can anybody think of a better API? Perhaps it's possible to do
even more magic in the callproc(), so the fetchone()[0] can be avoided
...

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

Re: [Pypgsql-users] Comparing PgNumeric etc. with other data types.

[Billy G. A. <bg...@mu...>]

Gerhard =?iso-8859-1?Q?H=E4ring?= wrote:
> * Adam Buraczewski <ad...@po...> [2002-07-29 00:46 +0200]:
> > Hallo,
> > 
> > Recently I found that some PgXXX classes defined in PgSQL module lack
> > some features I would expect because of the fact that they are
> > wrappers around PostgreSQL basic data types.  For example, PgNumeric
> > class is a wrapper around "numeric" type, so it should behave like a
> > Python number: it should be comparable to other numeric Python types
> > and it should be usable as a logical value (false when zero and true
> > when non-zero).  However, PgNumeric values behave differently: they
> > cannot be compared to other, non-PgNumeric, types/classes and they
> > always are "true" in logical expressions despite of their real value.
> > I found the patch on pyPgSQL site which solves the latter problem,
> 
> Committed now with the addition of __pos__ and __abs__ (for the + and
> abs unary "operators").
> 
> > but the first one still exists.
> 
> Indeed. It's not obvious for me how to fix this, as I'm not very
> proficient with emulating numeric types. It _seems_ that you can get a
> lot further by just adding something like this at the start of
> PgNumeric's __coerce__ method:
> 
>     if not isinstance(other, PgNumeric):
>         other = PgNumeric(str(other))
> 
> I have a gut feeling, however, that this isn't all that'll be involved
> for PgNumerics. That's why I haven't commited this, yet.
> 
> > IMHO the most annoying problem is that PgNumeric values cannot be
> > compared to data values of other type, for example following
> > expressions:
> > 
> > 	PgNumeric('123.45') == None
> 
> Ouch! As has been said already, this isn't good practise. But I learnt
> it only relatively recently myself. The reason is that None is a
> singleton and singletons are best compared using "is". This avoids
> potential bugs, and is a lot faster.
> 
> > [...] Especially comparing values to None is very popular in database
> > applications as None represents NULL value, am I not right? :)
> 
> Yeah. That's why you should do it like in SQL and compare using the
> "is"-operator :)
> 
> > Since I am going to write some new programs using pyPgSQL library, I
> > think I could contribute a patch to PgSQL module, which would solve
> > problems described above.  However I would like to know:
> > 
> > 1. if compatibility with Python < 2.1 should be kept (I would like to
> > use new __lt__, __eq__ etc. method names instead of __cmp__),
> 
> I myself haven't used Python 2.0 for years, but it's probably a bad idea
> to break Python 2.0 compatibility in the pyPgSQL 2.x line at this point.
> If you'd like to contribute to the upcoming (not started yet) pyPgSQL 3.0
> instead, you could even use all the cool Python 2.2-only features ;-)
> 
> > 2. if PgNumeric, PgInt8 and other wrappers around PostgreSQL numeric
> > types should be comparable to each other or to standard Python numeric
> > types (float, long, int) only.
> 
> IMO, they should be comparable to all numeric types. I just noticed that
> currently, they aren't.

I noticed that, at least in Python 2.2, that coerce is not being called.  I should have a fix for this shortly.
-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

[Pypgsql-users] Re: Pypgsql-users digest, Vol 1 #53 - 6 msgs

[terry <tg...@ci...>]

I posted the 'other end' of this problem last week on the Python 
list under "Numeric Data Types".  The result was that there is no 
intrinsic Python Numeric data type, so simple computations such 
as:  Total = Qty * UnitPrice  cannot be done with data from 
Postgres.  Following some of the links showed that there seems to 
be little interest in making an intrinsic numeric data type.  

Until that is done, all database use of numeric data (money) is 
forced into whatever set of constructs one can cobble together 
for the specific instance.  Beware if your software has to go 
through Quality Assurrance, because each calculation using mixed 
variable types will be suspect and will need to be tested 
uniquely.  It sort of says that Python is not suitable for 
accounting applications until they develope an intrinsic numeric 
type that the various database number types can be converted to.


>>  Recently I found that some PgXXX classes defined in PgSQL
>> module lack some features I would expect because of the fact
>> that they are wrappers around PostgreSQL basic data types. 
>> For example, PgNumeric class is a wrapper around "numeric"
>> type, so it should behave like a Python number: it should be
>> comparable to other numeric Python types and it should be
>> usable as a logical value (false when zero and true when
>> non-zero).  However, PgNumeric values behave differently: they
>> cannot be compared to other, non-PgNumeric, types/classes and
>> they always are "true" in logical expressions despite of their
>> real value. I found the patch on pyPgSQL site which solves the
>> latter problem, but the first one still exists.
>>
>>  IMHO the most annoying problem is that PgNumeric values
>> cannot be compared to data values of other type, for example
>> following expressions:
>>
>>  	PgNumeric('123.45') == None
>>  	PgNumeric('123.45') > 100
>>
>>  raise exceptions instead of returning "false" or "true" (0 or
>> 1).  Of course, one can write:
>>
>>  	type(PgNumeric('123.45')) == type(None)
>>
>>  but it is not so elegant and differs from what we can write
>> for standard built-in Python types:
>>
>>  	123.45 == None
>>
>>  Especially comparing values to None is very popular in
>> database applications as None represents NULL value, am I not
>> right? :)
>>
>>  Since I am going to write some new programs using pyPgSQL
>> library, I think I could contribute a patch to PgSQL module,
>> which would solve problems described above.  However I would
>> like to know:
>>
>>  1. if compatibility with Python < 2.1 should be kept (I would
>> like to use new __lt__, __eq__ etc. method names instead of
>> __cmp__),
>>
>>  2. if PgNumeric, PgInt8 and other wrappers around PostgreSQL
>> numeric types should be comparable to each other or to
>> standard Python numeric types (float, long, int) only.
>>
>>  Regards,

-- 

terry 

Re: [Pypgsql-users] Comparing PgNumeric etc. with other data types.

[Adam B. <ad...@po...>]

On Mon, Jul 29, 2002 at 03:23:12AM +0200, Gerhard H=E4ring wrote:
> > I found the patch on pyPgSQL site which solves the latter problem,
> Committed now with the addition of __pos__ and __abs__ (for the + and
> abs unary "operators").

Great!  I'll get it in a few minutes :)

> > but the first one still exists.
> Indeed. It's not obvious for me how to fix this, as I'm not very
> proficient with emulating numeric types. It _seems_ that you can get a
> lot further by just adding something like this at the start of
> PgNumeric's __coerce__ method:
>=20
>     if not isinstance(other, PgNumeric):
>         other =3D PgNumeric(str(other))
>=20
> I have a gut feeling, however, that this isn't all that'll be involved
> for PgNumerics. That's why I haven't commited this, yet.

I'll try to read more about implementing numeric types in Python.  I
feel that the section "3.3.6 Emulating numeric types" of the manual
does not answer all my questions, so I am going to look into some
mathematical Python libraries as well.  I'll write here about my
findings.
>=20
> > [...] Especially comparing values to None is very popular in database
> > applications as None represents NULL value, am I not right? :)
> Yeah. That's why you should do it like in SQL and compare using the
> "is"-operator :)

OK, now I understand this :)

> > 1. if compatibility with Python < 2.1 should be kept (I would like to
> > use new __lt__, __eq__ etc. method names instead of __cmp__),
> I myself haven't used Python 2.0 for years, but it's probably a bad ide=
a
> to break Python 2.0 compatibility in the pyPgSQL 2.x line at this point.
> If you'd like to contribute to the upcoming (not started yet) pyPgSQL 3=
.0
> instead, you could even use all the cool Python 2.2-only features ;-)

After some thinking I found that probably __cmp__ should be enough for
PgNumeric and other types, so compatibility with Python 2.0 could be
preserved.  Unless my studies on mathematical libraries (above) would
prove something different, but even in this case I can define both
__cmp__ and __lt__ and the latter will be used by Python >=3D 2.1
automatically.

> IMO, they should be comparable to all numeric types. I just noticed tha=
t
> currently, they aren't.

That's clear.

Thanks a lot for the answers!

Regards,

--=20
Adam Buraczewski <ad...@po...> * Linux registered user #165585
GCS/TW d- s-:+>+:- a- C+++(++++) UL++++$ P++ L++++ E++ W+ N++ o? K? w--
O M- V- PS+ !PE Y PGP+ t+ 5 X+ R tv- b+ DI? D G++ e+++>++++ h r+>++ y?

Re: [Pypgsql-users] Comparing PgNumeric etc. with other data types.

[Adam B. <ad...@po...>]

On Mon, Jul 29, 2002 at 03:04:49AM +0200, Gerhard H=E4ring wrote:
> > > 	123.45 =3D=3D None
> > 	123.45 is None:
> > I thought comparing to None via equality was bad form?
> Indeed. I learnt that in a thread you started on c.l.py. All comparison=
s
> to None using =3D=3D are now gone since the 2.1 release, btw.

OK, You're right.  I am not subscribed to c.l.py, although after
reading this above I think I should. :/  Indeed, "is" operator is
much more suitable for this purpose and is faster.  Now I see that my
knowledge of Python isn't simply good enough -- there are still
problems like this that can be solved in different ways, and my
solutions are sometimes not the best ones. :(

I used to write "x =3D=3D None" because I had read in Python manual that
"most other types compare unequal unless they are the same object", so
everything except None should not be equal to None.  And it worked
until pyPgSQL 2.1 which broke some my existing applications.  And
instead of looking into changelog, I assumed that this could be a lack
of "standard" behaviour of PgNumeric.  It was lame, I know now :))

Thanks a lot!

--=20
Adam Buraczewski <ad...@po...> * Linux registered user #165585
GCS/TW d- s-:+>+:- a- C+++(++++) UL++++$ P++ L++++ E++ W+ N++ o? K? w--
O M- V- PS+ !PE Y PGP+ t+ 5 X+ R tv- b+ DI? D G++ e+++>++++ h r+>++ y?

Re: [Pypgsql-users] Comparing PgNumeric etc. with other data types.

[Gerhard <ger...@gm...>]

* Adam Buraczewski <ad...@po...> [2002-07-29 00:46 +0200]:
> Hallo,
> 
> Recently I found that some PgXXX classes defined in PgSQL module lack
> some features I would expect because of the fact that they are
> wrappers around PostgreSQL basic data types.  For example, PgNumeric
> class is a wrapper around "numeric" type, so it should behave like a
> Python number: it should be comparable to other numeric Python types
> and it should be usable as a logical value (false when zero and true
> when non-zero).  However, PgNumeric values behave differently: they
> cannot be compared to other, non-PgNumeric, types/classes and they
> always are "true" in logical expressions despite of their real value.
> I found the patch on pyPgSQL site which solves the latter problem,

Committed now with the addition of __pos__ and __abs__ (for the + and
abs unary "operators").

> but the first one still exists.

Indeed. It's not obvious for me how to fix this, as I'm not very
proficient with emulating numeric types. It _seems_ that you can get a
lot further by just adding something like this at the start of
PgNumeric's __coerce__ method:

    if not isinstance(other, PgNumeric):
        other = PgNumeric(str(other))

I have a gut feeling, however, that this isn't all that'll be involved
for PgNumerics. That's why I haven't commited this, yet.

> IMHO the most annoying problem is that PgNumeric values cannot be
> compared to data values of other type, for example following
> expressions:
> 
> 	PgNumeric('123.45') == None

Ouch! As has been said already, this isn't good practise. But I learnt
it only relatively recently myself. The reason is that None is a
singleton and singletons are best compared using "is". This avoids
potential bugs, and is a lot faster.

> [...] Especially comparing values to None is very popular in database
> applications as None represents NULL value, am I not right? :)

Yeah. That's why you should do it like in SQL and compare using the
"is"-operator :)

> Since I am going to write some new programs using pyPgSQL library, I
> think I could contribute a patch to PgSQL module, which would solve
> problems described above.  However I would like to know:
> 
> 1. if compatibility with Python < 2.1 should be kept (I would like to
> use new __lt__, __eq__ etc. method names instead of __cmp__),

I myself haven't used Python 2.0 for years, but it's probably a bad idea
to break Python 2.0 compatibility in the pyPgSQL 2.x line at this point.
If you'd like to contribute to the upcoming (not started yet) pyPgSQL 3.0
instead, you could even use all the cool Python 2.2-only features ;-)

> 2. if PgNumeric, PgInt8 and other wrappers around PostgreSQL numeric
> types should be comparable to each other or to standard Python numeric
> types (float, long, int) only.

IMO, they should be comparable to all numeric types. I just noticed that
currently, they aren't.

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

Re: [Pypgsql-users] Comparing PgNumeric etc. with other data types.

[Gerhard <ger...@gm...>]

* Mark McEahern <mar...@mc...> [2002-07-28 19:02 -0500]:
> > but it is not so elegant and differs from what we can write for
> > standard built-in Python types:
> > 
> > 	123.45 == None
> 
> Have you tried:
> 
> 	123.45 is None:
> 
> I thought comparing to None via equality was bad form?

Indeed. I learnt that in a thread you started on c.l.py. All comparisons
to None using == are now gone since the 2.1 release, btw.

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

RE: [Pypgsql-users] Comparing PgNumeric etc. with other data types.

[Mark M. <mar...@mc...>]

> but it is not so elegant and differs from what we can write for
> standard built-in Python types:
> 
> 	123.45 == None

Have you tried:

	123.45 is None:

I thought comparing to None via equality was bad form?

// m

[Pypgsql-users] Comparing PgNumeric etc. with other data types.

[Adam B. <ad...@po...>]

Hallo,

Recently I found that some PgXXX classes defined in PgSQL module lack
some features I would expect because of the fact that they are
wrappers around PostgreSQL basic data types.  For example, PgNumeric
class is a wrapper around "numeric" type, so it should behave like a
Python number: it should be comparable to other numeric Python types
and it should be usable as a logical value (false when zero and true
when non-zero).  However, PgNumeric values behave differently: they
cannot be compared to other, non-PgNumeric, types/classes and they
always are "true" in logical expressions despite of their real value.
I found the patch on pyPgSQL site which solves the latter problem, but
the first one still exists.

IMHO the most annoying problem is that PgNumeric values cannot be
compared to data values of other type, for example following
expressions:

	PgNumeric('123.45') == None
	PgNumeric('123.45') > 100

raise exceptions instead of returning "false" or "true" (0 or 1).  Of
course, one can write:

	type(PgNumeric('123.45')) == type(None)

but it is not so elegant and differs from what we can write for
standard built-in Python types:

	123.45 == None

Especially comparing values to None is very popular in database
applications as None represents NULL value, am I not right? :)

Since I am going to write some new programs using pyPgSQL library, I
think I could contribute a patch to PgSQL module, which would solve
problems described above.  However I would like to know:

1. if compatibility with Python < 2.1 should be kept (I would like to
use new __lt__, __eq__ etc. method names instead of __cmp__),

2. if PgNumeric, PgInt8 and other wrappers around PostgreSQL numeric
types should be comparable to each other or to standard Python numeric
types (float, long, int) only.

Regards,

-- 
Adam Buraczewski <ad...@po...> * Linux registered user #165585
GCS/TW d- s-:+>+:- a- C+++(++++) UL++++$ P++ L++++ E++ W+ N++ o? K? w--
O M- V- PS+ !PE Y PGP+ t+ 5 X+ R tv- b+ DI? D G++ e+++>++++ h r+>++ y?

[Pypgsql-users] Re: Installing pyPgSQL under ZOPE

[Gerhard <hae...@gm...>]

Cc-ing the users' mailing list. I'd ask to keep discussions there,
unless there's a reason not to.

* Raj Maddali <mad...@ho...> [2002-07-25 21:19 -0500]:
> Hello
> 
> I installed pyPgSQL under Zope/products as a Zope product.

pyPgSQL or the Zope DA in CVS? Nobody has looked into the Zope DA for a
good time, AFAIK.

> However when i try to use it in a python script inside Zope I get
> ImportError.  Any help in this would be greatly appreciated

I can help you get either one to work, but I need an answer for the
question above first :)

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

Re: [Pypgsql-users] ImportError: No module named DateTime

[Mike W. <mw...@mi...>]

http://www.lemburg.com/files/python/mxDateTime.html

Download and install from here for your platform.=20
http://www.egenix.com/files/python/eGenix-mx-Extensions.html#Download-mxBASE



At 02:55 PM 7/5/2002 -0300, Ricardo Rivaldo wrote:
>but a get the message:
>
>Traceback (most recent call last):
>   File "D:\Documents and=20
> Settings\Administrator.MARLEY\Desktop\testedb.py", line 6, in ?
>     from pyPgSQL import PgSQL                     # load database routines
>   File "D:\Python22\Lib\site-packages\pyPgSQL\PgSQL.py", line 329, in ?
>     import DateTime
>ImportError: No module named DateTime
>
>i trying to find a module DateTime but no one exists
>
>Rivaldo
>N=AC=B1=F9=DE=B5=E9=9A=8AX=AC=B2=9A'=B2=8A=DEu=BC=93=86)=E4=E7=A4=B8=A7=82)=
=E0=CA=8B=A6=A2=E9=DD=B2=87=DCi=F7=DE=8Av=ADy=D8=E8=CAm=A7=FF=ED=86)=E4=81=
=E7=A4r=89=BF=B1=F3=F2=A6*=AD=EB=AE=C9=9A=8AX=A7=82X=AC=B4=FC=A9=82=CA=A5=BA=
=C7=AB=B2X=AC=B6=CB(=BA=B7~=8A=E0zw=AD=86=DBi=B3=FF=E5=8A=CBl=B2=8B=ABq=E7=
=E8=AE=A7z=DF=E5=8A=CBl=FEX=AC=B6)=DF=A3=FAr=A6*=AD=EB=AE=20
>

[Pypgsql-users] ImportError: No module named DateTime

[Ricardo R. <ri...@tr...>]

SGksIGkgYW0gdHJ5aW5nIHRvIG1ha2UgbXkgZmlyc3QgcHlwZ3NxbCBwcm9ncmFtOg0KIA0KaW1w
b3J0IHN5cw0KZnJvbSBweVBnU1FMIGltcG9ydCBQZ1NRTCAgICAgICAgICAgICAgICAgICAgICMg
bG9hZCBkYXRhYmFzZSByb3V0aW5lcyANCmwgPSBQZ1NRTC5jb25uZWN0KCc6QWRtaW5pc3RyYXRv
cjo6bWFybGV5OnRlc3RkYicpICAgICAgICMgY29ubmVjdCB0byB0aGUgZGF0YWJhc2UNCmNvbm4g
PSBsLmNvbm5lY3QoKSAgICAgICAjIGNvbm5lY3QgdG8gdGhlIGRhdGFiYXNlDQpjdXIgPSBjb25u
LmN1cnNvcigpDQpjdXIuZXhlY3V0ZSgiU0VMRUNUIG5hbWUgRlJPTSB0ZXN0ZSIpDQpSZXN1bHRT
ZXQgPSBjdXIuZmV0Y2hvbmUoKQ0Kc3lzLnN0ZG91dC53cml0ZSgnJXNcbicgJSBSZXN1bHRTZXQu
bmFtZSkgICAgICAgICAgICAgICAgICMgcHJpbnQgdGhlIHZhbHVlIHJldHVybmVkIA0KLT4+Pj4+
Pj4+Pj4+Pj4+Pj4+Pj4NCiANCmJ1dCBhIGdldCB0aGUgbWVzc2FnZToNCiANClRyYWNlYmFjayAo
bW9zdCByZWNlbnQgY2FsbCBsYXN0KToNCiAgRmlsZSAiRDpcRG9jdW1lbnRzIGFuZCBTZXR0aW5n
c1xBZG1pbmlzdHJhdG9yLk1BUkxFWVxEZXNrdG9wXHRlc3RlZGIucHkiLCBsaW5lIDYsIGluID8N
CiAgICBmcm9tIHB5UGdTUUwgaW1wb3J0IFBnU1FMICAgICAgICAgICAgICAgICAgICAgIyBsb2Fk
IGRhdGFiYXNlIHJvdXRpbmVzDQogIEZpbGUgIkQ6XFB5dGhvbjIyXExpYlxzaXRlLXBhY2thZ2Vz
XHB5UGdTUUxcUGdTUUwucHkiLCBsaW5lIDMyOSwgaW4gPw0KICAgIGltcG9ydCBEYXRlVGltZQ0K
SW1wb3J0RXJyb3I6IE5vIG1vZHVsZSBuYW1lZCBEYXRlVGltZQ0KIA0KaSB0cnlpbmcgdG8gZmlu
ZCBhIG1vZHVsZSBEYXRlVGltZSBidXQgbm8gb25lIGV4aXN0cw0KIA0KUml2YWxkbw0K

[Pypgsql-users] Re[2]: SOURCE CODE SAMPLES NEED

[<hae...@gm...>]

Ricardo Rivaldo <ri...@tr...> wrote:
> thank for all the answers, it should go now. I also find the samples on
> source code.
>  
> If I have have the time I will post some documentation from a beginners
> perspective.

That would be great.

Btw. I just got permission to use the Python DB-API tutorial [1] for the
so-called Redhat database (which really is just their supported version of
PostgreSQL) as the basis for the manual I plan to do soon. I'll try to start
work on it this weekend.

If anybody has suggestions as for what to include in the manual,
please let me know.

Gerhard

[1] http://www.redhat.com/support/resources/howto/database/database_python/ 
--
mail:   ger...@gm...              registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id 86AB43C0
public key fingerprint: DEC1 1D02 5743 1159 CD20  A4B6 7B22 6575 86AB 43C0
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

[Pypgsql-users] RE:SOURCE CODE SAMPLES NEED

[Ricardo R. <ri...@tr...>]

dGhhbmsgZm9yIGFsbCB0aGUgYW5zd2VycywgaXQgc2hvdWxkIGdvIG5vdy4gSSBhbHNvIGZpbmQg
dGhlIHNhbXBsZXMgb24gc291cmNlIGNvZGUuDQogDQpJZiBJIGhhdmUgaGF2ZSB0aGUgdGltZSBJ
IHdpbGwgcG9zdCBzb21lIGRvY3VtZW50YXRpb24gZnJvbSBhIGJlZ2lubmVycyBwZXJzcGVjdGl2
ZS4NCiANClJpdmFsZG8NCg==

Re: [Pypgsql-users] Sample source code needed

[Mike W. <mw...@mi...>]

Yes, absolutely. I wrote a content management system using PyPgSQL and came 
to love the DB-API style. Makes life easy. And safe.

Apparently I've forgotten that for the quick reporting hack example I 
provided !

At 09:01 PM 7/4/2002 +0200, Gerhard =?iso-8859-15?Q?H=E4ring?= wrote:
>In this case it works to use Python quoting. But it's good for you [tm]
>to get used to the DB-API style of quoting:
>
>query_string = 'delete from pbsp where extract(epoch from (now() -
>                 since)) >= %s'

 > cursor.execute(query_tring, (purge_age,))

Re: [Pypgsql-users] Sample source code needed

[Gerhard <ger...@gm...>]

* Michael Watkins <wa...@tr...> [2002-07-04 09:22 -0700]:
> And here is a simple example using the DBAPI wrapper.
> purge_age = 43200               # seconds  (43200 = 12 hours)
> query_string = 'delete from pbsp where extract(epoch from (now() - 
>                   since)) >= %d' % (purge_age,)
> [...]
> cursor.execute(query_string)

In this case it works to use Python quoting. But it's good for you [tm]
to get used to the DB-API style of quoting:

query_string = 'delete from pbsp where extract(epoch from (now() -
                since)) >= %s'
...
cursor.execute(query_tring, (purge_age,))

Otherwise, you'll get bitten as soon as you use strings, or data types,
which need special quoting. Especially _never_ commit this sin:

cursor.execute("select foo from bar where baz='%s' % "Tom's Farm")

The resulting SQL would be "select foo from bar where baz='Tom's Farm'"
which isn't valid SQL because the ' would need to be escaped.

The DB-API form of quoting does it right:
>>> from pyPgSQL import PgSQL
>>> db = PgSQL.connect()
>>> db.conn.toggleShowQuery
'On'
>>> cursor = db.cursor()
QUERY: BEGIN WORK
>>> cursor.execute("select foo from bar where baz=%s", "Tom's Farm")
QUERY: DECLARE PgSQL_0811067C CURSOR FOR select foo from bar where baz='Tom\'s Farm'

Note that the string gets automatically quoted and the single quote gets
automatically escaped, too.

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

Re: [Pypgsql-users] Sample source code needed

[Michael W. <wa...@tr...>]

And here is a simple example using the DBAPI wrapper.

#!/usr/local/bin/python
# run via cron
# purge pop before smtp records past an expiry time from virtual.pbsp
# send basic statistics to root

from pyPgSQL import PgSQL

## delete aged records

purge_age = 43200               # seconds  (43200 = 12 hours)
query_string = 'delete from pbsp where extract(epoch from (now() - 
since)) >= %d' % (purge_age,)
dsn = 'localhost::virtual'      #dsn = 
'host:port:database:username:password' some can be blank

db =  PgSQL.connect(dsn)
cursor = db.cursor()
cursor.execute(query_string)
db.commit()

## spit out a simple report

query_string =  """select mailbox_idnr as id, count(*) as msg_count, 
sum(messagesize) as msg_size,
     userid from messages, users where user_idnr = mailbox_idnr
     group by mailbox_idnr, users.userid"""

cursor.execute(query_string)
result = cursor.fetchall()
print "System email statistics\n"
print "%-5s %5s %10s %s" % ('ID','Count','Size/K', 'Account')
for row in result:
         print "%-5s %5s %10s %s" % (row.id, row.msg_count, 
int(row.msg_size) / 1000, row.userid)




At 09:10 AM 7/4/2002 -0700, bob ackerman wrote:

>On Wednesday, July 3, 2002, at 08:53  PM, Ricardo Rivaldo wrote:
>
>>Does anyone have a sample source code on how to use pyPgSQL ? Please send 
>>me if you have !
>>
>>Rivaldo
>
>i import it, but i use the lowlevel libpq, not the higher level wrapper:
>
>from pyPgSQL import libpq
>
>db = libpq.PQconnectdb('dbname=shopip')
>                 qry = "SELECT * from %s" % tbl_name
>qrslt = db.query(qry)
>for x in range(qrslt.ntuples):
>         for y in range(qrslt.nfields):
>                 data_val = qrslt.getvalue(x,y)
>
>you could of course look at documentation? in Python library docs?
>
>hth
>
>pob
>
>
>
>-------------------------------------------------------
>This sf.net email is sponsored by:ThinkGeek
>Caffeinated soap. No kidding.
>http://thinkgeek.com/sf
>_______________________________________________
>Pypgsql-users mailing list
>Pyp...@li...
>https://lists.sourceforge.net/lists/listinfo/pypgsql-users
>
>

Re: [Pypgsql-users] Sample source code needed

[bob a. <rd...@pa...>]

On Wednesday, July 3, 2002, at 08:53  PM, Ricardo Rivaldo wrote:

> Does anyone have a sample source code on how to use pyPgSQL ? Please send 
> me if you have !
>
> Rivaldo
>

i import it, but i use the lowlevel libpq, not the higher level wrapper:

from pyPgSQL import libpq

db = libpq.PQconnectdb('dbname=shopip')
		qry = "SELECT * from %s" % tbl_name
qrslt = db.query(qry)
for x in range(qrslt.ntuples):
	for y in range(qrslt.nfields):
		data_val = qrslt.getvalue(x,y)

you could of course look at documentation? in Python library docs?

hth

pob