Fetching huge data from Oracle in Python

Question

I need to fetch huge data from Oracle (using cx_oracle) in python 2.6, and to produce some csv file.

The data size is about 400k record x 200 columns x 100 chars each.

Which is the best way to do that?

Now, using the following code...

ctemp = connection.cursor()
ctemp.execute(sql)
ctemp.arraysize = 256
for row in ctemp:
  file.write(row[1])
  ...

... the script remain hours in the loop and nothing is writed to the file... (is there a way to print a message for every record extracted?)

Note: I don't have any issue with Oracle, and running the query in SqlDeveloper is super fast.

Thank you, gian

"Is there a way to print a message"....yes, add a print statement to your code... — user2665694
– user2665694, Commented Oct 8, 2013 at 9:14

olekb · Accepted Answer · 2017-03-15 20:29:30Z

1

You should use cur.fetchmany() instead. It will fetch chunk of rows defined by arraysise (256)

Python code:

def chunks(cur): # 256
    global log, d
    while True:
        #log.info('Chunk size %s' %  cur.arraysize, extra=d)
        rows=cur.fetchmany()

        if not rows: break;
        yield rows

Then do your processing in a for loop;

for i, chunk  in enumerate(chunks(cur)):
            for row in chunk:
                     #Process you rows here

That is exactly how I do it in my TableHunter for Oracle.

edited Mar 15, 2017 at 20:29

answered Mar 15, 2017 at 19:13

olekb

6581 gold badge12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2665694 · Accepted Answer · 2013-10-08 09:16:51Z

0

add print statements after each line
add a counter to your loop indicating progress after each N rows
look into a module like 'progressbar' for displaying a progress indicator

answered Oct 8, 2013 at 9:16

user2665694

1 Comment

Paco Over a year ago

Have you heard of pdb? Much better than using print

George · Accepted Answer · 2013-10-08 12:04:40Z

0

I think your code is asking the database for the data one row at the time which might explain the slowness.

Try:

ctemp = connection.cursor()
ctemp.execute(sql)
Results = ctemp.fetchall()
for row in Results:
    file.write(row[1])

answered Oct 8, 2013 at 12:04

George

1,0542 gold badges11 silver badges23 bronze badges

1 Comment

Colin 't Hart Over a year ago

Fetch 800 megabytes into memory first, then write to file? Could work, but I'd rather not.

Collectives™ on Stack Overflow

Fetching huge data from Oracle in Python

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related