cx-oracle-users — Primary discussion list for users (and developers) of cx_Oracle

Re: [cx-oracle-users] Help with fetchraw

[Anthony T. <ant...@gm...>]

On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
>=20
> --- Anthony Tuininga <ant...@gm...> wrote:
>=20
> > On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
> > >
> > > --- Anthony Tuininga <ant...@gm...> wrote:
> > > > 2) I'm not sure exactly what you are intending with this so I can't
> > > > see why the simple
> > > >
> > > > for row in cursor:
> > > >     process_the_row()
> > > >
> > > > isn't going to work or isn't desirable. Perhaps you could give more
> > > > detail? Thanks.
> > >
> > > The "problem" with:
> > >
> > > for row in cursor:
> > >     process_the_row()
> > >
> > > is that process_the_row() is essentially:
> > >
> > > for col in row:
> > >     rowOut =3D rowOut.append(format_col(col))
> > > rowOutString =3D "".join(rowOut)
> > >
> > > which creates quite an overhead when the tables have lots of columns =
(>40).
> >
> > You should be able to rewrite "process_the_row()" as the following if
> > all you are intending to do is replace None with ""
> >
> > for row in cursor:
> >     rowOutString =3D "".join([v or "" for v in row])
> >
> > if all you are fetching is strings. There is a flag for turning
> > numbers into strings in all cases as well. If you need something more
> > complicated than the above (multiple data types) then I'll need more
> > information. :-)
>=20
> There are multiple datatypes typically varchar,number,date and sometimes =
raw and long.

Ok. So what exactly does the process_row() method do? If you give the
full body I might be able to suggest something that would work better.
Or if you'd rather play with it on your own, that's fine too. :-)

Re: [cx-oracle-users] Help with fetchraw

[Chris D. <cdu...@ya...>]

--- Anthony Tuininga <ant...@gm...> wrote:

> On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
> > 
> > --- Anthony Tuininga <ant...@gm...> wrote:
> > > 2) I'm not sure exactly what you are intending with this so I can't
> > > see why the simple
> > >
> > > for row in cursor:
> > >     process_the_row()
> > >
> > > isn't going to work or isn't desirable. Perhaps you could give more
> > > detail? Thanks.
> > 
> > The "problem" with:
> > 
> > for row in cursor:
> >     process_the_row()
> > 
> > is that process_the_row() is essentially:
> > 
> > for col in row:
> >     rowOut = rowOut.append(format_col(col))
> > rowOutString = "".join(rowOut)
> > 
> > which creates quite an overhead when the tables have lots of columns (>40).
> 
> You should be able to rewrite "process_the_row()" as the following if
> all you are intending to do is replace None with ""
> 
> for row in cursor:
>     rowOutString = "".join([v or "" for v in row])
> 
> if all you are fetching is strings. There is a flag for turning
> numbers into strings in all cases as well. If you need something more
> complicated than the above (multiple data types) then I'll need more
> information. :-)

There are multiple datatypes typically varchar,number,date and sometimes raw and long.
 
> > So I'm looking at anything that could help speed things up (it's quite possible that in this
> case
> > fetchraw() won't make much of an impact compared to the process_the_row() code) This is why
> I'm
> > looking the None as Null returning "" stuff so as to reduce the complexity of "format_col()"
> and
> > hence CPU cost.
> > 
> > BTW I've actually put the process_the_row and format_col() code in-line as per
> > http://www.python.org/doc/faq/programming.html and associated performance guidelines to avoid
> the
> > function call overhead and this reduced the Python CPU usage by almost 20% on my standard perf
> > test.
> > 
> > Thanks again for your much appreciated help,
> 
> You're welcome. I hope to get the example of fetchraw() to you later
> today but I expect to be interrupted shortly with a task that might
> take all day so don't be alarmed if you don't hear from me right away.
> I haven't forgotten about it. :-)

No worries, I can wait.

Thanks again,

Chris

> 
> > Chris
> > > On 5/12/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > > I'm looking to use cursor.fetchraw to help improve performance in an app I'm writing. I've
> > > looked
> > > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out.
> > > > If possible I'd like a simple full example to show me the way. The context is:
> > > >
> > > > 1) Retrieve rows from source table and insert into a target table which has exactly the
> same
> > > > structure.
> > > >
> > > > 2) Retrieve rows from source table, add some column formatting and write out to a file.
> > > >
> > > > Thanks very much,
> > > >
> > > > Chris Dunscombe
> > > >
> > > > PS Thanks for all the input re my previous posting on Nulls and None. I've still got some
> > > thinking
> > > > and work to do on which is best for performance and how best to cope with different
> datatypes,
> > > > particularly Longs (what a pain they are!!)
> > > >
> > > > __________________________________
> > > > Yahoo! Mail Mobile
> > > > Take Yahoo! Mail with you! Check email on your mobile phone.
> > > > http://mobile.yahoo.com/learn/mail
> > > >
> > > > -------------------------------------------------------
> > > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > > Want to be the first software developer in space?
> > > > Enter now for the Oracle Space Sweepstakes!
> > > > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> > > > _______________________________________________
> > > > cx-oracle-users mailing list
> > > > cx-...@li...
> > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > >
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_idt12&alloc_id344&op=click
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> > 
> > __________________________________
> > Yahoo! Mail Mobile
> > Take Yahoo! Mail with you! Check email on your mobile phone.
> > http://mobile.yahoo.com/learn/mail
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id344&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 



		
__________________________________ 
Do you Yahoo!? 
Yahoo! Mail - Helps protect you from nasty viruses. 
http://promotions.yahoo.com/new_mail

[cx-oracle-users] Session pool

[Richard M. <ri...@we...>]

Hi all,

Does anyone have an example of how to use the cx_Oracle session
pool?

Cheers

Rich.
-- 
Richard Moore, Principal Software Engineer,
Westpoint Ltd,
Albion Wharf, 19 Albion Street, Manchester, M1 5LN, England
Tel: +44 161 237 1028
Fax: +44 161 237 1031

Re: [cx-oracle-users] Help with fetchraw

[Anthony T. <ant...@gm...>]

On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
>=20
> --- Anthony Tuininga <ant...@gm...> wrote:
> > 2) I'm not sure exactly what you are intending with this so I can't
> > see why the simple
> >
> > for row in cursor:
> >     process_the_row()
> >
> > isn't going to work or isn't desirable. Perhaps you could give more
> > detail? Thanks.
>=20
> The "problem" with:
>=20
> for row in cursor:
>     process_the_row()
>=20
> is that process_the_row() is essentially:
>=20
> for col in row:
>     rowOut =3D rowOut.append(format_col(col))
> rowOutString =3D "".join(rowOut)
>=20
> which creates quite an overhead when the tables have lots of columns (>40=
).

You should be able to rewrite "process_the_row()" as the following if
all you are intending to do is replace None with ""

for row in cursor:
    rowOutString =3D "".join([v or "" for v in row])

if all you are fetching is strings. There is a flag for turning
numbers into strings in all cases as well. If you need something more
complicated than the above (multiple data types) then I'll need more
information. :-)

> So I'm looking at anything that could help speed things up (it's quite po=
ssible that in this case
> fetchraw() won't make much of an impact compared to the process_the_row()=
 code) This is why I'm
