Re: . iterating using fetchmany

"Rodman, Daniel" <drodman-LjRBWs/[email protected]>
Newsgroups gmane.comp.python.db.cx-oracle
Message-ID <A4A1C995458F8B48B86E57568EF4AE8DB68037@mb-ls3.itorg.ad.buffalo.edu> 
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
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.