Thread: How to use the postgresql money type?
PostgreSQL has a money type, but I don't know how to use it with psycopg2. Do I need to write my own code to convert to and from SQL? Matt -- W. Matthew Wilson matt@tplus1.com http://tplus1.com
On Wed, Jan 19, 2011 at 8:06 PM, W. Matthew Wilson <matt@tplus1.com> wrote:
> PostgreSQL has a money type, but I don't know how to use it with
> psycopg2. Do I need to write my own code to convert to and from SQL?
From the docs I read that the output format is system and locale
dependent, so it doesn't seem easy or doable at all to add support in
psycopg in a general way.
In a more specific way instead, if you know the database locale and
you know e.g. that the symbol is "$" and the separator is ",", you can
write a typecaster for your database. Here is how to write a
typecaster to convert from money to Python Decimal, and to register it
on a specific connection.
In [1]: import psycopg2
In [2]: cnn = psycopg2.connect("dbname=test user=postgres")
In [3]: from decimal import Decimal
In [4]: def cast_money(s, cur):
...: if s is None: return None
...: return Decimal(s.replace(",","").replace("$",""))
...:
In [5]: MONEY = psycopg2.extensions.new_type((790,), "MONEY", cast_money)
In [6]: psycopg2.extensions.register_type(MONEY, cnn)
In [7]: cur = cnn.cursor()
In [8]: cur.execute("select '1000'::money;")
In [9]: cur.fetchone()
Out[9]: (Decimal('1000.00'),)
I also see that PostgreSQL doesn't let you convert from decimal to
money: bad stuff
test=> select 1000.00::money;
ERROR: cannot cast type numeric to money
LINE 1: select 1000.00::money;
^
This means that you also need an adapter to represent a monetary
amount as a string literal (in quotes). Adapters can only be
registered globally, not per connection, so it is less optimal than in
the other direction: you either overwrite the Decimal adapter or you
can use a different Python class to represent monies (e.g. a Decimal
subclass). An example overwriting Decimal is:
In [10]: class MoneyAdapter:
....: def __init__(self, m):
....: self.m = m
....: def getquoted(self):
....: return
psycopg2.extensions.adapt(str(self.m)).getquoted()
....:
In [11]: psycopg2.extensions.register_adapter(Decimal, MoneyAdapter)
In [12]: cur.mogrify("select %s;", (Decimal(1000),))
Out[12]: "select '1000';"
-- Daniele
On Jan 19, 2011, at 6:42 PM, Daniele Varrazzo wrote:
> On Wed, Jan 19, 2011 at 8:06 PM, W. Matthew Wilson <matt@tplus1.com> wrote:
>> PostgreSQL has a money type, but I don't know how to use it with
>> psycopg2. Do I need to write my own code to convert to and from SQL?
>
>
> I also see that PostgreSQL doesn't let you convert from decimal to
> money: bad stuff
That's because non-integer numbers in postgresql are interpreted as floats, which is exactly what you do not want to
representmoney. It's the same in python:
>>> from decimal import Decimal
>>> Decimal(100.00)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/System/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/decimal.py", line 652, in __new__
"First convert the float to a string")
TypeError: Cannot convert float to Decimal. First convert the float to a string
So the example which accepts floats and converts the input to a string includes a critical bug, not a workaround.
Becausethe money datatype is locale-dependent, the type should effectively be treated as an opaque text value- the
valuein the database must be used as-is for display to the user (including the decimal delimiter and currency
indicator).Math on money must be done in the database- this is required to properly handle banker's rounding and other
moneygimmicks.
For the original poster, if these restrictions are in your way, then look at NUMERIC(precision,scale) for an effective
equivalentto python's Decimal class.
Cheers,
M
On 20/01/2011 16:40, A.M. wrote: > > On Jan 19, 2011, at 6:42 PM, Daniele Varrazzo wrote: > >> On Wed, Jan 19, 2011 at 8:06 PM, W. Matthew Wilson <matt@tplus1.com> wrote: >>> PostgreSQL has a money type, but I don't know how to use it with >>> psycopg2. Do I need to write my own code to convert to and from SQL? >> >> >> I also see that PostgreSQL doesn't let you convert from decimal to >> money: bad stuff > > That's because non-integer numbers in postgresql are interpreted as floats, > which is exactly what you do not want to represent money. It's the same in python: decimal is not float. > So the example which accepts floats and converts the input to a string includes a critical bug, > not a workaround. Because the money datatype is locale-dependent, the type should effectively > be treated as an opaque text value- the value in the database must be used as-is for display > to the user (including the decimal delimiter and currency indicator). Math on money must be > done in the database- this is required to properly handle banker's rounding and other money > gimmicks. But this makes sense. Do you mean that banker's rounding may depend on the locale? If that's the case and if PostgreSQL implements such gimmicks then the correct default for money would be to return it to Python as string and let the user decide how to proceed from there. federico -- Federico Di Gregorio federico.digregorio@dndg.it Studio Associato Di Nunzio e Di Gregorio http://dndg.it Degli altri, della gente senza domande, si puo' fare a meno. -- macchinavapore
On Jan 20, 2011, at 10:45 AM, Federico Di Gregorio wrote: > On 20/01/2011 16:40, A.M. wrote: >> > > But this makes sense. Do you mean that banker's rounding may depend on > the locale? If that's the case and if PostgreSQL implements such > gimmicks then the correct default for money would be to return it to > Python as string and let the user decide how to proceed from there. Banker's rounding doesn't occur at all by default with the money type since it doesn't represent values outside it's locale-dependentscale, but any real->money conversion must occur in the database to account for this possibility. > the type should effectively > be treated as an opaque text value is what I wrote above. Python can't really do anything with the value other than to display it. Cheers, M
On Thursday 20 January 2011 7:45:30 am Federico Di Gregorio wrote: > On 20/01/2011 16:40, A.M. wrote: > > But this makes sense. Do you mean that banker's rounding may depend on > the locale? If that's the case and if PostgreSQL implements such > gimmicks then the correct default for money would be to return it to > Python as string and let the user decide how to proceed from there. > > federico Info on the money type: http://www.postgresql.org/docs/9.0/interactive/datatype-money.html FYI this type at times has been marked as deprecated. There has been some recent work on it but it has issues especially, from the link above, the dump output and moving data to a different locale. -- Adrian Klaver adrian.klaver@gmail.com