Re: Decimal

On Thu, Apr 22, 2010 at 10:32 PM, Antonio Prado <antonio-m7WKv7+duuT/[email protected]> wrote:
> Em Ter, 2010-04-20 às 14:40 -0300, Antonio Prado escreveu:
>> Em Ter, 2010-04-13 às 23:24 +0100, Daniele Varrazzo escreveu:
>> > 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'>
>
> [...]
> Using the solution above error occurs on record content Null.
>
> How to fix this?

> cur.execute("SELECT Null::decimal(10,2)")  # <<<<---------REGISTER NULL
> n = cur.fetchone()[0]
> print n, type(n)
>
>>>> None <type 'NoneType'>
>>>> Exception TypeError: 'expected string or Unicode object, NoneType
> found'

You are right, I'm very sorry for reading the message so late.

I don't know what's going wrong with the FLOAT typecaster, it deserves
some investigation. You can use a regular Python function to take care
of the NULL case:

	DEC2FLOAT = psycopg2.extensions.new_type(
		psycopg2._psycopg.DECIMAL.values,
		'DEC2FLOAT',
		lambda value, cur: float(value) if value is not None else None)
	psycopg2.extensions.register_type(DEC2FLOAT, cur)

-- Daniele