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

[Pypgsql-users] OpenBSD3.4 + Python 2.3.3 + pyPgSQL 2.4 Error

[Sean E. <se...@es...>]

I have successfully installed pyPgSQL 2.4 on a OpenBSD3.4 system 
running Python 2.3.3, but encounter a strange error.

 From python, when:

from pyPgSQL import PgSQL

is executed, the interpreter simple dies and I am taken back to the 
command line. This is regardless if I'm doing it by hand in the 
interpreter or have the 'from pyPgSQL import PgSQL' statement inside a 
python file.

I attempted to check the archives, but there was an error.

Any ideas on what this is, or how to obtain some sort of error log? (I 
assume there's some general error log where the error may be caught, 
I'm just not *nix savvy enough to know it.)

Thanks,
--Sean

[Pypgsql-users] PyPgSQL Zope Adaptor (via PyTable)

[Mike C. F. <mcf...@ro...>]

Hi all,

I've just released a Zope Adapter for PyTable (my database-wrapper 
system).  It's mostly just an experiment at the moment (I needed to have 
an exUserFolder and I wanted to run it using the same DB adapter 
(PyPgSQL) as the rest of the app, and I secondarily wanted to use the 
same mechanism for defining/controlling the connections as for rest of 
the app (i.e. PyTable)).

The adapter is based on the (Zope-licensed) PySQLite adapter, so may be 
missing some features compared to the PoPy one.  It will let you choose 
databases from the available databases on a server (if you have access 
to log on to the server, anyway), it will display the table/field schema 
as discovered on the server, and it will return the results of queries 
against the database.  The code for formatting the results from the 
queries still seems a little... primitive... PySQLite basically has 
strings everywhere... so I'll likely try to figure out what Zope really 
wants in those situations and try to provide that...

Anyway, just thought I'd let people know there's another DA available 
for use with Zope + PyPgSQL.

    http://pytable.sourceforge.net/

Have fun,
Mike

_______________________________________
  Mike C. Fletcher
  Designer, VR Plumber, Coder
  http://members.rogers.com/mcfletch/

[Pypgsql-users] Strange bug with Postgresql 7.3.4 and pyPgSQL 2.4

[Santtu P. <ja...@ra...>]

I'm having disturbing problems with PostgreSQL 7.3.4, Python 2.3.3 and
pyPgSQL 2.4 on Gentoo Linux. PostgreSQL is emerged and has its
includes, libs etc. in standard places, and is working AFAIK. Python
2.3 is installed from sources under /usr/local and there's Gentoo's
standard Python (2.2.3) installation in the usual place. Now I'm
trying to install pyPgSQL to /usr/local.

The install script builds and installs the package without errors,
however giving lots of warnings about redefining _POSIX_C_SOURCE. The
pyPgSQL module can be imported into Python but it does not seem to
contain the objects I think it should have - only the __ things are
there. libpq can be imported but it doesn't have anything
useful. Then, if I try to import PgSQL, Python crashes with a
segmentation fault. Somebody please check the attached outputs, there
are some really weird and disturbing things around...

Is anyone else experiencing this, and is there a way around?


Some outputs:

oryx pypgsql # python2.3 setup.py build
/usr/local/lib/python2.3/distutils/dist.py:213: UserWarning: 'licence' distribution option is deprecated; use 'license'
  warnings.warn(msg)
running build
running build_py
creating build
creating build/lib.linux-i686-2.3
creating build/lib.linux-i686-2.3/pyPgSQL
copying pyPgSQL/__init__.py -> build/lib.linux-i686-2.3/pyPgSQL
copying pyPgSQL/PgSQL.py -> build/lib.linux-i686-2.3/pyPgSQL
creating build/lib.linux-i686-2.3/pyPgSQL/libpq
copying pyPgSQL/libpq/__init__.py -> build/lib.linux-i686-2.3/pyPgSQL/libpq
running build_ext
building 'pyPgSQL.libpq.libpqmodule' extension
creating build/temp.linux-i686-2.3
creating build/temp.linux-i686-2.3/port
gcc -fno-strict-aliasing -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -fPIC -I/usr/include -I/usr/include/postgresql -I/usr/include/pgsql -I../../../../../ -I/usr/local/include/python2.3 -c pgnotify.c -o build/temp.linux-i686-2.3/pgnotify.o
In file included from /usr/local/include/python2.3/Python.h:8,
                 from pgnotify.c:51:
/usr/local/include/python2.3/pyconfig.h:847:1: warning: "_POSIX_C_SOURCE" redefined
In file included from /usr/include/string.h:26,
                 from pgnotify.c:47:
