From: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
---|---|
To: | "W(dot) Matthew Wilson" <matt(at)tplus1(dot)com> |
Cc: | psycopg(at)postgresql(dot)org |
Subject: | Re: How to use the postgresql money type? |
Date: | 2011-01-19 23:42:09 |
Message-ID: | AANLkTikwEScC8bjpmLPT2XkNy_WeWCrxnWeYiw3DQxU7@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
On Wed, Jan 19, 2011 at 8:06 PM, W. Matthew Wilson <matt(at)tplus1(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | A.M. | 2011-01-20 15:40:19 | Re: How to use the postgresql money type? |
Previous Message | W. Matthew Wilson | 2011-01-19 20:06:53 | How to use the postgresql money type? |