Python pandas read_sql returns generator object

Question

I am pulling data from Oracle db using pyodbc and pandas read_sql.

I see no errors when I enter this line

df = pd.read_sql(sql_str,cnxn,chunksize=10)

But when I try to see

df

I get this error

<generator object _query_iterator at 0x092D40F8>

My search as to what this error means or what could be causing it yielded no satisfactory answers.

The reason for using chunksize is that I have a Oracle db table with 60 million rows, and plan to download in bits and then put them together, just like here: How to create a large pandas dataframe from an sql query without running out of memory?

To be specific, what you see is not an 'error', it says the the `df` object is a generator object. — joris, Jul 01 '15 at 20:00

joris · Accepted Answer · 2015-07-01T20:02:57.313

13

As the explanation of chunksize says, when specified, it returns an iterator where chunksize is the number of rows to include in each chunk.
So you can iterate through the result and do something with each chunk:

for chunk in pd.read_sql_query(sql_str, engine, chunksize=10):
    do_something_with(chunk)

Typically you can process the chunk and add it to a list, and then after this for loop concat all processed chunks in this list together.

Also see the docs on sql querying: http://pandas.pydata.org/pandas-docs/stable/io.html#querying for an example.

edited Jul 01 '15 at 20:02

answered Jul 01 '15 at 19:56

joris

133,120
36
247
202

How to add all of them together in one dataframe? – Murtaza Haji Jun 09 '20 at 03:26
5

You can use `pd.concat(list(chunks))` for that. – joris Jun 09 '20 at 13:54

score 1 · Answer 2 · answered Jul 01 '15 at 19:37

1

I would prefer to comment but cannot yet. Regardless, that is not an error that is telling you that df is a generator object.

answered Jul 01 '15 at 19:37

gffbss

1,621
1
17
19

Thanks. Do you mean that the error is uninformative? – DavidH Jul 01 '15 at 19:52
As the other answers indicate you can loop over the generator object to get specific data points. – gffbss Jul 01 '15 at 20:11

Python pandas read_sql returns generator object

2 Answers2