/usr/include/features.h:171:1: warning: this is the location of the previous definition
gcc -fno-strict-aliasing -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -fPIC -I/usr/include -I/usr/include/postgresql -I/usr/include/pgsql -I../../../../../ -I/usr/local/include/python2.3 -c pgconnection.c -o build/temp.linux-i686-2.3/pgconnection.o
In file included from /usr/local/include/python2.3/Python.h:8,
                 from pgconnection.c:93:
/usr/local/include/python2.3/pyconfig.h:847:1: warning: "_POSIX_C_SOURCE" redefined
In file included from /usr/include/string.h:26,
                 from pgconnection.c:89:
/usr/include/features.h:171:1: warning: this is the location of the previous definition

...

other objects compiled similarly

...

gcc -fno-strict-aliasing -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -fPIC -I/usr/include -I/usr/include/postgresql -I/usr/include/pgsql -I../../../../../ -I/usr/local/include/python2.3 -c pgversion.c -o build/temp.linux-i686-2.3/pgversion.o
In file included from /usr/local/include/python2.3/Python.h:8,
                 from pgversion.c:97:
/usr/local/include/python2.3/pyconfig.h:847:1: warning: "_POSIX_C_SOURCE" redefined
In file included from /usr/include/string.h:26,
                 from pgversion.c:91:
/usr/include/features.h:171:1: warning: this is the location of the previous definition
gcc -pthread -shared build/temp.linux-i686-2.3/libpqmodule.o build/temp.linux-i686-2.3/pgboolean.o build/temp.linux-i686-2.3/pgint2object.o build/temp.linux-i686-2.3/pgint8object.o build/temp.linux-i686-2.3/pgversion.o build/temp.linux-i686-2.3/pglargeobject.o build/temp.linux-i686-2.3/pgnotify.o build/temp.linux-i686-2.3/pgconnection.o build/temp.linux-i686-2.3/pgresult.o build/temp.linux-i686-2.3/pymemstrdup.o build/temp.linux-i686-2.3/port/strtoll.o build/temp.linux-i686-2.3/port/strtoull.o build/temp.linux-i686-2.3/port/strtok.o -L/usr/lib -lpq -o build/lib.linux-i686-2.3/pyPgSQL/libpq/libpqmodule.so




oryx pypgsql # python2.3 setup.py install
/usr/local/lib/python2.3/distutils/dist.py:213: UserWarning: 'licence' distribution option is deprecated; use 'license'
  warnings.warn(msg)
running install
running build
running build_py
running build_ext
running install_lib
creating /usr/local/lib/python2.3/site-packages/pyPgSQL
copying build/lib.linux-i686-2.3/pyPgSQL/__init__.py -> /usr/local/lib/python2.3/site-packages/pyPgSQL
copying build/lib.linux-i686-2.3/pyPgSQL/PgSQL.py -> /usr/local/lib/python2.3/site-packages/pyPgSQL
creating /usr/local/lib/python2.3/site-packages/pyPgSQL/libpq
copying build/lib.linux-i686-2.3/pyPgSQL/libpq/__init__.py -> /usr/local/lib/python2.3/site-packages/pyPgSQL/libpq
copying build/lib.linux-i686-2.3/pyPgSQL/libpq/libpqmodule.so -> /usr/local/lib/python2.3/site-packages/pyPgSQL/libpq
byte-compiling /usr/local/lib/python2.3/site-packages/pyPgSQL/__init__.py to __init__.pyc
byte-compiling /usr/local/lib/python2.3/site-packages/pyPgSQL/PgSQL.py to PgSQL.pyc
byte-compiling /usr/local/lib/python2.3/site-packages/pyPgSQL/libpq/__init__.py to __init__.pyc




