Re: Decimal

On Tue, Apr 13, 2010 at 8:10 PM, Antonio Prado <antonio-m7WKv7+duuT/[email protected]> wrote:
> Hello!
>
> Using psycopg2 to connect as PostgreSQL, when set
> cur = conn.cursor (cursor_factory = psycopg2.extras.DictCursor)
> is returning data in decimal format.
>
> In my database I have numeric and decimal types.
>
> How to always return Float or Integer, equal in psycopg1?

You can register FLOAT as the type caster for the decimal type. You
should execute this code just once in your app:

# create a typecaster from Postgres decimal to Python float
import psycopg2.extensions
DEC2FLOAT = psycopg2.extensions.new_type(
  psycopg2._psycopg.DECIMAL.values, # oids for the decimal type
  'DEC2FLOAT', # the new typecaster name
  psycopg2.extensions.FLOAT) # the typecaster creating floats

# register the typecaster globally
psycopg2.extensions.register_type(DEC2FLOAT)

# Now Postgres decimals will produce Python floats
cnn = psycopg2.connect(database="test")
cur = cnn.cursor()
cur.execute("SELECT '123.45'::decimal(10,2)")
n = cur.fetchone()[0]
print n, type(n)
123.45, <type 'float'>

You can find documentation for this feature here:
http://initd.org/psycopg/docs/advanced.html#type-casting-of-sql-types-into-python-objects

I think we can add this recipe to the faqs: it seems pretty useful.
Now that I think about that, I have a program that was wasting *so*
much time in decimal calculations that I started adding ::float in
every query...

Federico, I see DECIMAL is not imported in the extensions module. I'll
check if there are other missing types and provide a patch for them.

-- Daniele