> looking the None as Null returning "" stuff so as to reduce the complexit=
y of "format_col()" and
> hence CPU cost.
>=20
> BTW I've actually put the process_the_row and format_col() code in-line a=
s per
> http://www.python.org/doc/faq/programming.html and associated performance=
 guidelines to avoid the
> function call overhead and this reduced the Python CPU usage by almost 20=
% on my standard perf
> test.
>=20
> Thanks again for your much appreciated help,

You're welcome. I hope to get the example of fetchraw() to you later
today but I expect to be interrupted shortly with a task that might
take all day so don't be alarmed if you don't hear from me right away.
I haven't forgotten about it. :-)

> Chris
> > On 5/12/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > I'm looking to use cursor.fetchraw to help improve performance in an =
app I'm writing. I've
> > looked
> > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out=
.
> > > If possible I'd like a simple full example to show me the way. The co=
ntext is:
> > >
> > > 1) Retrieve rows from source table and insert into a target table whi=
ch has exactly the same
> > > structure.
> > >
> > > 2) Retrieve rows from source table, add some column formatting and wr=
ite out to a file.
> > >
> > > Thanks very much,
> > >
> > > Chris Dunscombe
> > >
> > > PS Thanks for all the input re my previous posting on Nulls and None.=
 I've still got some
> > thinking
> > > and work to do on which is best for performance and how best to cope =
with different datatypes,
> > > particularly Longs (what a pain they are!!)
> > >
> > > __________________________________
> > > Yahoo! Mail Mobile
> > > Take Yahoo! Mail with you! Check email on your mobile phone.
> > > http://mobile.yahoo.com/learn/mail
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_idt12&alloc_id=16344&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>=20
> __________________________________
> Yahoo! Mail Mobile
> Take Yahoo! Mail with you! Check email on your mobile phone.
> http://mobile.yahoo.com/learn/mail
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7412&alloc_id=3D16344&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Help with fetchraw

[Chris D. <cdu...@ya...>]

--- Anthony Tuininga <ant...@gm...> wrote:
> 1) You should be able to do something like this without the need to
> descend into fetchraw().
> 
> import cx_Oracle
> 
> fromConnection = cx_Oracle.Connection("user/pw@tns")
> toConnection = cx_Oracle.Connection("user/pw@tns")
> fromCursor = fromConnection.cursor()
> fromCursor.arraysize = 250 # set as desired
> toCursor = toConnection.cursor()
> toCursor.arraysize = 250 # set as desired, same as fromCursor
> 
> fromCursor.execute("some query")
> toCursor.setinputsizes() # set things up if nulls are allowed
> while True:
>     data = fromCursor.fetchmany()
>     if not data:
>         break
>     toCursor.executemany("some insert statement", data)
>     toConnection.commit() # if desired
> 
> That should do it and should perform quite well. The fetchraw(),
> bindvar.copy() and executemanyprepared() trio were created solely to
> avoid the overhead of creating Python objects for the data. Its more
> complicated than the above code but I can give that as an example as
> well if you really want it.
> 

I've done essentially the same as you've got above and yes it works fine with pretty good
performance. I'm just trying to improve it further as there are cases when the app will be running
a number of parallel streams each selecting and inserting millions of rows in a fixed window so
saving 5% - 15% of the Python CPU time within this loop is definately worth doing. I'd like a
fetchraw example so I can benchmark it against my current approach. I'll then post the results so
everyone can see the general sort of performance differences between the two.


> 2) I'm not sure exactly what you are intending with this so I can't
> see why the simple
> 
> for row in cursor:
>     process_the_row()
> 
> isn't going to work or isn't desirable. Perhaps you could give more
> detail? Thanks.

The "problem" with:

for row in cursor:
    process_the_row()

is that process_the_row() is essentially:

for col in row:
    rowOut = rowOut.append(format_col(col))
rowOutString = "".join(rowOut)

which creates quite an overhead when the tables have lots of columns (>40).

So I'm looking at anything that could help speed things up (it's quite possible that in this case
fetchraw() won't make much of an impact compared to the process_the_row() code) This is why I'm
looking the None as Null returning "" stuff so as to reduce the complexity of "format_col()" and
hence CPU cost.

BTW I've actually put the process_the_row and format_col() code in-line as per
http://www.python.org/doc/faq/programming.html and associated performance guidelines to avoid the
function call overhead and this reduced the Python CPU usage by almost 20% on my standard perf
test.

Thanks again for your much appreciated help,

Chris
> On 5/12/05, Chris Dunscombe <cdu...@ya...> wrote:
> > I'm looking to use cursor.fetchraw to help improve performance in an app I'm writing. I've
> looked
> > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out.
> > If possible I'd like a simple full example to show me the way. The context is:
> > 
> > 1) Retrieve rows from source table and insert into a target table which has exactly the same
> > structure.
> > 
> > 2) Retrieve rows from source table, add some column formatting and write out to a file.
> > 
> > Thanks very much,
> > 
> > Chris Dunscombe
> > 
> > PS Thanks for all the input re my previous posting on Nulls and None. I've still got some
> thinking
> > and work to do on which is best for performance and how best to cope with different datatypes,
> > particularly Longs (what a pain they are!!)
> > 
> > __________________________________
> > Yahoo! Mail Mobile
> > Take Yahoo! Mail with you! Check email on your mobile phone.
> > http://mobile.yahoo.com/learn/mail
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id344&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 



		
__________________________________ 
Yahoo! Mail Mobile 
Take Yahoo! Mail with you! Check email on your mobile phone. 
http://mobile.yahoo.com/learn/mail 

Re: [cx-oracle-users] Help with fetchraw

[Chris D. <cdu...@ya...>]

See in-line
--- Anthony Tuininga <ant...@gm...> wrote:

> On 5/15/05, Leith Parkin <lei...@gm...> wrote:
> > Hi Chris,
> > 
> > This is a quick example i knocked togethor ages ago to return a dict
> > result set instead of tuples. In retrospect fetchall() would probably
> > be better writtern as a generator under the newer python stuff.
> 
> Definitely. :-) Especially if you wanted to use it for large result sets.
> 
> > eg r = db.fetchone()
> > r['FIELDNAME']
> > 
> > Prehaps Anthony could give some feedback as to the "correctness" of this.
> 
> Looks "correct" to me. It works, doesn't it?? :-) As suggested, if you
> wanted to override the __iter__() method (or the fetchall() as shown
> below) to replace None with "" for null strings, that would be quite
> doable. If you need an example of that, I could probably whip
> something up. As you can see by this example, subclassing to extend or
> override functionality is actually quite simple.

Anthony,

If you could supply a simple example that would be much appreciated.

Thanks,

Chris