oryx pypgsql # python2.3
Python 2.3.3 (#1, Dec 26 2003, 18:29:55)
[GCC 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r3, propolice)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from pyPgSQL import libpq
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "pyPgSQL/libpq/__init__.py", line 23, in ?
    from libpq import *
ImportError: No module named libpq
>>> from pyPgSQL import PgSQL
Segmentation fault




oryx pypgsql # python2.3
Python 2.3.3 (#1, Dec 26 2003, 18:29:55)
[GCC 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r3, propolice)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyPgSQL
>>> dir(pyPgSQL)
['__builtins__', '__doc__', '__file__', '__name__', '__path__', '__version__']
>>> from pyPgSQL import libpq
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "pyPgSQL/libpq/__init__.py", line 23, in ?
    from libpq import *
ImportError: No module named libpq
>>> dir(pyPgSQL)
['__builtins__', '__doc__', '__file__', '__name__', '__path__', '__version__', 'libpq']
>>> from pyPgSQL import libpq
>>> dir(libpq)
['__builtins__', '__doc__', '__file__', '__name__', '__path__']
>>> dir(pyPgSQL.libpq)
['__builtins__', '__doc__', '__file__', '__name__', '__path__']
>>> libpq.PQconnect
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
AttributeError: 'module' object has no attribute 'PQconnect'
>>> from pyPgSQL import PgSQL
Segmentation fault



Versions:

oryx pypgsql # uname -a
Linux oryx 2.6.0-test11-oryx03 #1 Sat Nov 29 20:22:08 EET 2003 i686 Celeron (Mendocino) GenuineIntel GNU/Linux
oryx pypgsql # gcc --version
gcc (GCC) 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r3, propolice)
Copyright (C) 2002 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

oryx pypgsql # psql --version
psql (PostgreSQL) 7.3.4
contains support for command-line editing
oryx pypgsql # python2.3 -V
Python 2.3.3
oryx pypgsql # head README
:Version: #ident "@(#) $Id: README,v 1.32 2003/07/14 21:35:57 ballie01 Exp $"

----------------------------------------------------------------------------
pyPgSQL - v2.4: Python DB-API 2.0 Compliant Interface Module for PostgreSQL.
----------------------------------------------------------------------------
===============================
0. Copyright notice and License
===============================

    pyPgSQL, version 2.4


-- 
-- Santtu "Japsu" Pajukanta
-- ja...@ra...

"Economics is extremely useful as a form of employment for
economists.", -John Kenneth Galbraith

[Pypgsql-users] Interval Type Mapping

[Francois G. <fra...@cl...>]

Hi all,

I'm using interval data type in postgres.

pypgsql returns a DateTimeDelta instance when retrieving such values.

Is there any plan to use RelativeDateTime, cause interval <->
DateTimeDelta is destructive on client side.

With DateTimeDelta, years can't be specified and all years haven't 365
days. Even saying a year is  365.2425 days, working with months is worse
:(

Any turn-around ?=20

Or, if I want to do it myself, is interval2DateTimeDelta the only place
to look at for returning RelativeDateTime instead of DateTimeDelta ?

Thanks for any help.

Fran=E7ois=20

Re: [Pypgsql-users] PgNumeric and NULL values

[Ivar Z. <if...@al...>]

On Tue, Dec 16, 2003 at 11:58:14PM -0500, Billy G. Allie wrote:

> The following patch will correct the problem.  I also updated CVS on
> sourceforge.

Thanks!!

-- 
Ivar

Re: [Pypgsql-users] PgNumeric and NULL values

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ivar Zarans wrote:
| On Sun, Dec 14, 2003 at 08:53:33PM -0500, Billy G. Allie wrote:
|
|
|>| Updating numeric field with values of '0.00' generates query, trying
|>| update with 'NULL'. Since column is defined as 'NOT NULL', update fails
|>
|>Can you post sample code that exhibits this behavior please.
|
|
| Creaed table as:
| create table testtable (
|     ident int not null,
|     price numeric not null
| );
|
| Inserted test values:
| insert into testtable (ident, price) values (10, 0.00);
|
| Running follofing short test:
| ====
| #!/usr/bin/env python
|
| from pyPgSQL import PgSQL
|
| connstr = '::DBname:DBuser:DBpass:'
| DBConn = PgSQL.connect(connstr)
| Cursor = DBConn.cursor()
|
| price = PgSQL.PgNumeric('0.00')
| ident = 10
|
| qry = """
| UPDATE
|     testtable
| SET
|     price = %s
| WHERE
|     ident = %s
| """
|
| Cursor.execute(qry, price, ident)
| ====
|
| throws exception:
|
| ====
| Traceback (most recent call last):
|   File "./numtest.py", line 21, in ?
|       Cursor.execute(qry, price, ident)
|   File "/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 3072,
|                 in execute
|       raise OperationalError, msg
|       libpq.OperationalError: ERROR:  ExecUpdate:
|                 Fail to add null value in not null attribute price
| ====
|
| PostgreSQL log has following query:
|
| ====
| postgres[23448]: [240-1] LOG:  query:
| postgres[23448]: [240-2] UPDATE
| postgres[23448]: [240-3]     testtable
| postgres[23448]: [240-4] SET
| postgres[23448]: [240-5]     price = NULL
| postgres[23448]: [240-6] WHERE
| postgres[23448]: [240-7]     ident = 10
| postgres[23448]: [241] ERROR:  ExecUpdate: Fail to add null value in
not null attribute price
| postgres[23448]: [242] DEBUG:  AbortCurrentTransaction
| postgres[23448]: [243] DEBUG:  StartTransactionCommand
| postgres[23448]: [244] LOG:  query: ROLLBACK WORK
| ====
|
| PostgreSQL version 7.3.4
| PyPgSQL version 2.4
| OS is Debian Woody
|

The following patch will correct the problem.  I also updated CVS on
sourceforge.

*** pyPgSQL/PgSQL.py    22 Nov 2003 05:35:47 -0000      1.38
- --- pyPgSQL/PgSQL.py    17 Dec 2003 04:49:08 -0000
***************
*** 1674,1680 ****
~            return PgNumeric(-self.__v, self.__p, self.__s)

~      def _quote(self, forArray=0):
!       if self.__v:
~            if forArray:
~                return '"%s"' % self.__fmtNumeric()
~            else:
- --- 1674,1680 ----
~            return PgNumeric(-self.__v, self.__p, self.__s)

~      def _quote(self, forArray=0):
!       if self.__v != None:
~            if forArray:
~                return '"%s"' % self.__fmtNumeric()
~            else:



- --
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/3+JmnmIkMXoVVdURAtU8AJ9wBHD1WPT+M5ulypbBCCbgR1FLBACgkmRp
h+sD0X1dRRnYXGkTYcFkV/c=
=1mm6
-----END PGP SIGNATURE-----

Re: [Pypgsql-users] PgNumeric and NULL values

[Ivar Z. <if...@al...>]

On Sun, Dec 14, 2003 at 08:53:33PM -0500, Billy G. Allie wrote:

> | Updating numeric field with values of '0.00' generates query, trying
> | update with 'NULL'. Since column is defined as 'NOT NULL', update fails
> 
> Can you post sample code that exhibits this behavior please.

Creaed table as:
create table testtable (
    ident int not null,
    price numeric not null
);

Inserted test values:
insert into testtable (ident, price) values (10, 0.00);

Running follofing short test:
====
#!/usr/bin/env python

from pyPgSQL import PgSQL

connstr = '::DBname:DBuser:DBpass:'
DBConn = PgSQL.connect(connstr)
Cursor = DBConn.cursor()

price = PgSQL.PgNumeric('0.00')
ident = 10

qry = """
UPDATE
    testtable
SET
    price = %s
WHERE
    ident = %s
"""

Cursor.execute(qry, price, ident)
====

throws exception:

====
Traceback (most recent call last):
  File "./numtest.py", line 21, in ?
      Cursor.execute(qry, price, ident)
  File "/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 3072,
                in execute
      raise OperationalError, msg
      libpq.OperationalError: ERROR:  ExecUpdate: 
                Fail to add null value in not null attribute price
====

PostgreSQL log has following query:

====
postgres[23448]: [240-1] LOG:  query:
postgres[23448]: [240-2] UPDATE
postgres[23448]: [240-3]     testtable
postgres[23448]: [240-4] SET
postgres[23448]: [240-5]     price = NULL
postgres[23448]: [240-6] WHERE
postgres[23448]: [240-7]     ident = 10
postgres[23448]: [241] ERROR:  ExecUpdate: Fail to add null value in not null attribute price
postgres[23448]: [242] DEBUG:  AbortCurrentTransaction
postgres[23448]: [243] DEBUG:  StartTransactionCommand
postgres[23448]: [244] LOG:  query: ROLLBACK WORK
====

PostgreSQL version 7.3.4
PyPgSQL version 2.4
OS is Debian Woody


-- 
Ivar

[Pypgsql-users] Postgres 7.4

[Shawn H. <har...@tb...>]

Hi, folks,

I just began using pyPgSQL with my current postgres (7.2) and would like =
to upgrade to pg 7.4. I read in the docs somewhere that pyPgSQL is not =
tested with pg 7.4, and when I tried to set it up I got  a couple of =
errors.

What is the status of porting to pg 7.4? I could re-try the setup and =
post the error reports, if that would be helpful.

Shawn Harrison

Re: [Pypgsql-users] PgNumeric and NULL values

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ivar Zarans wrote:
| Hello!
|
| I am playing around with PyPgSQL and found interesting behaviour:
| Updating numeric field with values of '0.00' generates query, trying
| update with 'NULL'. Since column is defined as 'NOT NULL', update fails
| with exception:
| "OperationalError: ERROR:  ExecUpdate: Fail to add null value in not
| null attribute... "
|
| Is this normal?
|

Can you post sample code that exhibits this behavior please.

Thanks.

- --
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/3RQcnmIkMXoVVdURAtxtAKCKW2GNrimqYmNDbqShJHrHVnLI4ACeOrdf
SpyoSH5VpPYED/P12NW+vJ8=
=jpdR
-----END PGP SIGNATURE-----

[Pypgsql-users] PgNumeric and NULL values

[Ivar Z. <if...@al...>]

Hello!

I am playing around with PyPgSQL and found interesting behaviour:
Updating numeric field with values of '0.00' generates query, trying
update with 'NULL'. Since column is defined as 'NOT NULL', update fails
with exception:
"OperationalError: ERROR:  ExecUpdate: Fail to add null value in not
null attribute... "

Is this normal?

-- 
Best regards,
Ivar Zarans

[Pypgsql-users] PgNumeric and NULL values

[Ivar Z. <if...@al...>]

Hello!

I am playing around with PyPgSQL and found interesting behaviour:
Updating numeric field with values of '0.00' generates query, trying
update with 'NULL'. Since column is defined as 'NOT NULL', update fails
with exception:
"OperationalError: ERROR:  ExecUpdate: Fail to add null value in not null
attribute... "

Is this normal? 

-- 
Best regards,
Ivar Zarans

[Pypgsql-users] Re: Buliding on SuSE 8 for x86_64 (AMD Opteron)

[Paul K. <ko...@cm...>]

On Sat, 2003-12-13 at 17:12, Paul Komarek wrote:
> Hi,
> 
> SuSE 8 for x86_64 has /usr/lib for 32 bit stuff, and /usr/lib64 for 64
> bit stuff.  /usr/bin/python is 64 bit.  There is /usr/lib64/libpq.so.3
> and /usr/lib/libpq.so.2.  You can imagine the mess this makes for
> distutils.
> 
> I sucessfully compiled and installed pyPgSQL 2.4.0 from the source
> tarball.  I made the following customizations:
> 
> 1) Using the CUSTOM stuff in setup.py, set library_dirs = ["/usr/lib64",
> "/usr/lib"]
> 
> 2) After installing with python setup.py instal, stuff gets installed in
> /usr/lib/python2.2/site-packages and you are warned this isn't in the
> search path.  Simply cp -a /usr/lib/python2.2/site-packages/pyPgSQL to
> /usr/lib64/python2.2/site-packages/.
> 
> I figure distutils may be able to do this kind of thing automatically.
> Today is the first time I looked into distutils, and I'm not going to
> mess around with fancy automatic stuff.
> 
> egenix mxDateTime also gave me some grief because their configuration
> isn't documented and isn't as nice as yours.  To make it work, I edited
> mxSetup.py (which I think gets copied around, so modify all copies you
> find in the tarball before building).  The change is again simple:
> 
> 1) search for /usr/local/lib in mxSetup.py
> 2) prepend '/usr/local/lib64' and '/usr/lib64' to the default library
> list.
> 
> After building and installing, cp -a /usr/lib/python2.2/site-packages/mx
> /usr/lib64/python2.2/site-packages.
> 
> To make this email easier to find, I'll include the error messages you
> get if you don't do this stuff:
> 
> /usr/lib64/gcc-lib/x86_64-suse-linux/3.2.2/../../../../x86_64-suse-linux/bin/ld: cannot find /lib/libc.so.6
> collect2: ld returned 1 exit status
> error: command 'gcc' failed with exit status 1
> 
> The same errors occur for egenix-mx-base (for which I didn't find a good
> email list to post this to).
> 
> Note that I am not subscribed to this list.  If someone feels the need
> to get my attention, you can email me at ko...@cm....
> 
> -Paul Komarek

