Re: . iterating using fetchmany

Joram Agten <[email protected]>
Newsgroups gmane.comp.python.db.cx-oracle
Message-ID <CAAybBsATRgajd16rXTbMNzUNkJzEwk6qNieet0hCf1B4_4Hb2A@mail.gmail.com> 
note that TO_CLOB (and I think also TO_CHAR) uses the temporary tablespace
to create the strings.
If that tablespace is not big enough, or getting slow when growing, that
can cause issues.
So during the query take a look at the size of the temporary tablespace for
the connected user.

2014-11-26 16:21 GMT+01:00 Rodman, Daniel <drodman-LjRBWs/[email protected]>:

> Thanks for the responses...
>
> My code appears to also hang on fetchall() and when I try iterating on the
> cursor, as was suggested by Anthony:
>     >> for row in cursor_metadata:
>     >>     print row
>
> After some tinkering, I noticed that I can use fetchmany() to iterate
> through data sets from other tables without any issue. It's just this
> particular table that's giving me problems.
>
> Could unclosed cursors be an issue?
>
>
> -----Original Message-----
> From: cx-oracle-users-request-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org [mailto:
> cx-oracle-users-request-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org]
> Sent: Tuesday, November 25, 2014 10:50 PM
> To: cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> Subject: cx-oracle-users Digest, Vol 97, Issue 1
>
> Send cx-oracle-users mailing list submissions to
>         cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
>         https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> or, via email, send a message with subject or body 'help' to
>         cx-oracle-users-request-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>
> You can reach the person managing the list at
>         cx-oracle-users-owner-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>
> When replying, please edit your Subject line so it is more specific than
> "Re: Contents of cx-oracle-users digest..."
>
>
> Today's Topics:
>
>    1. iterating using fetchmany (Rodman, Daniel)
>    2. Re: iterating using fetchmany (Fawcett, David (MNIT))
>    3. Re: iterating using fetchmany (Anthony Tuininga)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Tue, 25 Nov 2014 21:22:58 +0000
> From: "Rodman, Daniel" <drodman-LjRBWs/[email protected]>
> Subject: [cx-oracle-users] iterating using fetchmany
> To: "cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org"
>         <cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>
> Message-ID:
>         <
> A4A1C995458F8B48B86E57568EF4AE8DB68009-Yn6e/[email protected]>
> Content-Type: text/plain; charset="us-ascii"
>
> I'm working with python and oracle using the cx_Oracle module. I'm having
> issues getting fetchmany to iterate over my result set correctly. The table
> in the query, i2test, has over 20,000 rows, however the while loop hangs
> when it gets to about 1,800 rows. If I remove the order by clause... the
> loop hangs at about 12,000. Any advice?
>
> cursor_metadata = cx_Oracle.Cursor(i_connection)
>
> query_select = """SELECT name, code, cname, TO_CHAR(updatedate),
> TO_CHAR(downloaddate), TO_CHAR(importdate)
>                                       FROM imetadata.i2test
>                                       WHERE code like '%DIN%' AND
>                                           cd = 'N'
>                                       ORDER BY code"""
>
> cursor_metadata.execute(query_select)
>
> count = 0
> results = cursor_metadata.fetchmany(100) while results:
>     print count
>     count += 1
>     results = cursor_metadata.fetchmany(100)
>
> I realize this is probably something simple... thanks in advance.
>
>
> Dan
> -------------- next part --------------
> An HTML attachment was scrubbed...
>
> ------------------------------
>
> Message: 2
> Date: Tue, 25 Nov 2014 21:50:24 +0000
> From: "Fawcett, David (MNIT)" <[email protected]>
> Subject: Re: [cx-oracle-users] iterating using fetchmany
> To: "cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org"
>         <cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>
> Message-ID:
>         <
> D645DD294B659443AAFDEFBEC8F1E9820AA12086-FeAHNZULU7LiTqIcKZ1S2OOxy0qZv+7hLnY5E4hWTkheoWH0uzbU5w@public.gmane.org
> >
>
> Content-Type: text/plain; charset="us-ascii"
>
> I don't see an obvious issue with your code, but you may just want to try
> using .fetchall()  Depending on your machine, 20,000 rows shouldn't be a
> problem.
>
> Once you have all of the data, you can then iterate through it and do what
> you want with it.
>
> data = cursor_metadata.fetchall()
>
> for row in data:
>     print row
>
> From: Rodman, Daniel [mailto:drodman-LjRBWs/[email protected]]
> Sent: Tuesday, November 25, 2014 3:23 PM
> To: cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> Subject: [cx-oracle-users] iterating using fetchmany
>
> I'm working with python and oracle using the cx_Oracle module. I'm having
> issues getting fetchmany to iterate over my result set correctly. The table
> in the query, i2test, has over 20,000 rows, however the while loop hangs
> when it gets to about 1,800 rows. If I remove the order by clause... the
> loop hangs at about 12,000. Any advice?
>
> cursor_metadata = cx_Oracle.Cursor(i_connection)
>
> query_select = """SELECT name, code, cname, TO_CHAR(updatedate),
> TO_CHAR(downloaddate), TO_CHAR(importdate)
>                                       FROM imetadata.i2test
>                                       WHERE code like '%DIN%' AND
>                                           cd = 'N'
>                                       ORDER BY code"""
>
> cursor_metadata.execute(query_select)
>
> count = 0
> results = cursor_metadata.fetchmany(100) while results:
>     print count
>     count += 1
>     results = cursor_metadata.fetchmany(100)
>
> I realize this is probably something simple... thanks in advance.
>
>
> Dan
> -------------- next part --------------
> An HTML attachment was scrubbed...
>
> ------------------------------
>
> Message: 3
> Date: Tue, 25 Nov 2014 20:49:30 -0700
> From: Anthony Tuininga <[email protected]>
> Subject: Re: [cx-oracle-users] iterating using fetchmany
> To: cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> Message-ID:
>         <
> CAE1XR-6u2J7uv_GPcmvwiaODgb_QnAAAoHkarDNHGWyjCJxYug-JsoAwUIsXosN+BqQ9rBEUg@public.gmane.org>
> Content-Type: text/plain; charset="utf-8"
>
> You can also use this code:
>
> for row in cursor_metadata:
>     print row
>
> Internally this does the equivalent of fetchmany() anyway. You can
> determine how big of a buffer you want to use by using cursor.arraysize.
>
> Anthony
>
> On Tue, Nov 25, 2014 at 2:50 PM, Fawcett, David (MNIT) <
> [email protected]> wrote:
>
> >  I don?t see an obvious issue with your code, but you may just want to
> > try using .fetchall()  Depending on your machine, 20,000 rows
> > shouldn?t be a problem.
> >
> >
> >
> > Once you have all of the data, you can then iterate through it and do
> > what you want with it.
> >
> >
> >
> > data = cursor_metadata.fetchall()
> >
> >
> >
> > for row in data:
> >
> >     print row
> >
> >
> >
> > *From:* Rodman, Daniel [mailto:drodman-LjRBWs/[email protected]]
> > *Sent:* Tuesday, November 25, 2014 3:23 PM
> > *To:* cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> > *Subject:* [cx-oracle-users] iterating using fetchmany
> >
> >
> >
> > I'm working with python and oracle using the cx_Oracle module. I'm
> > having issues getting fetchmany to iterate over my result set
> > correctly. The table in the query, i2test, has over 20,000 rows,
> > however the while loop hangs when it gets to about 1,800 rows. If I
> > remove the order by clause... the loop hangs at about 12,000. Any advice?
> >
> >
> >
> > cursor_metadata = cx_Oracle.Cursor(i_connection)
> >
> >
> >
> > query_select = """SELECT name, code, cname, TO_CHAR(updatedate),
> > TO_CHAR(downloaddate), TO_CHAR(importdate)
> >
> >                                       FROM imetadata.i2test
> >
> >                                       WHERE code like '%DIN%' AND
> >
> >                                           cd = 'N'
> >
> >                                       ORDER BY code"""
> >
> >
> >
> > cursor_metadata.execute(query_select)
> >
> >
> >
> > count = 0
> >
> > results = cursor_metadata.fetchmany(100)
> >
> > while results:
> >
> >     print count
> >
> >     count += 1
> >
> >     results = cursor_metadata.fetchmany(100)
> >
> >
> >
> > I realize this is probably something simple... thanks in advance.
> >
> >
> >
> >
> >
> > Dan
> >
> >
> > ----------------------------------------------------------------------
> > -------- Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT
> > Server from Actuate! Instantly Supercharge Your Business Reports and
> > Dashboards with Interactivity, Sharing, Native Excel Exports, App
> > Integration & more Get technology previously reserved for
> > billion-dollar corporations, FREE
> >
> > http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.
> > clktrk _______________________________________________
> > cx-oracle-users mailing list
> > cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> -------------- next part --------------
> An HTML attachment was scrubbed...
>
> ------------------------------
>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server from
> Actuate! Instantly Supercharge Your Business Reports and Dashboards with
> Interactivity, Sharing, Native Excel Exports, App Integration & more Get
> technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
>
> ------------------------------
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> End of cx-oracle-users Digest, Vol 97, Issue 1
> **********************************************
>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
>
> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
> _______________________________________________
> cx-oracle-users mailing list
> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk

_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.