> 
> > --- SNIP ---
> > from cx_Oracle import *
> > 
> > # DictCursor for Oracle, returns dicts instead of tuples
> > class DictConnect(Connection):
> >         def cursor(self):
> >                 return DictCursor(self)
> > 
> > class DictCursor(Cursor):
> >     def fetchall(self):
> >         r = Cursor.fetchall(self)
> >         result = []
> >         for l in r:
> >             result.append( dict([(a[0],b) for a,b in
> > zip(self.description, l)]) )
> > 
> >         return result
> > 
> >     def fetchone(self):
> >         r = Cursor.fetchone(self)
> >         if r:
> >                 return dict([(a[0],b) for a,b in zip(self.description, r)])
> >         else:
> >                 return None
> > 
> > # factory class
> > def connect(**kw):
> >         return DictConnect(**kw)
> > 
> > ---- SNIP ----
> > 
> > On 5/15/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > Leith,
> > >
> > > Thanks, I like the sub-classing idea but my OO Python isn't that good so if you could give
> me a
> > > code example that would be much appreciated.
> > >
> > > Thanks very much,
> > >
> > > Chris
> > > --- Leith Parkin <lei...@gm...> wrote:
> > >
> > > > Hi Chris,
> > > >
> > > > No worries. We have a very similair setup at work, transferring
> > > > summarised data from oracle into MySQL.
> > > >
> > > > Depending on the database you are transferring too, couldnt you set a
> > > > trigger, inserting NULL (eg, a python None from Oracle) into the table
> > > > generates an empty string?
> > > >
> > > > Failing that, subclassing cx_Oracles cursor object to return a the
> > > > empty string when the column type is textual and the value is null
> > > > might be another option.
> > > >
> > > > Good luck,
> > > >
> > > > Leith
> > > >



		
__________________________________ 
Do you Yahoo!? 
Make Yahoo! your home page 
http://www.yahoo.com/r/hs

Re: [cx-oracle-users] Help with fetchraw

[Chris D. <cdu...@ya...>]

Leith,

Thanks very much,

Chris
--- Leith Parkin <lei...@gm...> wrote:

> Hi Chris,
> 
> This is a quick example i knocked togethor ages ago to return a dict
> result set instead of tuples. In retrospect fetchall() would probably
> be better writtern as a generator under the newer python stuff.
> 
> eg r = db.fetchone()
> r['FIELDNAME']
> 
> Prehaps Anthony could give some feedback as to the "correctness" of this.
> 
> --- SNIP ---
> from cx_Oracle import *
> 
> # DictCursor for Oracle, returns dicts instead of tuples
> class DictConnect(Connection):
>         def cursor(self):
>                 return DictCursor(self)
> 
> class DictCursor(Cursor):
>     def fetchall(self):
>         r = Cursor.fetchall(self)
>         result = []
>         for l in r:
>             result.append( dict([(a[0],b) for a,b in
> zip(self.description, l)]) )
> 
>         return result
> 
>     def fetchone(self):
>         r = Cursor.fetchone(self)
>         if r:
>                 return dict([(a[0],b) for a,b in zip(self.description, r)])
>         else:
>                 return None
> 
> # factory class
> def connect(**kw):
>         return DictConnect(**kw)
> 
> ---- SNIP ----
> 
> On 5/15/05, Chris Dunscombe <cdu...@ya...> wrote:
> > Leith,
> > 
> > Thanks, I like the sub-classing idea but my OO Python isn't that good so if you could give me
> a
> > code example that would be much appreciated.
> > 
> > Thanks very much,
> > 
> > Chris
> > --- Leith Parkin <lei...@gm...> wrote:
> > 
> > > Hi Chris,
> > >
> > > No worries. We have a very similair setup at work, transferring
> > > summarised data from oracle into MySQL.
> > >
> > > Depending on the database you are transferring too, couldnt you set a
> > > trigger, inserting NULL (eg, a python None from Oracle) into the table
> > > generates an empty string?
> > >
> > > Failing that, subclassing cx_Oracles cursor object to return a the
> > > empty string when the column type is textual and the value is null
> > > might be another option.
> > >
> > > Good luck,
> > >
> > > Leith


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [cx-oracle-users] Help with fetchraw

[Anthony T. <ant...@gm...>]

On 5/15/05, Leith Parkin <lei...@gm...> wrote:
> Hi Chris,
>=20
> This is a quick example i knocked togethor ages ago to return a dict
> result set instead of tuples. In retrospect fetchall() would probably
> be better writtern as a generator under the newer python stuff.

Definitely. :-) Especially if you wanted to use it for large result sets.

> eg r =3D db.fetchone()
> r['FIELDNAME']
>=20
> Prehaps Anthony could give some feedback as to the "correctness" of this.

Looks "correct" to me. It works, doesn't it?? :-) As suggested, if you
wanted to override the __iter__() method (or the fetchall() as shown
below) to replace None with "" for null strings, that would be quite
doable. If you need an example of that, I could probably whip
something up. As you can see by this example, subclassing to extend or
override functionality is actually quite simple.

> --- SNIP ---
> from cx_Oracle import *
>=20
> # DictCursor for Oracle, returns dicts instead of tuples
> class DictConnect(Connection):
>         def cursor(self):
>                 return DictCursor(self)
>=20
> class DictCursor(Cursor):
>     def fetchall(self):
>         r =3D Cursor.fetchall(self)
>         result =3D []
>         for l in r:
>             result.append( dict([(a[0],b) for a,b in
> zip(self.description, l)]) )
>=20
>         return result
>=20
>     def fetchone(self):
>         r =3D Cursor.fetchone(self)
>         if r:
>                 return dict([(a[0],b) for a,b in zip(self.description, r)=
])
>         else:
>                 return None
>=20
> # factory class
> def connect(**kw):
>         return DictConnect(**kw)
>=20
> ---- SNIP ----
>=20
> On 5/15/05, Chris Dunscombe <cdu...@ya...> wrote:
> > Leith,
> >
> > Thanks, I like the sub-classing idea but my OO Python isn't that good s=
o if you could give me a
> > code example that would be much appreciated.
> >
> > Thanks very much,
> >
> > Chris
> > --- Leith Parkin <lei...@gm...> wrote:
> >
> > > Hi Chris,
> > >
> > > No worries. We have a very similair setup at work, transferring
> > > summarised data from oracle into MySQL.
> > >
> > > Depending on the database you are transferring too, couldnt you set a
> > > trigger, inserting NULL (eg, a python None from Oracle) into the tabl=
e
> > > generates an empty string?
> > >
> > > Failing that, subclassing cx_Oracles cursor object to return a the
> > > empty string when the column type is textual and the value is null
> > > might be another option.
> > >
> > > Good luck,
> > >
> > > Leith
> > >
> > >
> > > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > > Leith,
> > > >
> > > > I didn't mention that for 1) the target table is in a separate data=
base and using database
> > > links
> > > > isn't an option (although I wish it was) due to reasons that are to=
o lengthy to go into here.
> > > > Hence  PL/SQL isn't really viable. So I'd still like to look at cur=
sor.fetchraw()
> > > >
> > > > Now for 2) you maybe right that PL/SQL could be used but I'll still=
 have to write out to file
> > > > using Python as util_file can only write to files that the database=
 server can "see" which