I should mention that there's a /lib in the egenix mxSetup.py library
lists that needs to be preceded by /lib64, too.

-Paul Komarek

[Pypgsql-users] Buliding on SuSE 8 for x86_64 (AMD Opteron)

[Paul K. <ko...@cm...>]

Hi,

SuSE 8 for x86_64 has /usr/lib for 32 bit stuff, and /usr/lib64 for 64
bit stuff.  /usr/bin/python is 64 bit.  There is /usr/lib64/libpq.so.3
and /usr/lib/libpq.so.2.  You can imagine the mess this makes for
distutils.

I sucessfully compiled and installed pyPgSQL 2.4.0 from the source
tarball.  I made the following customizations:

1) Using the CUSTOM stuff in setup.py, set library_dirs = ["/usr/lib64",
"/usr/lib"]

2) After installing with python setup.py instal, stuff gets installed in
/usr/lib/python2.2/site-packages and you are warned this isn't in the
search path.  Simply cp -a /usr/lib/python2.2/site-packages/pyPgSQL to
/usr/lib64/python2.2/site-packages/.

I figure distutils may be able to do this kind of thing automatically.
Today is the first time I looked into distutils, and I'm not going to
mess around with fancy automatic stuff.

egenix mxDateTime also gave me some grief because their configuration
isn't documented and isn't as nice as yours.  To make it work, I edited
mxSetup.py (which I think gets copied around, so modify all copies you
find in the tarball before building).  The change is again simple:

