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-----