Thread: [cx-oracle-users] Types used for values returned from a query
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Paul M. <p.f...@gm...> - 2005-02-09 18:00:19
|
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". 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? Alternatively, is there any likelihood of cxOracle using Python 2.4's Decimal type in the near future? Thanks, Paul. |
|
From: Anthony T. <an...@co...> - 2005-02-09 18:08:47
|
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 |
|
From: Paul M. <p.f...@gm...> - 2005-02-09 18:46:35
|
On Wed, 09 Feb 2005 11:08:36 -0700, Anthony Tuininga
<an...@co...> wrote:
> 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.
Correct - sorry, I didn't mention this explicitly.
> 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.
That's pretty much what I'd determined by experiment.
> 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.
That's pretty much the same requirement as I have and so the same
solution should apply. The only issue I have is that my loader is
written as a Java stored procedure inside the database, so my dump
format needs to be Java-readable.
> 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.
Quite possibly. I can just bind the variable as a String in Java, and
then load it into a Number column, I guess. Could there be any format
issues (exponential formats, commas in numbers, etc)? I only ask
because the environemnt makes it annoyingly hard to report errors, so
when things go wrong it's a real pain to debug :-(
> If you are interested in pursuing that option, you should take a look at the
> OPT_NumbersAsStrings option in cx_Oracle.
That sounds great. I'll look at that in the morning.
> > 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?
That may be an option, but frankly, this is pretty specialised use
where an exact representation is crucial. Maybe just having a way of
saying that a specific column in a query must be returned as a string,
and then doing string->Decimal in Python would be suitable. Something
like
c = cn.cursor()
c.execute("select...")
c.col_as_string(1)
row = c.fetchone()
decval = Decimal(row[1])
All I really care about is having some way of being sure that there's
no precision loss - using strings does it, the rest is just
convenience (only using strings where it's really needed).
Sorry - must dash, but I hope you get the idea...
Paul
|
|
From: Leith P. <lei...@gm...> - 2005-02-09 23:32:54
|
Why cant you use cast?
>>> import cx_Oracle as O
>>> db = O.connect('sid')
>>> s = db.cursor()
>>> s.execute('select CAST( 1234 as varchar2(255)) a_string from dual')
[<StringVar object at 0x402240b0>]
On Wed, 9 Feb 2005 18:46:32 +0000, Paul Moore <p.f...@gm...> wrote:
> On Wed, 09 Feb 2005 11:08:36 -0700, Anthony Tuininga
> <an...@co...> wrote:
> > 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.
>
> Correct - sorry, I didn't mention this explicitly.
>
> > 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.
>
> That's pretty much what I'd determined by experiment.
>
> > 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.
>
> That's pretty much the same requirement as I have and so the same
> solution should apply. The only issue I have is that my loader is
> written as a Java stored procedure inside the database, so my dump
> format needs to be Java-readable.
>
> > 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.
>
> Quite possibly. I can just bind the variable as a String in Java, and
> then load it into a Number column, I guess. Could there be any format
> issues (exponential formats, commas in numbers, etc)? I only ask
> because the environemnt makes it annoyingly hard to report errors, so
> when things go wrong it's a real pain to debug :-(
>
> > If you are interested in pursuing that option, you should take a look at the
> > OPT_NumbersAsStrings option in cx_Oracle.
>
> That sounds great. I'll look at that in the morning.
>
> > > 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?
>
> That may be an option, but frankly, this is pretty specialised use
> where an exact representation is crucial. Maybe just having a way of
> saying that a specific column in a query must be returned as a string,
> and then doing string->Decimal in Python would be suitable. Something
> like
>
> c = cn.cursor()
> c.execute("select...")
> c.col_as_string(1)
> row = c.fetchone()
> decval = Decimal(row[1])
>
> All I really care about is having some way of being sure that there's
> no precision loss - using strings does it, the rest is just
> convenience (only using strings where it's really needed).
>
> Sorry - must dash, but I hope you get the idea...
>
> 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
>
|
|
From: Paul M. <p.f...@gm...> - 2005-02-10 09:13:17
|
On Thu, 10 Feb 2005 09:32:47 +1000, Leith Parkin <lei...@gm...> wrote: > Why cant you use cast? Basically because I'm writing something to handle user-supplied SELECT statements, so I don't have control over the query (beyond saying that I don't certain types - I don't think I'll get away with refusing to support numbers, though :-)). Regards, Paul. |
|
From: Anthony T. <an...@co...> - 2005-02-10 14:50:25
|
Paul Moore wrote:
>>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.
>
>
> Quite possibly. I can just bind the variable as a String in Java, and
> then load it into a Number column, I guess. Could there be any format
> issues (exponential formats, commas in numbers, etc)? I only ask
> because the environemnt makes it annoyingly hard to report errors, so
> when things go wrong it's a real pain to debug :-(
Not certain. Since I've only dumped it for future import into an Oracle
database that hasn't been an issue for me. You'll have to try -- you can
find out what happens by simply issuing "to_char(number)" on any number
you would care to and looking at the resulting string.
>>>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?
>
>
> That may be an option, but frankly, this is pretty specialised use
> where an exact representation is crucial. Maybe just having a way of
> saying that a specific column in a query must be returned as a string,
> and then doing string->Decimal in Python would be suitable. Something
> like
>
> c = cn.cursor()
> c.execute("select...")
> c.col_as_string(1)
> row = c.fetchone()
> decval = Decimal(row[1])
>
> All I really care about is having some way of being sure that there's
> no precision loss - using strings does it, the rest is just
> convenience (only using strings where it's really needed).
>
> Sorry - must dash, but I hope you get the idea...
I do and I've considered something similar. Perhaps a
cursor.define(position, data_type) which would allow you to override the
default retrieve definition. In your example the call would be
"c.define(1, cx_Oracle.STRING)". I suspect it would have to take place
before the execute() call, though. Comments, anyone?
> 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
|
|
From: Paul M. <p.f...@gm...> - 2005-02-10 19:34:59
|
On Thu, 10 Feb 2005 07:50:11 -0700, Anthony Tuininga
<an...@co...> wrote:
> > Quite possibly. I can just bind the variable as a String in Java, and
> > then load it into a Number column, I guess. Could there be any format
> > issues (exponential formats, commas in numbers, etc)? I only ask
> > because the environemnt makes it annoyingly hard to report errors, so
> > when things go wrong it's a real pain to debug :-(
>
> Not certain. Since I've only dumped it for future import into an Oracle
> database that hasn't been an issue for me. You'll have to try -- you can
> find out what happens by simply issuing "to_char(number)" on any number
> you would care to and looking at the resulting string.
I tried it out, and it works perfectly. Thanks for the pointer. But
(see below) the result isn't always the same as to_char() gives, in
the presence of NLS parameters...
> > That may be an option, but frankly, this is pretty specialised use
> > where an exact representation is crucial. Maybe just having a way of
> > saying that a specific column in a query must be returned as a string,
> > and then doing string->Decimal in Python would be suitable. Something
> > like
[...]
> > All I really care about is having some way of being sure that there's
> > no precision loss - using strings does it, the rest is just
> > convenience (only using strings where it's really needed).
> >
> > Sorry - must dash, but I hope you get the idea...
>
> I do and I've considered something similar. Perhaps a
> cursor.define(position, data_type) which would allow you to override the
> default retrieve definition. In your example the call would be
> "c.define(1, cx_Oracle.STRING)". I suspect it would have to take place
> before the execute() call, though. Comments, anyone?
That looks fine. In practice, though, for my requirement,
OPT_NumbersAsStrings is perfectly adequate. As things stand, I don't
have a use for anything finer grained, and the only example I can
think of is using a string to allow me to construct a Decimal without
loss of precision. It would probably be worth having some additional
use cases before expending much effort on this :-)
One question that does come up is that of format. My application was
basically like yours - dump the numbers to strings so that you can
later load the strings back into the database. As this is
Oracle->Oracle, no format issue arises. However, if you want to use
the results elsewhere, you'd need to define the format. A check of the
Oracle OCI documentation wasn't clear to me so I did some
experimenting. It *looks* like the format is
<digits>.<digits>E<sign><digits>
with the .<digits> and the E<sign><digits> parts optional. But it's
not affected by NLS_NUMERIC_CHARACTERS, which is probably good...
OK, I'm overdoing this now. But the point remains that ultimately use
of OPT_NumbersAsStrings and any column-level equivalent is going to
hit a need to be sure what the valid formats are. Oracle's
documentation isn't very clear on this, and expecting Python
programmers to have a good understanding of OCI is probably expecting
a bit too much.
Hmm, actually it may not even be roundtrip-safe. Try this:
>>> cn = cx_Oracle.connect("scott/tiger")
>>> cx_Oracle.OPT_NumbersAsStrings = 1
>>> c = cn.cursor()
>>> c.execute("select 123456/100 from dual")
[<NumberVar object at 0x00973ED0>]
>>> n, = c.fetchone()
>>> n
'1234.56'
>>> c.execute("create table t (n number)")
>>> c.execute("insert into t values (:a)", a=n)
>>> c.execute("alter session set NLS_NUMERIC_CHARACTERS=',.'")
>>> c.execute("select 123456/100 from dual")
[<NumberVar object at 0x00973ED0>]
>>> n, = c.fetchone()
>>> n
'1234.56'
>>> c.execute("insert into t values (:a)", a=n)
Traceback (most recent call last):
File "<stdin>", line 1, in ?
cx_Oracle.DatabaseError: ORA-01722: invalid number
>>>
Ick. This is getting complicated...
Well, it works for me. So this is not something I'm going to lose sleep over :-)
Paul.
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X