1) search for /usr/local/lib in mxSetup.py
2) prepend '/usr/local/lib64' and '/usr/lib64' to the default library
list.

After building and installing, cp -a /usr/lib/python2.2/site-packages/mx
/usr/lib64/python2.2/site-packages.

To make this email easier to find, I'll include the error messages you
get if you don't do this stuff:

/usr/lib64/gcc-lib/x86_64-suse-linux/3.2.2/../../../../x86_64-suse-linux/bin/ld: cannot find /lib/libc.so.6
collect2: ld returned 1 exit status
error: command 'gcc' failed with exit status 1

The same errors occur for egenix-mx-base (for which I didn't find a good
email list to post this to).

Note that I am not subscribed to this list.  If someone feels the need
to get my attention, you can email me at ko...@cm....

-Paul Komarek

Re: [Pypgsql-users] Exception: "VACUUM cannot run inside a BEGIN/END block"

[Rene P. <re...@la...>]

Billy G. Allie:
>Of course, using the libpq.conn.query method as suggested by Adam avoids=
=20
>the need to create a cursor object, but you still need to be sure that=20
>there are no open cursors for the connection when you use (opening a=20
>connection with autocommit =3D=3D 0 will open a transaction at cursor=20
>creations time).

I see. I'm doing vacuum halfway through my algorithm (after
batch inserts/updates, before selects) and I'm beginning to
think its best to do the vacuum on a separate connection with
autocommit enabled.

