Re: [cx-oracle-users] . iterating using fetchmany

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

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-...@li... [mailto:cx-...@li...] 
Sent: Tuesday, November 25, 2014 10:50 PM
To: cx-...@li...
Subject: cx-oracle-users Digest, Vol 97, Issue 1

Send cx-oracle-users mailing list submissions to
	cx-...@li...

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-...@li...

You can reach the person managing the list at
	cx-...@li...

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" <dr...@bu...>
Subject: [cx-oracle-users] iterating using fetchmany
To: "cx-...@li..."
	<cx-...@li...>
Message-ID:
	<A4A...@mb...>
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)" <Dav...@st...>
Subject: Re: [cx-oracle-users] iterating using fetchmany
To: "cx-...@li..."
	<cx-...@li...>
Message-ID:
	<D64...@05...>

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:dr...@bu...]
Sent: Tuesday, November 25, 2014 3:23 PM
To: cx-...@li...
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 <ant...@gm...>
Subject: Re: [cx-oracle-users] iterating using fetchmany
To: cx-...@li...
Message-ID:
	<CAE...@ma...>
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) < Dav...@st...> 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:dr...@bu...]
> *Sent:* Tuesday, November 25, 2014 3:23 PM
> *To:* cx-...@li...
> *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-...@li...
> 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-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

End of cx-oracle-users Digest, Vol 97, Issue 1
**********************************************