Not able to read data to csv file using pandas

Question

I have a below data in table need to read entire data in table to pandas after performing cumulative sum and average need to generate csv file [ comma separated file with headers ]

NAME,AGE,MARKS
A1,12,40
B1,13,54
C1,15,67
D1,11,41
E1,16,59
F1,10,60

I tried to write got stuck

import cx_Oracle
import pandas as pd

try : 
    sq='select * from emp'

    conn=cx_Oracle.cpnnect(myconnection)
    fd=pd.read_sql(sql,con=conn)
    fd['CUM_SUM'] = fd['MARKS'].cumsum()
    fd['AVG'] = fd['MARKS'].expanding().mean()
    fd.to_csv('file.csv', index=False)

except Exception as er:

    print(er)

Expected output in csv file with headers

NAME,AGE,MARKS,CUM_SUM,AVG
A1,12,40,40,40
B1,13,54,94,47
C1,15,67,161,53.66
D1,11,41,202,50.5
E1,16,59,261,43.5
F1,10,60,321,45.85

When i do print(fd) , it gives below output

NAME ..CUM,AVG
A1,..,40,40
B1,..,94,47
C1,..,161,53.66
D1,..,202,50.5
E1,..,261,43.5
F1,..,321,45.85

Please, describe what exactly is wrong with your current code. — astentx
– astentx, Commented Oct 12, 2021 at 11:09
@astentx : Do i need to use cusor.fetchall to read entire data — user17086826
– user17086826, Commented Oct 12, 2021 at 11:12
You cannot read data to file. And what the "not able" means? Do you have any errors? Do you have an output that doesn't fit your needs? — astentx
– astentx, Commented Oct 12, 2021 at 11:13
Get rid of that try: except:. You'll never see what the actual error and traceback are. Then, maybe cpnnect should be connect. — AKX
– AKX, Commented Oct 12, 2021 at 11:14

Vyacheslav · Accepted Answer · 2021-10-12 11:30:16Z

1

import sqlalchemy
import psycopg2

conn = 'postgresql+psycopg2://DB_USER:DB_PASS@DB_HOST:5432/DB_NAME'     # 5432 = DB_PORT

# Connect to DB
engine = sqlalchemy.create_engine(conn)
connect = engine.connect()
inspector = sqlalchemy.inspect(engine)
# inspector.get_table_names()

# Read data from table_name and add to data-frame
df = pd.read_sql("select * from table_name", connect)
# df.head(10)

# calculation

# Write to csv with headers and comma delimiter
df.to_csv('filename.csv', sep=',', header=True)

Docs for .to_csv: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_csv.html

answered Oct 12, 2021 at 11:30

Vyacheslav

872 silver badges14 bronze badges

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

2 Comments

user17086826 Over a year ago

It works but how to calculate the cumulative sum and average of MARKS columns that also i want in csv file

Jairus Over a year ago

You will need a group by for any aggregations like so select sum from table_name group by columns.

Collectives™ on Stack Overflow

Not able to read data to csv file using pandas

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related