--=20
Regards / Groeten,  http://www.leren.nl
Ren=E9 Pijlman        http://www.applinet.nl
 =20

Re: [Pypgsql-users] Exception: "VACUUM cannot run inside a BEGIN/END block"

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

Adam Buraczewski wrote:

>On Sat, Nov 29, 2003 at 06:13:35PM +0100, Karsten Hilbert wrote:
>  
>
>>cursor.execute('end;vacuum analyze;begin;')
>>we use this successfully around "create database"
>>    
>>
>
>Don't you have a feeling it's a bit ugly? ;-)  DBI, and especially
>cursor objects, weren't invented for DDL commands, only for DMLs.
>Personally, I think that DBI spec should be somehow enhanced so that
>it would cover such situations.  As an acceptable solution I usually
>use plain libpq module here.  The goal can be achieved with following
>code:
>
>	from pyPgSQL import PgSQL
>	conn = PgSQL.connect(...)
>	...
>	conn.conn.query("vacuum analyze")
>
>Of course it will work with pyPgSQL only :^)
>
>Best regards,
>
>  
>
Actually, you can just set autocommit to on (per the DB-API spec it's 
off by default).  If autocommit is on, then pyPgSQL won't wrap the query 
within a begin ... end block.

from pyPgSQL import PgSQL
conn - PgSQL.connect(...)
conn.autocommit = 1
curs = conn.cursor()
curs.execute("vacuum analyze")
  

*Note: *You must set autocommit to 0 /before/ creating any cursors.

Of course, using the libpq.conn.query method as suggested by Adam avoids 
the need to create a cursor object, but you still need to be sure that 
there are no open cursors for the connection when you use (opening a 
connection with autocommit == 0 will open a transaction at cursor 
creations time).

Re: [Pypgsql-users] Exception: "VACUUM cannot run inside a BEGIN/END block"

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

On Sat, Nov 29, 2003 at 06:13:35PM +0100, Karsten Hilbert wrote:
> cursor.execute('end;vacuum analyze;begin;')
> we use this successfully around "create database"

Don't you have a feeling it's a bit ugly? ;-)  DBI, and especially
cursor objects, weren't invented for DDL commands, only for DMLs.
Personally, I think that DBI spec should be somehow enhanced so that
it would cover such situations.  As an acceptable solution I usually
use plain libpq module here.  The goal can be achieved with following
code:

	from pyPgSQL import PgSQL
	conn = PgSQL.connect(...)
	...
	conn.conn.query("vacuum analyze")

Of course it will work with pyPgSQL only :^)

Best regards,

-- 
Adam Buraczewski <adamb (at) nor (dot) pl> * Linux 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] Exception: "VACUUM cannot run inside a BEGIN/END block"

[Karsten H. <Kar...@gm...>]

> Thanks Karsten. It looks like a bug to me
I don't think it's a bug but we'd have to check the PG docs to see
what's the rationale behind vacuum analyze having to be outside
a transaction.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: [Pypgsql-users] Exception: "VACUUM cannot run inside a BEGIN/END block"

[Rene P. <re...@la...>]

Karsten Hilbert:
>> cursor.execute("vacuum analyze")
>cursor.execute('end;vacuum analyze;begin;')

Thanks Karsten. It looks like a bug to me, but this is a good
workaround.

I'm too new to pyPgSQL to suggest a patch. Maybe later.

--=20
Regards / Groeten,  http://www.leren.nl
Ren=E9 Pijlman        http://www.applinet.nl
 =20

Re: [Pypgsql-users] Exception: "VACUUM cannot run inside a BEGIN/END block"

[Karsten H. <Kar...@gm...>]

> cursor.execute("vacuum analyze")
cursor.execute('end;vacuum analyze;begin;')

we use this successfully around "create database"
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

[Pypgsql-users] Exception: "VACUUM cannot run inside a BEGIN/END block"

[Rene P. <re...@la...>]

Hello,

When I try to issue the statement "vacuum analyze" I get an
exception with the error message "VACUUM cannot run inside a
BEGIN/END block".