> > > isn't
> > > > always the case for my app.
> > > >
> > > > Thanks for the idea,
> > > >
> > > > Chris
> > > >
> > > >
> > > > --- Leith Parkin <lei...@gm...> wrote:
> > > > > This sounds like something better done in PL/SQL?
> > > > >
> > > > > Regards,
> > > > >
> > > > > Leith
> > > > > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > > > > I'm looking to use cursor.fetchraw to help improve performance =
in an app I'm writing. I've
> > > > > looked
> > > > > > at CopyData from cx_OracleTools but I'm afraid I couldn't work =
it out.
> > > > > > If possible I'd like a simple full example to show me the way. =
The context is:
> > > > > >
> > > > > > 1) Retrieve rows from source table and insert into a target tab=
le which has exactly the
> > > same
> > > > > > structure.
> > > > > >
> > > > > > 2) Retrieve rows from source table, add some column formatting =
and write out to a file.
> > > > > >
> > > > > > Thanks very much,
> > > > > >
> > > > > > Chris Dunscombe
> > > > > >
> > > > > > PS Thanks for all the input re my previous posting on Nulls and=
 None. I've still got some
> > > > > thinking
> > > > > > and work to do on which is best for performance and how best to=
 cope with different
> > > datatypes,
> > > > > > particularly Longs (what a pain they are!!)
> > > > > >
> > > > > > __________________________________
> > > > > > Yahoo! Mail Mobile
> > > > > > Take Yahoo! Mail with you! Check email on your mobile phone.
> > > > > > http://mobile.yahoo.com/learn/mail
> > > > > >
> > > > > > -------------------------------------------------------
> > > > > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > > > > Want to be the first software developer in space?
> > > > > > Enter now for the Oracle Space Sweepstakes!
> > > > > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > > > > _______________________________________________
> > > > > > cx-oracle-users mailing list
> > > > > > cx-...@li...
> > > > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > > > >
> > > > >
> > > > >
> > > > > -------------------------------------------------------
> > > > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > > > Want to be the first software developer in space?
> > > > > Enter now for the Oracle Space Sweepstakes!
> > > > > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > > > > _______________________________________________
> > > > > cx-oracle-users mailing list
> > > > > cx-...@li...
> > > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > > >
> > > >
> > > > __________________________________________________
> > > > Do You Yahoo!?
> > > > Tired of spam?  Yahoo! Mail has the best spam protection around
> > > > http://mail.yahoo.com
> > > >
> > > >
> > > > -------------------------------------------------------
> > > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > > Want to be the first software developer in space?
> > > > Enter now for the Oracle Space Sweepstakes!
> > > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > > _______________________________________________
> > > > cx-oracle-users mailing list
> > > > cx-...@li...
> > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > >
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_ids93&alloc_id=16281&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Help with fetchraw

[Anthony T. <ant...@gm...>]

1) You should be able to do something like this without the need to
descend into fetchraw().

import cx_Oracle

fromConnection =3D cx_Oracle.Connection("user/pw@tns")
toConnection =3D cx_Oracle.Connection("user/pw@tns")
fromCursor =3D fromConnection.cursor()
fromCursor.arraysize =3D 250 # set as desired
toCursor =3D toConnection.cursor()
toCursor.arraysize =3D 250 # set as desired, same as fromCursor

fromCursor.execute("some query")
toCursor.setinputsizes() # set things up if nulls are allowed
while True:
    data =3D fromCursor.fetchmany()
    if not data:
        break
    toCursor.executemany("some insert statement", data)
    toConnection.commit() # if desired

That should do it and should perform quite well. The fetchraw(),
bindvar.copy() and executemanyprepared() trio were created solely to
avoid the overhead of creating Python objects for the data. Its more
complicated than the above code but I can give that as an example as
well if you really want it.

2) I'm not sure exactly what you are intending with this so I can't
see why the simple

for row in cursor:
    process_the_row()

isn't going to work or isn't desirable. Perhaps you could give more
detail? Thanks.

On 5/12/05, Chris Dunscombe <cdu...@ya...> wrote:
> I'm looking to use cursor.fetchraw to help improve performance in an app =
I'm writing. I've looked
> at CopyData from cx_OracleTools but I'm afraid I couldn't work it out.
> If possible I'd like a simple full example to show me the way. The contex=
t is:
>=20
> 1) Retrieve rows from source table and insert into a target table which h=
as exactly the same
> structure.
>=20
> 2) Retrieve rows from source table, add some column formatting and write =
out to a file.
>=20
> Thanks very much,
>=20
> Chris Dunscombe
>=20
> PS Thanks for all the input re my previous posting on Nulls and None. I'v=
e still got some thinking
> and work to do on which is best for performance and how best to cope with=
 different datatypes,
> particularly Longs (what a pain they are!!)
>=20
> __________________________________
> Yahoo! Mail Mobile
> Take Yahoo! Mail with you! Check email on your mobile phone.
> http://mobile.yahoo.com/learn/mail
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Help with fetchraw

[Leith P. <lei...@gm...>]

Hi Chris,

This is a quick example i knocked togethor ages ago to return a dict
result set instead of tuples. In retrospect fetchall() would probably
be better writtern as a generator under the newer python stuff.

eg r =3D db.fetchone()
r['FIELDNAME']

Prehaps Anthony could give some feedback as to the "correctness" of this.

--- SNIP ---
from cx_Oracle import *

# DictCursor for Oracle, returns dicts instead of tuples
class DictConnect(Connection):
        def cursor(self):
                return DictCursor(self)

class DictCursor(Cursor):
    def fetchall(self):
        r =3D Cursor.fetchall(self)
        result =3D []
        for l in r:
            result.append( dict([(a[0],b) for a,b in
zip(self.description, l)]) )

        return result

    def fetchone(self):
        r =3D Cursor.fetchone(self)
        if r:
                return dict([(a[0],b) for a,b in zip(self.description, r)])
        else:
                return None

# factory class
def connect(**kw):
        return DictConnect(**kw)

---- SNIP ----

On 5/15/05, Chris Dunscombe <cdu...@ya...> wrote:
> Leith,
>=20
> Thanks, I like the sub-classing idea but my OO Python isn't that good so =
if you could give me a
> code example that would be much appreciated.
>=20
> Thanks very much,
>=20
> Chris
> --- Leith Parkin <lei...@gm...> wrote:
>=20
> > Hi Chris,
> >
> > No worries. We have a very similair setup at work, transferring
> > summarised data from oracle into MySQL.
> >
> > Depending on the database you are transferring too, couldnt you set a
> > trigger, inserting NULL (eg, a python None from Oracle) into the table
> > generates an empty string?
> >
> > Failing that, subclassing cx_Oracles cursor object to return a the
> > empty string when the column type is textual and the value is null
> > might be another option.
> >
> > Good luck,
> >
> > Leith
> >
> >
> > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > Leith,
> > >
> > > I didn't mention that for 1) the target table is in a separate databa=
se and using database
> > links
> > > isn't an option (although I wish it was) due to reasons that are too =
lengthy to go into here.
> > > Hence  PL/SQL isn't really viable. So I'd still like to look at curso=
r.fetchraw()
> > >
> > > Now for 2) you maybe right that PL/SQL could be used but I'll still h=
ave to write out to file
> > > using Python as util_file can only write to files that the database s=
erver can "see" which
> > isn't
> > > always the case for my app.
> > >
> > > Thanks for the idea,
> > >
> > > Chris
> > >
> > >
> > > --- Leith Parkin <lei...@gm...> wrote:
> > > > This sounds like something better done in PL/SQL?
> > > >
> > > > Regards,
> > > >
> > > > Leith
> > > > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > > > I'm looking to use cursor.fetchraw to help improve performance in=
 an app I'm writing. I've
