We help IT Professionals succeed at work.

save dataframe to a csv file in python

ocean O
ocean O asked
on
28 Views
Last Modified: 2020-04-21
Hi, I have a question for python.  I had use cursor.fetchmany() to fetch the data. It will fetch multiple times depends on the data number. I want to generate one csv file after all the fetching. How can I do it? Code is below. Thanks

 datafile = pd.DataFrame([])
       
            while True:

                results = cursor.fetchmany(7000)

                if not results:
                    break
               

                col_Names = ['market', 'site_id', 'latitude', 'longitude', 'state', 'zip', 'structure_type']
                data = pd.DataFrame(results)
                data.to_csv(filename, index=False, header=col_Names) //This is the first time fetching
          //how can I append the next fetching data to the csv file?
               

        cursor.close()
Comment
Watch Question

CERTIFIED EXPERT

Commented:
have you heard about the to_csv method?

pandas.DataFrame.to_csv
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html#pandas-dataframe-to-csv

some examples:

Saving a Pandas Dataframe as a CSV
https://www.geeksforgeeks.org/saving-a-pandas-dataframe-as-a-csv/

How to Export Pandas DataFrame to a CSV File
https://datatofish.com/export-dataframe-to-csv/
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Just open the .csv file outside your loop. Write to the .csv file as desired. Close the .csv file when your SQL looping is complete.

Author

Commented:
I can't put all the data in one dataframe. Since the data is too big, like 7241764, if everytime I fetch 5000, it will take over 1400 times to fetch. so I am thinking everytime when fetching 5000, then I will write it to a csv file , then append the second csv file from second time fetching to the previous csv file. But I am not sure how to append the csv file in python.

 if execution_type == _READ:
            while True:

                results = cursor.fetchmany(5000)

                if not results:
                    break
       

                if i == 0:
                   data = pd.DataFrame(results)
                   data.to_csv(filename, index=False, header=col_Names)

                if i >= 1:
                    data.to_csv(filename1, index=False)
                    append = filename.join(filename1) //How to append csv file filename1 to csv file filename???
                i += 1

        cursor.close()
CERTIFIED EXPERT

Commented:
Since the data is too big, like 7241764, if everytime I fetch 5000, it will take over 1400 times to fetch.

what's the database you are using? if we are saying to export 7.2 mil of records, we probably need to have a better approach on this.
System Infrastructure Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ryan:
we are using postgresql
CERTIFIED EXPERT

Commented:
@ocean O

sorry as I didn't use much on postgresql, so can't really provide advice on that

perhaps with such volume of data, some off-the-shelf software + RPA would be a more reliable solution for you?

dbForge Studio for PostgreSQL
https://www.devart.com/dbforge/postgresql/studio/postgresql-migration.html
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.