This is the code that reproduces the problem:

from pyPgSQL import PgSQL
db =3D PgSQL.connect(host=3D"myhost", database=3D"mydb",
                   user=3D"myuser", password=3D"mypw")
cursor =3D db.cursor()
cursor.execute("vacuum analyze")

I use pyPgSQL version 2.4 with Python 2.2 on Windows XP. The
server is PostgreSQL 7.2.1 on Linux.

Is this considered a bug in pyPgSQL? Is there any way to issue
"vacuum analyze" from within a Python program?

The reason I'm doing this is that my program inserts and deletes
lots of records. And the PostgreSQL manual says: "After adding
or deleting a large number of records, it may be a good idea to
issue a VACUUM ANALYZE command for the affected table. This will
update the system catalogs with the results of all recent
changes, and allow the PostgreSQL query optimizer to make better
choices in planning user queries."

Thanks in advance for your help.

--=20
Regards / Groeten,  http://www.leren.nl
Ren=E9 Pijlman        http://www.applinet.nl
 =20

Re: [Pypgsql-users] problem with single-item lists and IN operator

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

Karsten Hilbert wrote:

>Hi,
>
>I am trying to use the IN operator with a tuple of length 1
>(no, I can't change to = since I don't know in advance whether
>the tuple will be of size 1 or longer).
>
>If I simply pass the tuple to cursor.execute() it gets
>unrolled producing one of two errors:
> if length == 1:
>    select * from ... where ... IN scalar_value;
> if length > 1:
>    Exception: not all arguments converted
>
>If I wrap the tuple in another tuple or list of length 1, eg.
>(my_tuple,) or [my_tuple] then cursor.execute does recognize
>that it is supposed to build a list suitable for IN. However,
>this fails with tuples of size 1:
>
>select * from ... where ... IN (scalar_value,);
>
>tuples with size > 1 work fine:
>
>select * from ... where ... IN (scalar, scalar);
>
>I did upgrade to 2.4 since the changelog said something about
>a fix to the parameter handling concerned with IN operators.
>That didn't help, unfortunately.
>
>And this line in PgSQL -> cursor.execute() seems to be the
>root of the evil:
>
>	self.res = self.conn.conn.query(_qstr % parms)
>
>What am I to do ?
>
>Karsten
>  
>
   1. Get the latest PgSQL.py from CVS.  It contains additional fixes
      for using the IN operator.
   2. If the list to use in the IN statement is the only parameter, then
      you will need to use the following syntax:
      <assume inparms s the tuple to use in the IN statement>
      cursor.execute("select * from ... where ... n %s", (inparms, ))

    This is necessary due to the way python handles varying parameters. 
    If parms is a list, it is assumed to be multiple parameters to be
    quoted and placed into the query.   If the inparms was one of many
    parameters being sent to the query, it would not need to be wrapped
    in a tuple.


-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

[Pypgsql-users] problem with single-item lists and IN operator

[Karsten H. <Kar...@gm...>]

Hi,

I am trying to use the IN operator with a tuple of length 1
(no, I can't change to = since I don't know in advance whether
the tuple will be of size 1 or longer).

If I simply pass the tuple to cursor.execute() it gets
unrolled producing one of two errors:
 if length == 1:
    select * from ... where ... IN scalar_value;
 if length > 1:
    Exception: not all arguments converted

If I wrap the tuple in another tuple or list of length 1, eg.
(my_tuple,) or [my_tuple] then cursor.execute does recognize
that it is supposed to build a list suitable for IN. However,
this fails with tuples of size 1:

select * from ... where ... IN (scalar_value,);

tuples with size > 1 work fine:

select * from ... where ... IN (scalar, scalar);

I did upgrade to 2.4 since the changelog said something about
a fix to the parameter handling concerned with IN operators.
That didn't help, unfortunately.

And this line in PgSQL -> cursor.execute() seems to be the
root of the evil:

	self.res = self.conn.conn.query(_qstr % parms)

What am I to do ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: [Pypgsql-users] bytea quoting problems

[Greg L. <gre...@ea...>]

At 10:04 AM 10/31/2003, Billy G. Allie wrote:
>Greg Landrum wrote:
>>
>>Greetings,
>>
>>I'm just getting starting with pyPgSQL (I'm in the process of switching 
>>over from Interbase/Firebird) and I've hit some odd behavior with bytea 
>>columns.
>>
>>I am trying to store pickles in bytea columns of a table and I appear to 
>>be encountering problems in the quoting rules.  Here's a session:
>>
>You need to tell pyPgSQL that you are inserting a bytea string. you can do 
>this as follows:
>
>curs.execute('insert into throwaway values (%s)', 
>PgBytea(cPickle.dumps(range(1,129))))I hope this answers your question.

