Thread: Iterating through cur and cur.fetchone()

Hi, how do I use cur.fetchone() when iterating through a cur object.

with
conn = psycopg2.connect()
cur = conn.cursor()
SQL = ('select * from something limit 1000')

n = 1
cur.execute(SQL)
for edge_list in cur:
    edge = cur.fetchone()
    print n

    n = n +1

I get
n = 500

when I skip edge = cur.fetchone()

for edge_list in cur:
    print n

    n = n +1

n = 1000

Why does this happen, and what is the solution?


--
Håvard Wahl Kongsgård

2011/10/10 Håvard Wahl Kongsgård <haavard.kongsgaard@gmail.com>:
> Hi, how do I use cur.fetchone() when iterating through a cur object.
>
> with
> conn = psycopg2.connect()
> cur = conn.cursor()
> SQL = ('select * from something limit 1000')
>
> n = 1
> cur.execute(SQL)
> for edge_list in cur:
>    edge = cur.fetchone()
>    print n
>
>    n = n +1
>
> I get
> n = 500
>
> when I skip edge = cur.fetchone()
>
> for edge_list in cur:
>    print n
>
>    n = n +1
>
> n = 1000
>
> Why does this happen, and what is the solution?

You are consuming the cursor both with the iteration and with the
fetchone. You should either use:

    for record in cur:
        # use record

or

    while 1:
        record = cur.fetchone()
        if not record: break
        # use record

Not mix the two together.

-- Daniele

Sorry, I stupid mistake. But I have been using psycopg for a year and
I have some issue with memory usage on large cursors.
Any tips on how to reduce the memory usage.

-Håvard

2011/10/10 Håvard Wahl Kongsgård <haavard.kongsgaard@gmail.com>:
> Hi, how do I use cur.fetchone() when iterating through a cur object.
>
> with
> conn = psycopg2.connect()
> cur = conn.cursor()
> SQL = ('select * from something limit 1000')
>
> n = 1
> cur.execute(SQL)
> for edge_list in cur:
>    edge = cur.fetchone()
>    print n
>
>    n = n +1
>
> I get
> n = 500
>
> when I skip edge = cur.fetchone()
>
> for edge_list in cur:
>    print n
>
>    n = n +1
>
> n = 1000
>
> Why does this happen, and what is the solution?
>
>
> --
> Håvard Wahl Kongsgård
>

2011/10/10 Håvard Wahl Kongsgård <haavard.kongsgaard@gmail.com>:
> Sorry, I stupid mistake. But I have been using psycopg for a year and
> I have some issue with memory usage on large cursors.
> Any tips on how to reduce the memory usage.

You can use server-side cursors, aka named cursors
<http://initd.org/psycopg/docs/usage.html#server-side-cursors>. In the
latest psycopg versions, iteration on the cursor (i.e. the "for record
in cur: ..." syntax) is more efficient than iterating with fetchone.
Everything is explained in the docs.

-- Daniele