pandas read_sql not reading all rows

user_5983171 0
Viewed 2845

I am running the exact same query both through pandas' read_sql and through an external app (DbVisualizer).

DbVisualizer returns 206 rows, while pandas returns 178.

I have tried reading the data from pandas by chucks based on the information provided at How to create a large pandas dataframe from an sql query without running out of memory?, it didn't make a change.

What could be the cause for this and ways to remedy it?

The query:

select *
from rainy_days
where year=’2010’ and day=‘weekend’

The columns contain: date, year, weekday, amount of rain at that day, temperature, geo_location (row per location), wind measurements, amount of rain the day before, etc..

The exact python code (minus connection details) is:

import pandas
from sqlalchemy import create_engine

engine = create_engine(
   'postgresql://user:pass@server.com/weatherhist?port=5439',
)

query = """
        select *
        from rainy_days
        where year=’2010’ and day=‘weekend’
        """
df = pandas.read_sql(query, con=engine)
pandas sqlalchemy
3 Answers
user_5319240

The problem is that pandas returns a packed dataframe (DF). For some reason this is always on by default and the results varies widely as to what is shown. The solution is to use the unpacking operator (*) before/when trying to print the df, like this:

print(*df)

(This is also know as the splat operator for Ruby enthusiasts.)

To read more about this, please check out these references & tutorials:

user_11476880
Related