Re: PgNumeric and NULL values
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....: Bill.Allie@...
| /| | 7436 Hartwell | MSN.......: B_G_Allie@...
|-/-|----- | Dearborn, MI 48126|
|/ |LLIE | (313) 582-1540 |
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,
RSS Feed