pythonmysqlwrds

Save WRDS SQL query to file in python

My code is:

import wrds
db = wrds.Connection() 
compustatq = db.raw_sql("""
              select *
              from comp.fundq 
              """)

compustatq.to_csv('path.csv',index=False)

The problem is that the dataset is so large that I run out of RAM memory. I was therefore thinking if it is possible to download/save the data directly on the disk? Hence, avoiding the 2 step procedure of downloading into a dataframe and then exporting to a csv file.

Solution

  • you can use csv library to do it. it's a python built-in library. I don't known what type is your compustatq. but find your way to read it by each row.

    use it like this:

    import csv

writer = csv.writer(open('path.csv', 'wb+'), dialect='excel')
for row in compustatq:
    writer.writerow([str(v) for v in row])

    ref: csv

    I just read the source code of wrds.Connection.raw_sql. it tells your compustatq is an instance of pandas.DataFrame. So you can still use the method to_csv but in a slightly different way:

    f = open('path.csv', 'wb+')
for i in xrange(len(compustatq)):
    f.write(df[i:i+1].to_csv(header=i==0))
f.close()