Not able to read data to csv file using pandas-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Seems like you are struggling to create csv file from table result

Where in your table as only 3 columns , so specify the column names in your query instead of '*'.

And the CUM and AVG you are getting it from the query result based on MARKS column dynamically using pandas functions and that you wanted in csv file , if that's the requirement you are looking for then below code will work for you

If throws any error let me know

May be this code will work for you

# Importing modules 
import cx_Oracle
import pandas as pd

#Connection and Query taken into variable 
connec='Username/Password@Hostname:Port/Servicename'
yourQuery='select NAME,AGE,MARKS from emp'

try:
    
    your_connection=cx_Oracle.connect(connec)
    fpd=pd.read_sql(yourQuery,your_connection)
    
    #cumsum : calculate the sum of table marks 
    fpd['CUMULATIVE']=fpd['MARKS'].cumsum()
    
    #expanding().mean() : get you average 
    fpd['AVG']=fpd['MARKS'].expanding().mean()
    
    #Writing output to csv file 
    fpd.to_csv('/path/emp.csv',index=False)

except Exception as er:
    
    print(er)

Note : If their is no data in table the csv file will be created with only HEADER as a record