> > > > looked
> > > > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it=
 out.
> > > > > If possible I'd like a simple full example to show me the way. Th=
e context is:
> > > > >
> > > > > 1) Retrieve rows from source table and insert into a target table=
 which has exactly the
> > same
> > > > > structure.
> > > > >
> > > > > 2) Retrieve rows from source table, add some column formatting an=
d write out to a file.
> > > > >
> > > > > Thanks very much,
> > > > >
> > > > > Chris Dunscombe
> > > > >
> > > > > PS Thanks for all the input re my previous posting on Nulls and N=
one. I've still got some
> > > > thinking
> > > > > and work to do on which is best for performance and how best to c=
ope with different
> > datatypes,
> > > > > particularly Longs (what a pain they are!!)
> > > > >
> > > > > __________________________________
> > > > > Yahoo! Mail Mobile
> > > > > Take Yahoo! Mail with you! Check email on your mobile phone.
> > > > > http://mobile.yahoo.com/learn/mail
> > > > >
> > > > > -------------------------------------------------------
> > > > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > > > Want to be the first software developer in space?
> > > > > Enter now for the Oracle Space Sweepstakes!
> > > > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > > > _______________________________________________
> > > > > cx-oracle-users mailing list
> > > > > cx-...@li...
> > > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > > >
> > > >
> > > >
> > > > -------------------------------------------------------
> > > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > > Want to be the first software developer in space?
> > > > Enter now for the Oracle Space Sweepstakes!
> > > > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > > > _______________________________________________
> > > > cx-oracle-users mailing list
> > > > cx-...@li...
> > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > >
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Tired of spam?  Yahoo! Mail has the best spam protection around
> > > http://mail.yahoo.com
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>=20
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Help with fetchraw

[Chris D. <cdu...@ya...>]

Leith,

Thanks, I like the sub-classing idea but my OO Python isn't that good so if you could give me a
code example that would be much appreciated.

Thanks very much,

Chris
--- Leith Parkin <lei...@gm...> wrote:

> Hi Chris,
> 
> No worries. We have a very similair setup at work, transferring
> summarised data from oracle into MySQL.
> 
> Depending on the database you are transferring too, couldnt you set a
> trigger, inserting NULL (eg, a python None from Oracle) into the table
> generates an empty string?
> 
> Failing that, subclassing cx_Oracles cursor object to return a the
> empty string when the column type is textual and the value is null
> might be another option.
> 
> Good luck,
> 
> Leith
> 
> 
> On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> > Leith,
> > 
> > I didn't mention that for 1) the target table is in a separate database and using database
> links
> > isn't an option (although I wish it was) due to reasons that are too lengthy to go into here.
> > Hence  PL/SQL isn't really viable. So I'd still like to look at cursor.fetchraw()
> > 
> > Now for 2) you maybe right that PL/SQL could be used but I'll still have to write out to file
> > using Python as util_file can only write to files that the database server can "see" which
> isn't
> > always the case for my app.
> > 
> > Thanks for the idea,
> > 
> > Chris
> > 
> > 
> > --- Leith Parkin <lei...@gm...> wrote:
> > > This sounds like something better done in PL/SQL?
> > >
> > > Regards,
> > >
> > > Leith
> > > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > > I'm looking to use cursor.fetchraw to help improve performance in an app I'm writing. I've
> > > looked
> > > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out.
> > > > If possible I'd like a simple full example to show me the way. The context is:
> > > >
> > > > 1) Retrieve rows from source table and insert into a target table which has exactly the
> same
> > > > structure.
> > > >
> > > > 2) Retrieve rows from source table, add some column formatting and write out to a file.
> > > >
> > > > Thanks very much,
> > > >
> > > > Chris Dunscombe
> > > >
> > > > PS Thanks for all the input re my previous posting on Nulls and None. I've still got some
> > > thinking
> > > > and work to do on which is best for performance and how best to cope with different
> datatypes,
> > > > particularly Longs (what a pain they are!!)
> > > >
> > > > __________________________________
> > > > Yahoo! Mail Mobile
> > > > Take Yahoo! Mail with you! Check email on your mobile phone.
> > > > http://mobile.yahoo.com/learn/mail
> > > >
> > > > -------------------------------------------------------
> > > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > > Want to be the first software developer in space?
> > > > Enter now for the Oracle Space Sweepstakes!
> > > > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> > > > _______________________________________________
> > > > cx-oracle-users mailing list
> > > > cx-...@li...
> > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > >
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> > 
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [cx-oracle-users] Help with fetchraw

[Leith P. <lei...@gm...>]

Hi Chris,

No worries. We have a very similair setup at work, transferring
summarised data from oracle into MySQL.

Depending on the database you are transferring too, couldnt you set a
trigger, inserting NULL (eg, a python None from Oracle) into the table
generates an empty string?

Failing that, subclassing cx_Oracles cursor object to return a the
empty string when the column type is textual and the value is null
might be another option.

Good luck,

Leith


On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> Leith,
>=20
> I didn't mention that for 1) the target table is in a separate database a=
nd using database links
> isn't an option (although I wish it was) due to reasons that are too leng=
thy to go into here.
> Hence  PL/SQL isn't really viable. So I'd still like to look at cursor.fe=
tchraw()
>=20
> Now for 2) you maybe right that PL/SQL could be used but I'll still have =
to write out to file
> using Python as util_file can only write to files that the database serve=
r can "see" which isn't
> always the case for my app.
>=20
> Thanks for the idea,
>=20
> Chris
>=20
>=20
> --- Leith Parkin <lei...@gm...> wrote:
> > This sounds like something better done in PL/SQL?
> >
> > Regards,
> >
> > Leith
> > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > I'm looking to use cursor.fetchraw to help improve performance in an =
app I'm writing. I've
> > looked
> > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out=
.
> > > If possible I'd like a simple full example to show me the way. The co=
ntext is:
> > >
> > > 1) Retrieve rows from source table and insert into a target table whi=
ch has exactly the same
> > > structure.
> > >
> > > 2) Retrieve rows from source table, add some column formatting and wr=
ite out to a file.
> > >
> > > Thanks very much,
> > >
> > > Chris Dunscombe
> > >
> > > PS Thanks for all the input re my previous posting on Nulls and None.=
 I've still got some
> > thinking
> > > and work to do on which is best for performance and how best to cope =
with different datatypes,
> > > particularly Longs (what a pain they are!!)
> > >
> > > __________________________________
> > > Yahoo! Mail Mobile
> > > Take Yahoo! Mail with you! Check email on your mobile phone.
> > > http://mobile.yahoo.com/learn/mail
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>=20
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Help with fetchraw

[Chris D. <cdu...@ya...>]

Leith,

I didn't mention that for 1) the target table is in a separate database and using database links
isn't an option (although I wish it was) due to reasons that are too lengthy to go into here.
Hence  PL/SQL isn't really viable. So I'd still like to look at cursor.fetchraw()

Now for 2) you maybe right that PL/SQL could be used but I'll still have to write out to file
using Python as util_file can only write to files that the database server can "see" which isn't
always the case for my app.

Thanks for the idea,

Chris


