Thread: [SQLObject] Decimal type problem
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Sam N. <li...@se...> - 2005-08-20 21:03:12
|
Hello, I'm having a basic problem using Decimal (actually Currency) columns. I'm using SQLObject .0.6.1 and I have two classes that each have CurrencyCol columns. In the example below, I create a TestOne instance (t1). Then I create a TestTwo instance (t2) setting t2.unitPrice=t1.unitPrice. This raises an exception: ValueError: Unknown SQL builtin type: <class 'decimal.Decimal'> for Decimal("25.00"). I found that if I set t2.unitPrice=str(t1.unitPrice) it works. Is that what I should do? Is this a mysql thing? I'm using 4.1.13. class TestOne(SQLObect): unitPrice = CurrencyCol(default=None) class TestOne(SQLObject): unitPrice = CurrencyCol(default=None) Here is an example: >>> TestOne.createTable() 1/Query : CREATE TABLE test_one ( id INT PRIMARY KEY AUTO_INCREMENT, unit_price DECIMAL(10, 2) ) >>> TestTwo.createTable() 1/Query : CREATE TABLE test_two ( id INT PRIMARY KEY AUTO_INCREMENT, unit_price DECIMAL(10, 2) ) >>> t1 = TestOne(unitPrice='25.00') 1/QueryIns: INSERT INTO test_one (unit_price) VALUES ('25.00') 1/QueryOne: SELECT unit_price FROM test_one WHERE id = 1 >>> t1 <TestOne 1L unitPrice=Decimal("25.00")> >>> t2 = TestTwo(unitPrice=t1.unitPrice) Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/local/lib/python2.4/site-packages/sqlobject/main.py", line 890, in __init__ self._create(id, **kw) File "/usr/local/lib/python2.4/site-packages/sqlobject/main.py", line 923, in _create self._SO_finishCreate(id) File "/usr/local/lib/python2.4/site-packages/sqlobject/main.py", line 947, in _SO_finishCreate id, names, values) File "/usr/local/lib/python2.4/site-packages/sqlobject/dbconnection.py", line 241, in queryInsertID return self._runWithConnection(self._queryInsertID, soInstance, id, names, values) File "/usr/local/lib/python2.4/site-packages/sqlobject/dbconnection.py", line 125, in _runWithConnection val = meth(conn, *args) File "/usr/local/lib/python2.4/site-packages/sqlobject/mysql/mysqlconnection.py", line 50, in _queryInsertID q = self._insertSQL(table, names, values) File "/usr/local/lib/python2.4/site-packages/sqlobject/dbconnection.py", line 233, in _insertSQL return ("INSERT INTO %s (%s) VALUES (%s)" % File "/usr/local/lib/python2.4/site-packages/sqlobject/dbconnection.py", line 475, in sqlrepr return sqlrepr(v, self.dbName) File "/usr/local/lib/python2.4/site-packages/sqlobject/converters.py", line 185, in sqlrepr raise ValueError, "Unknown SQL builtin type: %s for %s" % \ ValueError: Unknown SQL builtin type: <class 'decimal.Decimal'> for Decimal("25.00") Thanks for any and all insight, - Sam Nilsson |
From: Oleg B. <ph...@ma...> - 2005-08-21 12:22:54
|
On Sat, Aug 20, 2005 at 02:03:51PM -0700, Sam Nilsson wrote: > ValueError: Unknown SQL builtin type: <class 'decimal.Decimal'> for > Decimal("25.00") Seems like you are using Python 2.4, and SQLObject does not know how to convert Decimal objects to SQL format. Any idea what is a syntax for decimal/currency in different DBMS? Is it float? INSERT ... VALUES (..., 28.1...). or what? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Sam N. <li...@se...> - 2005-08-22 02:48:05
|
Oleg Broytmann wrote: > On Sat, Aug 20, 2005 at 02:03:51PM -0700, Sam Nilsson wrote: > >>ValueError: Unknown SQL builtin type: <class 'decimal.Decimal'> for >>Decimal("25.00") > > > Seems like you are using Python 2.4, and SQLObject does not know how to > convert Decimal objects to SQL format. Thanks for your reply Oleg. Your hypothesis sounds good. > Any idea what is a syntax for decimal/currency in different DBMS? Is it > float? INSERT ... VALUES (..., 28.1...). or what? > > Oleg. This from the postgresql manual: Numeric constants are accepted in these general forms: """ digits digits.[digits][e[+-]digits] [digits].digits[e[+-]digits] digitse[+-]digits where digits is one or more decimal digits (0 through 9). At least one digit must be before or after the decimal point, if one is used. At least one digit must follow the exponent marker (e), if one is present. There may not be any spaces or other characters embedded in the constant. Note that any leading plus or minus sign is not actually considered part of the constant; it is an operator applied to the constant. """ I'm not sure about mysql except that it seems that quoting the decimal as a string should work like this: '+5.407' No clue about SQLite. I don't know if SQLObject supports any other DBs. It would be nice to at least get this updated for mysql, but I don't really know where to look (in the SO source code) and I don't fully understand the problem. - Sam |
From: Oleg B. <ph...@ma...> - 2005-08-22 05:04:48
|
On Sun, Aug 21, 2005 at 07:48:46PM -0700, Sam Nilsson wrote: > digits.[digits][e[+-]digits] Thank you. I think I can write a converter for this format. That should be easy. > It would be nice to at least get this updated for mysql, but I don't > really know where to look (in the SO source code) and I don't fully > understand the problem. I know and I understand. There is a module converters.py where all converters (Python to SQL strings) are registered. We need to add a conditional (there is no Decimal in pre-2.4 pythons) converter there. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2005-08-22 06:33:10
|
On Sun, Aug 21, 2005 at 07:48:46PM -0700, Sam Nilsson wrote: > digits > digits.[digits][e[+-]digits] > [digits].digits[e[+-]digits] > digitse[+-]digits I committed a converter and a test at revision 935. Can you check and report if the code really fixes your problem? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Sam N. <li...@se...> - 2005-08-23 01:00:24
|
Oleg Broytmann wrote: > On Sun, Aug 21, 2005 at 07:48:46PM -0700, Sam Nilsson wrote: > >>digits >>digits.[digits][e[+-]digits] >>[digits].digits[e[+-]digits] >>digitse[+-]digits > > > I committed a converter and a test at revision 935. Can you check and > report if the code really fixes your problem? > > Oleg. I tried to test it but had problems. First I couldn't import sqlobject.* without formencode. After grabbing formencode from svn, I now get this error when trying to import my very simple Test sqlobject schema. Not sure what to do about that. - Sam >>> from Model.Test import * Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/local/www/live-education/App/Model/Test.py", line 1, in ? from sqlobject935 import * File "/usr/local/www/live-education/App/sqlobject935/__init__.py", line 16, in ? import firebird as _firebird File "/usr/local/www/live-education/App/sqlobject935/firebird/__init__.py", line 13, in ? registerConnection(['firebird', 'interbase'], builder, isSupported) File "/usr/local/lib/python2.4/site-packages/sqlobject/dbconnection.py", line 637, in registerConnection assert not self.schemeBuilders.has_key(uriScheme) \ AssertionError: A driver has already been registered for the URI scheme firebird Here is Model.Test: from sqlobject935 import * from Store import conn class TestOne(SQLObject): _connection = conn unitPrice = CurrencyCol(default=None) class TestTwo(SQLObject): _connection = conn unitPrice = CurrencyCol(default=None) Here is Store: from sqlobject import * from Config import Config config = Config() conn = "mysql://%s:%s@localhost/%s?debug=1" % (config.SqlUserName, config.SqlPassword, config.SqlDatabase) |
From: Oleg B. <ph...@ma...> - 2005-08-23 07:00:00
|
On Mon, Aug 22, 2005 at 06:00:57PM -0700, Sam Nilsson wrote: > I tried to test it but had problems. First I couldn't import sqlobject.* > without formencode. Yes, FormEncode is required now. SQLObject and FormEncode now share validators/converters. > After grabbing formencode from svn, I now get this > error when trying to import my very simple Test sqlobject schema. Not > sure what to do about that. > >>> from Model.Test import * > Traceback (most recent call last): > File "<stdin>", line 1, in ? > File "/usr/local/www/live-education/App/Model/Test.py", line 1, in ? > from sqlobject935 import * > File "/usr/local/www/live-education/App/sqlobject935/__init__.py", > line 16, in ? > import firebird as _firebird > File > "/usr/local/www/live-education/App/sqlobject935/firebird/__init__.py", > line 13, in ? > registerConnection(['firebird', 'interbase'], builder, isSupported) > File > "/usr/local/lib/python2.4/site-packages/sqlobject/dbconnection.py", line > 637, in registerConnection > assert not self.schemeBuilders.has_key(uriScheme) \ > AssertionError: A driver has already been registered for the URI scheme > firebird You have imported two different sqlobject modules, and they interfere. Use only one of them. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Sam N. <li...@se...> - 2005-08-23 08:36:12
|
Oleg Broytmann wrote: > You have imported two different sqlobject modules, and they interfere. > Use only one of them. That's exactly right. Thanks for the tip. Decimals seem to work perfectly now for me at revision 935. I'm using python 2.4 with mysql 4.1.13. Thanks for committing that excellent fix! >>> from decimal import Decimal >>> dn = Decimal('50.43') >>> dn Decimal("50.43") >>> t1 = TestOne(unitPrice=dn) 1/QueryIns: INSERT INTO test_one (unit_price) VALUES (50.43) 1/QueryOne: SELECT unit_price FROM test_one WHERE id = 1 >>> t1 <TestOne 1L unitPrice=Decimal("50.43")> >>> t1.unitPrice Decimal("50.43") >>> t2 = TestTwo(unitPrice=t1.unitPrice) 1/QueryIns: INSERT INTO test_two (unit_price) VALUES (50.43) 1/QueryOne: SELECT unit_price FROM test_two WHERE id = 2 >>> t2.unitPrice Decimal("50.43") >>> t2 <TestTwo 2L unitPrice=Decimal("50.43")> - Sam |
From: M. D. <md...@em...> - 2005-10-02 19:37:36
|
On Fri, Sep 30, 2005 at 01:06:46PM +0400, Oleg Broytmann wrote: > On Fri, Sep 30, 2005 at 08:30:44AM +0200, M. Dietrich wrote: > > everything would be much simpler if bind vars would be used... > > and more performant for most DBs. > > Patches will be gladly accepted! > shure, but i can only test that for postgres. also the code is far from simple so i am afraid to break something else. could someone help here? best regards, michael |
From: Oleg B. <ph...@ma...> - 2005-10-02 19:50:03
|
On Sat, Oct 01, 2005 at 10:46:21AM +0200, M. Dietrich wrote: > On Fri, Sep 30, 2005 at 01:06:46PM +0400, Oleg Broytmann wrote: > > On Fri, Sep 30, 2005 at 08:30:44AM +0200, M. Dietrich wrote: > > > everything would be much simpler if bind vars would be used... > > > and more performant for most DBs. > > > > Patches will be gladly accepted! > > > shure, but i can only test that for postgres. also the code is far > from simple so i am afraid to break something else. could someone help > here? A half a year ago I tried to do some work in this area, but the work is stalled. I can commit some code to a branch and I'd like to restart the work... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: M. D. <md...@em...> - 2005-10-19 09:09:13
|
On Sun, Oct 02, 2005 at 11:49:52PM +0400, Oleg Broytmann wrote: > On Sat, Oct 01, 2005 at 10:46:21AM +0200, M. Dietrich wrote: > > On Fri, Sep 30, 2005 at 01:06:46PM +0400, Oleg Broytmann wrote: > > > On Fri, Sep 30, 2005 at 08:30:44AM +0200, M. Dietrich wrote: > > > > everything would be much simpler if bind vars would be used... > > > > and more performant for most DBs. > > > > > > Patches will be gladly accepted! > > > > > shure, but i can only test that for postgres. also the code is far > > from simple so i am afraid to break something else. could someone > > help here? > > A half a year ago I tried to do some work in this area, but the > work is stalled. I can commit some code to a branch and I'd like > to restart the work... sorry for not responding - had lots of thinks to do at work... :( yes, this sounds great. i would really like to take a look on this and can try to help here. please tell me if and where you put it, thnx alot! michael |
From: Oleg B. <ph...@ma...> - 2005-10-19 09:40:44
|
On Wed, Oct 19, 2005 at 10:12:27AM +0200, M. Dietrich wrote: > please tell me if and where you put it, thnx alot! http://svn.colorstudy.com/home/phd/SQLObject/paramstyles/ The work is again stalled because I stumbled upon a major troubles in drivers. Low-level drives do a poor job converting different types to SQL. For example, PySQLite1 does not convert boolean values - it actually does query_string % parameters in the code, and SQLite barfs on 'True'/'False' constants. psycopg1 does not quote datetime and mxDateTime instances, so cursor.execute("INSERT VALUES (?,?,?)", 'Yes', datetime.now(), True) is executed as INSERT VALUES ('Yes', 2005-10-10 12:00:01, .t.) and Postgres of course aborts such a broken query. Date instances are even more funny: INSERT VALUES ('Yes', 2005-10-10, .t.) and Postgres report the error: "You are trying to insert an integer into a DATE column; use cast." Yes, 2005-10-10 is an integer, 1985. PySQLite2 works a bit better, and I am going to try psycopg2. If anyone has experience with psycopg2 and MySQLdb in this area - I'd like to hear advice. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Ian B. <ia...@co...> - 2005-10-22 22:54:58
|
Oleg Broytmann wrote: > On Wed, Oct 19, 2005 at 10:12:27AM +0200, M. Dietrich wrote: > >>please tell me if and where you put it, thnx alot! > > > http://svn.colorstudy.com/home/phd/SQLObject/paramstyles/ > > The work is again stalled because I stumbled upon a major troubles in > drivers. Low-level drives do a poor job converting different types to SQL. > For example, PySQLite1 does not convert boolean values - it actually does > > query_string % parameters > > in the code, and SQLite barfs on 'True'/'False' constants. Ah... now I remember why I didn't start out using parameters :-/ I almost feel like some conversion infrastructure needs to remain, except that it would convert Python objects into objects that make good database parameters. -- Ian Bicking | ia...@co... | http://blog.ianbicking.org |