Re: [cx-oracle-users] Types used for values returned from a query

[Anthony T. <an...@co...>]

Paul Moore wrote:
> I have an application which needs to be able to execute a (relatively)
> arbitrary query and dump the output to stdout, in such a way that
> another (non-Python) program can pick up the values and rebuild them
> exactly.
> 
> I don't need to support exotic types like LOBs, but I *do* need to
> handle basic numbers, dates, and strings.
> 
> To do this, I need to understand *exactly* what types are returned by
> cursor.fetchone().
> 
> Strings and dates are OK - strings are obviously trivial, and
> dates/timestamps come back as Python datetime values, so I can convert
> to something transportable like "microseconds since the epoch,
> converted to a string".

This is true in Python 2.4 and up. Python 2.3 and earlier do use an 
internal date representation which is less powerful than the Python 
datetime data type. I'm assuming here that you are using Python 2.4.

> But I seem to be having problems with numbers. At the moment, I'm just
> doing str() on the returned value, and getting  odd results. I suspect
> this is because I'm sometimes getting floats or longs and just doing
> str() on these is (a) giving me variable formats, and (b) losing
> precision in the case of floats.
> 
> Is there a way of getting a precise value back for a numeric field, or
> is the data lost? Can I do things with NumberVar types, and get the
> full precision data from that?

The way it currently works is as follows: if the Oracle data type has no 
decimal places, the value is converted to a long/integer value; 
otherwise, the value is converted to a float with all of its loss of 
precision. When faced with this problem myself (for a program which 
dumps the contents of arbitrary queries in a format suitable for later 
importing) I dumped all numbers as strings. Oracle is quite happy to 
accept the string as input to a numeric column. I'm not sure if that 
will work for you but it is an option. If you are interested in pursuing 
that option, you should take a look at the OPT_NumbersAsStrings option 
in cx_Oracle.

> Alternatively, is there any likelihood of cxOracle using Python 2.4's
> Decimal type in the near future?

Theoretically this wouldn't be all that difficult to accomplish but 
since there is no C implementation this would mean a significant 
performance penalty. Having used the Decimal type myself in pure Python 
I know that it is the most efficiently coded module, either. I suppose 
this type could be returned __only__ if the data is not an integer and 
that would mitigate the problem somewhat. Comments?

> Thanks,
> Paul.
> 
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com