--- Leith Parkin <lei...@gm...> wrote:
> This sounds like something better done in PL/SQL?
> 
> Regards,
> 
> Leith
> On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> > I'm looking to use cursor.fetchraw to help improve performance in an app I'm writing. I've
> looked
> > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out.
> > If possible I'd like a simple full example to show me the way. The context is:
> > 
> > 1) Retrieve rows from source table and insert into a target table which has exactly the same
> > structure.
> > 
> > 2) Retrieve rows from source table, add some column formatting and write out to a file.
> > 
> > Thanks very much,
> > 
> > Chris Dunscombe
> > 
> > PS Thanks for all the input re my previous posting on Nulls and None. I've still got some
> thinking
> > and work to do on which is best for performance and how best to cope with different datatypes,
> > particularly Longs (what a pain they are!!)
> > 
> > __________________________________
> > Yahoo! Mail Mobile
> > Take Yahoo! Mail with you! Check email on your mobile phone.
> > http://mobile.yahoo.com/learn/mail
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [cx-oracle-users] Help with fetchraw

[Leith P. <lei...@gm...>]

This sounds like something better done in PL/SQL?

Regards,

Leith
On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> I'm looking to use cursor.fetchraw to help improve performance in an app =
I'm writing. I've looked
> at CopyData from cx_OracleTools but I'm afraid I couldn't work it out.
> If possible I'd like a simple full example to show me the way. The contex=
t is:
>=20
> 1) Retrieve rows from source table and insert into a target table which h=
as exactly the same
> structure.
>=20
> 2) Retrieve rows from source table, add some column formatting and write =
out to a file.
>=20
> Thanks very much,
>=20
> Chris Dunscombe
>=20
> PS Thanks for all the input re my previous posting on Nulls and None. I'v=
e still got some thinking
> and work to do on which is best for performance and how best to cope with=
 different datatypes,
> particularly Longs (what a pain they are!!)
>=20
> __________________________________
> Yahoo! Mail Mobile
> Take Yahoo! Mail with you! Check email on your mobile phone.
> http://mobile.yahoo.com/learn/mail
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Help with fetchraw

[Chris D. <cdu...@ya...>]

I'm looking to use cursor.fetchraw to help improve performance in an app I'm writing. I've looked
at CopyData from cx_OracleTools but I'm afraid I couldn't work it out.
If possible I'd like a simple full example to show me the way. The context is:

1) Retrieve rows from source table and insert into a target table which has exactly the same
structure.

2) Retrieve rows from source table, add some column formatting and write out to a file.

Thanks very much,

Chris Dunscombe

PS Thanks for all the input re my previous posting on Nulls and None. I've still got some thinking
and work to do on which is best for performance and how best to cope with different datatypes,
particularly Longs (what a pain they are!!)


		
__________________________________ 
Yahoo! Mail Mobile 
Take Yahoo! Mail with you! Check email on your mobile phone. 
http://mobile.yahoo.com/learn/mail 

[cx-oracle-users] python 2.3.4 + cx_oracle 4.1 + utf-8 error

[Maxim K. <kul...@cs...>]

Hello!

Trying to fetch long varchar2 column and get the following error:

cx_Oracle.DatabaseError: column at array pos 0 fetched with error: 1406

i.e. string buffer is not much enough to fetch the string.

# fragment of code...
myCon = cx_Oracle.connect(user, psw, dsn)
myCur = myCon.cursor()
myCur.execute("""
select COLUMN from TABLE where ID=1
""")
for record in myCur.fetchall():
# ...

Error is reproduced only if actual string value longer than half of 
declared column size. For short strings all is ok.

If I print myCur.description, I get:

[('COLUMN', <type 'cx_Oracle.STRING'>, 250, 250, 0, 0, 1)]

250 - declared column max size, but I guess cx_Oracle allocates only 250 
bytes(!), so if my string longer than 125 chars (utf-8 national char 
occupies > 1 byte) - I get the error.

Is it bug or what? Any suggestions?

Extra info:
     OS - SuSE Linux 9.2
     Client - Oracle Client 9.2.0.1.0
     NLS_LANG="RUSSIAN_CIS.UTF8"

-- 
Best regards,
Maxim Kuleshov

RE: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Orr, S. <so...@ri...>]

If this is for display can you deal with spaces?

Select nvl(null,' ') from dual

Hack if you can't use spaces...
Print data[0][0].strip()



-----Original Message-----
From: cx-...@li... =
[mailto:cx-...@li...] On Behalf Of Chris =
Dunscombe
Sent: Wednesday, May 11, 2005 9:34 AM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Nulls returned as None and not as as =
"empty or zero lenght string"

Marcos,

Great idea (nvl as opposed to isnull). However cx_Oracle still returns =
None e.g.

