Re: iterating using fetchmany (size)
"Rodman, Daniel" <drodman-LjRBWs/[email protected]>
| Newsgroups | gmane.comp.python.db.cx-oracle |
|---|---|
| Message-ID | <A4A1C995458F8B48B86E57568EF4AE8DB6804F@mb-ls3.itorg.ad.buffalo.edu> |
I think I've narrowed the issue to size. When I try to retrieve certain columns eg. varchar2(2000), I can't iterate through all the rows of the data set. How do I deal with larger row sizes? -----Original Message----- From: cx-oracle-users-request-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org [mailto:cx-oracle-users-request-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org] Sent: Wednesday, November 26, 2014 10:41 AM To: cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org Subject: cx-oracle-users Digest, Vol 97, Issue 2 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. Re: . iterating using fetchmany (Rodman, Daniel) 2. Re: . iterating using fetchmany (Joram Agten) ---------------------------------------------------------------------- Message: 1 Date: Wed, 26 Nov 2014 15:21:56 +0000 From: "Rodman, Daniel" <drodman-LjRBWs/[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: <A4A1C995458F8B48B86E57568EF4AE8DB68037-Yn6e/[email protected]> Content-Type: text/plain; charset="us-ascii" 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 ********************************************** ------------------------------ Message: 2 Date: Wed, 26 Nov 2014 16:41:18 +0100 From: Joram Agten <[email protected]> Subject: Re: [cx-oracle-users] . iterating using fetchmany To: cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org Message-ID: <CAAybBsATRgajd16rXTbMNzUNkJzEwk6qNieet0hCf1B4_4Hb2A-JsoAwUIsXosN+BqQ9rBEUg@public.gmane.org> Content-Type: text/plain; charset="utf-8" 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+7hSq8jRy4gxJA@public.gmane.org > .net > > > > 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 > -------------- 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 2 ********************************************** ------------------------------------------------------------------------------ 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