It does, thanks for the help.  I had assumed (incorrectly!) that the 
adaptor would magically figure this stuff out for me.  The PgBytea thing 
does the trick though.  Thanks!

-greg

----
greg Landrum (gre...@ea...)
Software Carpenter/Computational Chemist

Re: [Pypgsql-users] bytea quoting problems

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

Greg Landrum wrote:

>Greetings,
>
>I'm just getting starting with pyPgSQL (I'm in the process of switching over from Interbase/Firebird) and I've hit some odd behavior with bytea columns.
>
>I am trying to store pickles in bytea columns of a table and I appear to be encountering problems in the quoting rules.  Here's a session:
>
>-------------------------------
>~ > uname -a
>Linux badger 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47 EDT 2002 i686 i686 i386 GNU/Linux
>~ > python
>Python 2.2.3 (#1, Aug  6 2003, 09:41:11)
>[GCC 3.2 20020903 (Red Hat Linux 8.0 3.2-7)] on linux2
>Type "help", "copyright", "credits" or "license" for more information.
>  
>
>>>>from pyPgSQL import PgSQL
>>>>PgSQL.version
>>>>        
>>>>
>'$Revision: 1.35 $'
>  
>
>>>>import cPickle
>>>>conn = PgSQL.connect()
>>>>curs = conn.cursor()
>>>>curs.execute('create table throwaway (guts bytea)')
>>>>curs.commit()
>>>>        
>>>>
># start adding values:
>  
>
>>>>curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(64)),))
>>>>        
>>>>
># that worked, but try a binary pickle:
>  
>
>>>>curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(64),1)))
>>>>        
>>>>
>Traceback (most recent call last):
>  File "<stdin>", line 1, in ?
>  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 3047, in execute
>    parms = tuple(map(_quote, parms));
>  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 2261, in _quote
>    return PgQuoteString(value)
>TypeError: PgQuoteString() argument 1 must be string without null bytes, not str
># we can clear that up by not starting at 0:
>  
>
>>>>curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(1,64),1)))
>>>>        
>>>>
># but then we get into trouble again (but a different kind this time) with larger values:
>  
>
>>>>curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(1,129),1)))
>>>>        
>>>>
>Traceback (most recent call last):
>  File "<stdin>", line 1, in ?
>  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 3072, in execute
>    raise OperationalError, msg
>libpq.OperationalError: ERROR:  Bad input string for type bytea
># everything is still fine with text pickles though:
>  
>
>>>>curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(1,129))))
>>>>
>>>>        
>>>>
>-------------------------------
>
>I did a bit of googling and couldn't find this problem mentioned.  
>
>Is this user error or is there something wrong in pyPgSQL itself?
>  
>
You need to tell pyPgSQL that you are inserting a bytea string. you can 
do this as follows:

curs.execute('insert into throwaway values (%s)', PgBytea(cPickle.dumps(range(1,129))))

I hope this answers your question.

-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |

[Pypgsql-users] bytea quoting problems

[Greg L. <gre...@ea...>]

Greetings,

I'm just getting starting with pyPgSQL (I'm in the process of switching over from Interbase/Firebird) and I've hit some odd behavior with bytea columns.

I am trying to store pickles in bytea columns of a table and I appear to be encountering problems in the quoting rules.  Here's a session:

-------------------------------
~ > uname -a
Linux badger 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47 EDT 2002 i686 i686 i386 GNU/Linux
~ > python
Python 2.2.3 (#1, Aug  6 2003, 09:41:11)
[GCC 3.2 20020903 (Red Hat Linux 8.0 3.2-7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from pyPgSQL import PgSQL
>>> PgSQL.version
'$Revision: 1.35 $'
>>> import cPickle
>>> conn = PgSQL.connect()
>>> curs = conn.cursor()
>>> curs.execute('create table throwaway (guts bytea)')
>>> curs.commit()
# start adding values:
>>> curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(64)),))
# that worked, but try a binary pickle:
>>> curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(64),1)))
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 3047, in execute
    parms = tuple(map(_quote, parms));
  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 2261, in _quote
    return PgQuoteString(value)
TypeError: PgQuoteString() argument 1 must be string without null bytes, not str
# we can clear that up by not starting at 0:
>>> curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(1,64),1)))
# but then we get into trouble again (but a different kind this time) with larger values:
>>> curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(1,129),1)))
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 3072, in execute
    raise OperationalError, msg
libpq.OperationalError: ERROR:  Bad input string for type bytea
# everything is still fine with text pickles though:
>>> curs.execute('insert into throwaway values (%s)',(cPickle.dumps(range(1,129))))
>>>
-------------------------------

I did a bit of googling and couldn't find this problem mentioned.  

Is this user error or is there something wrong in pyPgSQL itself?

Thanks,
-greg
----
greg Landrum (gre...@ea...)
Software Carpenter/Computational Chemist