cur1.execute("select nvl(subobject_name,'') from objects_big where =
rownum < 5")
data =3D cur1.fetchall()
print data
[(None,), (None,), (None,), (None,), (None,)]

I thought you'd "fixed it". Thanks for the idea anyway.

Chris

--- Marcos S=E1nchez Provencio <ma...@bu...> wrote:

> you may try to do it at oracle level
>=20
> select isnull(col,'')...
>=20
> it should be faster
>=20
> El mi=E9, 11-05-2005 a las 07:15 -0700, Chris Dunscombe escribi=F3:
> > All,
> >=20
> > Is there anyway that Null values returned from a query can be an =
"empty or zero length string"
> > instead of None?
> >=20
> > This may seem a strange request as it's easy to use type to test if =
a null has been returned
> as
> > None. However I've a performance critical app where I need to output =
"nothing" (zero length /
> > empty string) if the value is null. Hence at present I have to =
perform this "type test" on ALL
> > columns that don't have a "Not Null" constraint in the database =
which is quite an overhead
> when in
> > some cases I need to do it millions and sometimes billions of times.
> >=20
> > Thanks,
> >=20
> > Chris Dunscombe
> >=20
> >=20
> > 	=09
> > __________________________________=20
> > Do you Yahoo!?=20
> > Read only the mail you want - Yahoo! Mail SpamGuard.=20
> > http://promotions.yahoo.com/new_mail=20
> >=20
> >=20
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>=20



	=09
Discover Yahoo!=20
Stay in touch with email, IM, photo sharing and more. Check it out!=20
http://discover.yahoo.com/stayintouch.html


-------------------------------------------------------
This SF.Net email is sponsored by Oracle Space Sweepstakes
Want to be the first software developer in space?
Enter now for the Oracle Space Sweepstakes!
http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[Chris D. <cdu...@ya...>]

Danny,

Thanks for the idea, I'll give it a look.

Chris
--- "D.R.   Boxhoorn" <da...@as...> wrote:

> 
> Hoi Chris,
> 
> Have you considered the following decorate/undecorate pattern?
> 
>    SQL:    SELECT strcolumn || 'x' FROM ...
> 
>    python: strcolumn = row[0][:-1]
> 
> As far as I can tell this will give about as much overhead as an if statement.
> However, only benchmarking your application will tell you what works best.
> 
>                                                    Danny
> 
> 
> On Thu, May 12, 2005 at 01:22:56AM -0700, Chris Dunscombe wrote:
> > Henning,
> > 
> > I know what you mean, but the issue is that this "if test" has to go in some sort of function
> and
> > be called for each col returned in each row and it's the function call overhead that's really
> the
> > issue. I'd like to use the following:
> > 
> > append = rowsOutput.append
> > for row in data: # where data is the resultset from a query
> >     rowsGroup = rowsGroup + 1
> >     append("%s%s%s%s" %(row[0], colSep, row[1], colSep)) # In reality many more columns
> >     append(recSep)
> >     if rowsGroup > :
> >         outFile.write(''.join(rowsOutput))
> >         rowsGroup = 0
> >         rowsOutput = []
> > outFile.write(''.join(rowsOutput))
> > 
> > Now if I need to do the test on None then I believe I need to call a function or have the if
> test
> > inline in a loop and with many columns this is quite an overhead. In my test case on a 13
> column
> > table returning 200K rows:
> > 
> > Doing the if test approx 20 CPU secs in Python
> > No if test approx 16 CPU secs secs in Python
> > 
> > A 20% reduction. I know the numbers here are small but given the app will be running against
> many
> > tables concurrently and extracting millions of rows the potential saving is significant.
> > 
> > Thanks for taking the time to respond.
> > 
> > Cheers,
> > 
> > Chris
> > 
> > --- Henning von Bargen <H.v...@t-...> wrote:
> > > Chris,
> > > 
> > > why do you think the simple statement "if x is None: x=''" means an
> > > overhead?
> > > It means about 0.000(insert zeroes here)1 percent performance overhead,
> > > because transferring/outputting the data is what takes most of time.
> > > 
> > > And - with Python - it should not mean too much typing overhead.
> > > You could easily write a function to do this automatically for each
> > > record.
> > > 
> > > HTH
> > > Henning
> > > 
> > > 
> > > > -----Urspr?ngliche Nachricht-----
> > > > From: Chris Dunscombe <cdu...@ya...>
> > > > To: cx-oracle-list <cx-...@li...>
> > > > Subject: [cx-oracle-users] Nulls returned as None and not as 
> > > > as "empty or zero lenght string"
> > > > Reply-To: cx-...@li...
> > > > 
> > > > All,
> > > > 
> > > > Is there anyway that Null values returned from a query can be 
> > > > an "empty or zero length string"
> > > > instead of None?
> > > > 
> > > > This may seem a strange request as it's easy to use type to 
> > > > test if a null has been returned as
> > > > None. However I've a performance critical app where I need to 
> > > > output "nothing" (zero length /
> > > > empty string) if the value is null. Hence at present I have 
> > > > to perform this "type test" on ALL
> > > > columns that don't have a "Not Null" constraint in the 
> > > > database which is quite an overhead when in
> > > > some cases I need to do it millions and sometimes billions of times.
> > > > 
> > > > Thanks,
> > > > 
> > > > Chris Dunscombe
> > > > 
> > > 
> > > 
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > 
> > 
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam protection around 
> > http://mail.yahoo.com 
> > 
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 



		
Discover Yahoo! 
Find restaurants, movies, travel and more fun for the weekend. Check it out! 
http://discover.yahoo.com/weekend.html 

Re: [cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[Chris D. <cdu...@ya...>]

Rich,

Thanks for the idea, I'll give it a look.

Chris
--- Richard Moore <ri...@we...> wrote:
> 
> 
> Chris Dunscombe wrote:
> > Now if I need to do the test on None then I believe I need to call a
> > function or have the if test inline in a loop and with many columns
> > this is quite an overhead. In my test case on a 13 column table
> > returning 200K rows:
> 
> If your application is that performance critical then you might
> want to benchmark a few different implementations. eg. using an
> array instead of an if with something like:
> 
> newowner    = [self.newowner, None][self.contactid == self.newowner]
> 
> This is taken from one of my apps, you'll obviously need to
> modify if for yours.
> 
> Rich.
> -- 
> Richard Moore, Principal Software Engineer,
> Westpoint Ltd,
> Albion Wharf, 19 Albion Street, Manchester, M1 5LN, England
> Tel: +44 161 237 1028
> Fax: +44 161 237 1031
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 


		
__________________________________ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 

Re: [cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[D.R. B. <da...@as...>]

Hoi Chris,

Have you considered the following decorate/undecorate pattern?

   SQL:    SELECT strcolumn || 'x' FROM ...

   python: strcolumn =3D row[0][:-1]

As far as I can tell this will give about as much overhead as an if stateme=
nt.
However, only benchmarking your application will tell you what works best.

                                                   Danny


On Thu, May 12, 2005 at 01:22:56AM -0700, Chris Dunscombe wrote:
> Henning,
>=20
> I know what you mean, but the issue is that this "if test" has to go in s=
ome sort of function and
> be called for each col returned in each row and it's the function call ov=
erhead that's really the
> issue. I'd like to use the following:
>=20
> append =3D rowsOutput.append
> for row in data: # where data is the resultset from a query
>     rowsGroup =3D rowsGroup + 1
>     append("%s%s%s%s" %(row[0], colSep, row[1], colSep)) # In reality man=
y more columns
>     append(recSep)
>     if rowsGroup > :
>         outFile.write(''.join(rowsOutput))
>         rowsGroup =3D 0
>         rowsOutput =3D []
> outFile.write(''.join(rowsOutput))
>=20
> Now if I need to do the test on None then I believe I need to call a func=
tion or have the if test
> inline in a loop and with many columns this is quite an overhead. In my t=
est case on a 13 column
> table returning 200K rows:
>=20
> Doing the if test approx 20 CPU secs in Python
> No if test approx 16 CPU secs secs in Python
>=20
> A 20% reduction. I know the numbers here are small but given the app will=
 be running against many
> tables concurrently and extracting millions of rows the potential saving =
is significant.
>=20
> Thanks for taking the time to respond.
>=20
> Cheers,
>=20
> Chris
>=20
> --- Henning von Bargen <H.v...@t-...> wrote:
> > Chris,
> >=20
> > why do you think the simple statement "if x is None: x=3D''" means an
> > overhead?
> > It means about 0.000(insert zeroes here)1 percent performance overhead,
> > because transferring/outputting the data is what takes most of time.
> >=20
> > And - with Python - it should not mean too much typing overhead.
> > You could easily write a function to do this automatically for each
> > record.
> >=20
> > HTH
> > Henning
> >=20
> >=20
> > > -----Urspr?ngliche Nachricht-----
> > > From: Chris Dunscombe <cdu...@ya...>
> > > To: cx-oracle-list <cx-...@li...>
> > > Subject: [cx-oracle-users] Nulls returned as None and not as=20
> > > as "empty or zero lenght string"
> > > Reply-To: cx-...@li...
> > >=20
> > > All,
> > >=20
> > > Is there anyway that Null values returned from a query can be=20
> > > an "empty or zero length string"
> > > instead of None?
> > >=20
> > > This may seem a strange request as it's easy to use type to=20
> > > test if a null has been returned as
> > > None. However I've a performance critical app where I need to=20
> > > output "nothing" (zero length /
> > > empty string) if the value is null. Hence at present I have=20
> > > to perform this "type test" on ALL
> > > columns that don't have a "Not Null" constraint in the=20
> > > database which is quite an overhead when in
> > > some cases I need to do it millions and sometimes billions of times.
> > >=20
> > > Thanks,
> > >=20
> > > Chris Dunscombe
> > >=20
> >=20
> >=20
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >=20
>=20
>=20
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around=20
> http://mail.yahoo.com=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[Richard M. <ri...@we...>]

Chris Dunscombe wrote:
> Now if I need to do the test on None then I believe I need to call a
> function or have the if test inline in a loop and with many columns
> this is quite an overhead. In my test case on a 13 column table
> returning 200K rows:

If your application is that performance critical then you might
want to benchmark a few different implementations. eg. using an
array instead of an if with something like:

newowner    = [self.newowner, None][self.contactid == self.newowner]

This is taken from one of my apps, you'll obviously need to
modify if for yours.

Rich.
-- 
Richard Moore, Principal Software Engineer,
Westpoint Ltd,
Albion Wharf, 19 Albion Street, Manchester, M1 5LN, England
Tel: +44 161 237 1028
Fax: +44 161 237 1031

Re: [cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[Chris D. <cdu...@ya...>]

Henning,

I know what you mean, but the issue is that this "if test" has to go in some sort of function and
be called for each col returned in each row and it's the function call overhead that's really the
issue. I'd like to use the following:

append = rowsOutput.append
for row in data: # where data is the resultset from a query
    rowsGroup = rowsGroup + 1
    append("%s%s%s%s" %(row[0], colSep, row[1], colSep)) # In reality many more columns
    append(recSep)
    if rowsGroup > :
        outFile.write(''.join(rowsOutput))
        rowsGroup = 0
        rowsOutput = []
outFile.write(''.join(rowsOutput))

Now if I need to do the test on None then I believe I need to call a function or have the if test
inline in a loop and with many columns this is quite an overhead. In my test case on a 13 column
table returning 200K rows:

Doing the if test approx 20 CPU secs in Python
No if test approx 16 CPU secs secs in Python

A 20% reduction. I know the numbers here are small but given the app will be running against many
tables concurrently and extracting millions of rows the potential saving is significant.

Thanks for taking the time to respond.

Cheers,

Chris

--- Henning von Bargen <H.v...@t-...> wrote:
> Chris,
> 
> why do you think the simple statement "if x is None: x=''" means an
> overhead?
> It means about 0.000(insert zeroes here)1 percent performance overhead,
> because transferring/outputting the data is what takes most of time.
> 
> And - with Python - it should not mean too much typing overhead.
> You could easily write a function to do this automatically for each
> record.
> 
> HTH
> Henning
> 
> 
> > -----Ursprüngliche Nachricht-----
> > From: Chris Dunscombe <cdu...@ya...>
> > To: cx-oracle-list <cx-...@li...>
> > Subject: [cx-oracle-users] Nulls returned as None and not as 
> > as "empty or zero lenght string"
> > Reply-To: cx-...@li...
> > 
> > All,
> > 
> > Is there anyway that Null values returned from a query can be 
> > an "empty or zero length string"
> > instead of None?
> > 
> > This may seem a strange request as it's easy to use type to 
> > test if a null has been returned as
> > None. However I've a performance critical app where I need to 
> > output "nothing" (zero length /
> > empty string) if the value is null. Hence at present I have 
> > to perform this "type test" on ALL
> > columns that don't have a "Not Null" constraint in the 
> > database which is quite an overhead when in
> > some cases I need to do it millions and sometimes billions of times.
> > 
> > Thanks,
> > 
> > Chris Dunscombe
> > 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

[cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[Henning v. B. <H.v...@t-...>]

Chris,

why do you think the simple statement "if x is None: x=3D''" means an
overhead?
It means about 0.000(insert zeroes here)1 percent performance overhead,
because transferring/outputting the data is what takes most of time.

And - with Python - it should not mean too much typing overhead.
You could easily write a function to do this automatically for each
record.

HTH
Henning


> -----Urspr=FCngliche Nachricht-----
> From: Chris Dunscombe <cdu...@ya...>
> To: cx-oracle-list <cx-...@li...>
> Subject: [cx-oracle-users] Nulls returned as None and not as=20
> as "empty or zero lenght string"
> Reply-To: cx-...@li...
>=20
> All,
>=20
> Is there anyway that Null values returned from a query can be=20
> an "empty or zero length string"
> instead of None?
>=20
> This may seem a strange request as it's easy to use type to=20
> test if a null has been returned as
> None. However I've a performance critical app where I need to=20
> output "nothing" (zero length /
> empty string) if the value is null. Hence at present I have=20
> to perform this "type test" on ALL
> columns that don't have a "Not Null" constraint in the=20
> database which is quite an overhead when in
> some cases I need to do it millions and sometimes billions of times.
>=20
> Thanks,
>=20
> Chris Dunscombe
>=20

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Paul M. <p.f...@gm...>]

On 5/11/05, Marcos S=E1nchez Provencio <ma...@bu...> wrote:
> you may try to do it at oracle level
>=20
> select isnull(col,'')...

That won't work, precisely because Oracle doesn't distinguish between
NULL and ''. This is a problem with Oracle rather than anything else.

You might be able to get away with NVL(col, ' ') and return a single
space, but that is different from an empty string, and so may not
satisfy your application's requirements.

Paul.

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Anthony T. <ant...@gm...>]

Oracle is interesting in this respect. An empty string is implicitly
changed to NULL in Oracle -- thus nvl(column, "") is pointless since
you'll get null back anyway. :-)

On 5/11/05, Chris Dunscombe <cdu...@ya...> wrote:
> Marcos,
>=20
> Great idea (nvl as opposed to isnull). However cx_Oracle still returns No=
ne e.g.
>=20
> cur1.execute("select nvl(subobject_name,'') from objects_big where rownum=
 < 5")
> data =3D cur1.fetchall()
> print data
> [(None,), (None,), (None,), (None,), (None,)]
>=20
> I thought you'd "fixed it". Thanks for the idea anyway.
>=20
> Chris
>=20
> --- Marcos S=E1nchez Provencio <ma...@bu...> wrote:
>=20
> > you may try to do it at oracle level
> >
> > select isnull(col,'')...
> >
> > it should be faster
> >
> > El mi=E9, 11-05-2005 a las 07:15 -0700, Chris Dunscombe escribi=F3:
> > > All,
> > >
> > > Is there anyway that Null values returned from a query can be an "emp=
ty or zero length string"
> > > instead of None?
> > >
> > > This may seem a strange request as it's easy to use type to test if a=
 null has been returned
> > as
> > > None. However I've a performance critical app where I need to output =
"nothing" (zero length /
> > > empty string) if the value is null. Hence at present I have to perfor=
m this "type test" on ALL
> > > columns that don't have a "Not Null" constraint in the database which=
 is quite an overhead
> > when in
> > > some cases I need to do it millions and sometimes billions of times.
> > >
> > > Thanks,
> > >
> > > Chris Dunscombe
> > >
> > >
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > Read only the mail you want - Yahoo! Mail SpamGuard.
> > > http://promotions.yahoo.com/new_mail
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>=20
> Discover Yahoo!
> Stay in touch with email, IM, photo sharing and more. Check it out!
> http://discover.yahoo.com/stayintouch.html
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>