Python - Pandas writing blank files to file

[tdunphy]

I have a python script that writes to several file formats via Pandas. It can write to CSV/JSON/HTML/Excel.

I'm pulling the data from MongoDB and into a Pandas DF, then writing the contents to a file.

However for some reason the script is writing blank files. When I open the file this is what I see:

https://pasteboard.co/JJwc1ZK.png

Before printing the file I am printing the dataframe to the screen output so I can validate that the data is there. For example with CSV the output to the screen is this:

1 2 3 4 5 6 7 8 9

CSV data: ,AWS Account,Account Number,Name,Instance ID,AMI ID,Volumes,Private IP,Public IP,Private DNS,Availability Zone,VPC ID,Type,Key Pair Name,State,Launch Date 0,project-client-lab,123456789101,bastion001,i-xxxxxxxxxxxxxxxxxxxx,ami-xxxxxxxxxxxxxxxxxxx,vol-xxxxxxxxxxxxxxx,10.238.2.166,3.214.15.175,ip-10-238-2-166.ec2.internal,us-east-1a,vpc-xxxxxxxxxxxxxxxxx,t3.small,project-client-int01,running,March 10 2020 1,project-client-lab,123456789101,logicmonitor001,i-xxxxxxxxxxxxxxxxxxxx,ami-xxxxxxxxxxxxxxxxxxx,vol-0xxxxxxxxxxxxxx,10.238.2.52,,ip-10-238-2-52.ec2.internal,us-east-1a,vpc-xxxxxxxxxxxxxxxxx,m5.large,project-client-int01,running,September 02 2019 2,project-client-lab,123456789101,project-cassandra001,i-xxxxxxxxxxxxxxxxxxxx,ami-xxxxxxxxxxxxxxxxxxx,"vol-xxxxxxxxxxxxxxxxxx, vol-xxxxxxxxxxxxxxxxx",10.238.2.221,,ip-10-238-2-221.ec2.internal,us-east-1a,vpc-xxxxxxxxxxxxxxxxx,m5.large,project-client-int01,running,January 14 2020 3,project-client-lab,123456789101,project-cassandra003,i-xxxxxxxxxxxxxxxxxxxx,ami-xxxxxxxxxxxxxxxxxxx,"vol-xxxxxxxxxxxxxxxxxx, vol-xxxxxxxxxxxxxxxxx",10.238.2.207,,ip-10-238-2-207.ec2.internal,us-east-1a,vpc-xxxxxxxxxxxxxxxxx,m5.large,project-client-int01,running,January 14 2020 4,project-client-lab,123456789101,project-cassandra003,i-xxxxxxxxxxxxxxxxxxxx,ami-xxxxxxxxxxxxxxxxxxx,"vol-xxxxxxxxxxxxxxxxxx, vol-xxxxxxxxxxxxxxxxx",10.238.2.203,,ip-10-238-2-203.ec2.internal,us-east-1a,vpc-xxxxxxxxxxxxxxxxx,c5.xlarge,project-client-int01,running,January 22 2020 5,project-client-lab,123456789101,project-cassandra001,i-xxxxxxxxxxxxxxxxxxxx,ami-xxxxxxxxxxxxxxxxxxx,"vol-xxxxxxxxxxxxxxxxxx, vol-xxxxxxxxxxxxxxxxx",10.238.2.209,,ip-10-238-2-209.ec2.internal,us-east-1a,vpc-xxxxxxxxxxxxxxxxx,c5.xlarge,project-client-int01,running,January 22 2020 6,project-client-lab,123456789101,haproxy001,i-xxxxxxxxxxxxxxxxx,ami-xxxxxxxxxxxxxxxxxxx,vol-xxxxxxxxxxxxxxxxxx,10.238.2.169,54.242.118.165,ip-10-238-2-169.ec2.internal,us-east-1a,vpc-xxxxxxxxxxxxxxxxx,m5.large,project-client-int01,running,February 20 2020 7,project-client-lab,123456789101,logicmonitor002,i-xxxxxxxxxxxxxxx,ami-xxxxxxxxxxxxxxxxxxx,vol-0c48ff6ebb031008a,10.238.2.69,,ip-10-238-2-69.ec2.internal,us-east-1b,vpc-xxxxxxxxxxxxxxxxx,m5.large,project-client-int01,running,September 13 2019

These are the functions that write to file:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121

def mongo_export_to_file(interactive, aws_account, aws_account_number,instance_col=None,date=None):     create_directories()     if date == None:         format= "%m-%d-%Y"         today = datetime.today()         today = today.strftime(format)         date = today     else:         format= "%m-%d-%Y"         date = datetime.strptime(date,"%m%d%Y")         date = date.strftime(format)     if not instance_col:         _, _, instance_col = set_db()     # make an API call to the MongoDB server     if interactive == 0:         mongo_docs = instance_col.find({})     else:         mongo_docs = instance_col.find({"Account Number": aws_account_number})     # Convert the mongo docs to a DataFrame     docs = pandas.DataFrame(mongo_docs)     # Discard the Mongo ID for the documents     docs.pop("_id")     if __name__ == "__main__":         print("Choose a file format")         print("1. CSV")         print("2. JSON")         print("3. HTML")         print("4. Excel")         choice = input("Enter a number 1-4: ")         choice = int(choice)     else:         choice = 1     if choice == 1:         if __name__ == "__main__":             # export MongoDB documents to CSV             csv_export = docs.to_csv(sep=",") # CSV delimited by commas             print ("\nCSV data:", csv_export)         # Set the CSV output directory         output_dir = os.path.join("..", "..", "output_files", "aws_instance_list", "csv", "")         if interactive == 1:             output_file = os.path.join(output_dir, "aws-instance-list-" + aws_account + "-" + date +".csv")         else:             output_file = os.path.join(output_dir, "aws-instance-master-list-" + date +".csv")           # export MongoDB documents to a CSV file, leaving out the row "labels" (row numbers)         docs.to_csv(output_file, ",", index=False) # CSV delimited by commas     elif choice == 2:         if __name__ == "__main__":             json_export = docs.to_json() # return JSON data             print ("\nJSON data:", json_export)         # Set the JSON output directory         output_dir = os.path.join("..", "..", "output_files", "aws_instance_list", "json", "")         if interactive == 1:             output_file = os.path.join(output_dir, "aws-instance-list-" + aws_account + "-" + date +".json")         else:             output_file = os.path.join(output_dir, "aws-instance-master-list-" + date +".json")         # export MongoDB documents to a CSV file, leaving out the row "labels" (row numbers)         docs.to_json(output_file)     elif choice == 3:         html_str = io.StringIO()         # export as HTML         docs.to_html(         buf=html_str,         classes="table table-striped"         )         if __name__ == "__main__":             # print out the HTML table             print (html_str.getvalue())         # Set the HTML output directory         output_dir = os.path.join("..", "..", "output_files", "aws_instance_list", "html", "")         if interactive == 1:             output_file = os.path.join(output_dir, "aws-instance-list-" + aws_account + "-" + date +".html")         else:             output_file = os.path.join(output_dir, "aws-instance-master-list-" + date + ".html")         # save the MongoDB documents as an HTML table         docs.to_html(output_file)     elif choice == 4:         # Set the Excel output directory         output_dir = os.path.join("..", "..", "output_files", "aws_instance_list", "excel", "")         time.sleep(5)         if interactive == 1:             output_file = os.path.join(output_dir, "aws-instance-list-" + aws_account + "-" + date + ".xlsx")         else:             output_file = os.path.join(output_dir, "aws-instance-master-list-" + date + ".xlsx")         # export MongoDB documents to a Excel file, leaving out the row "labels" (row numbers)         writer = ExcelWriter(output_file)         docs.to_excel(writer,"EC2 List",index=False)         writer.save()         writer.close()     if __name__ == "__main__":         exit = input("Exit program (y/n): ")         if exit.lower() == "y" or exit.lower() == "yes":             exit_program()         else:             main()     def print_reports(interactive,aws_account,aws_account_number):     set_db(instance_col=None)     inputDate = input("Enter the date in format 'dd/mm/yyyy': ")     day,month,year = inputDate.split('/')     isValidDate = True     try:         datetime(int(year),int(month),int(day))     except ValueError :         isValidDate = False         print_reports(interactive,aws_account,aws_account_number)       if(isValidDate) :         print(f"Input date is valid: {inputDate}")         format= "%m%d%Y"         inputDate = datetime.strptime(inputDate,"%m/%d/%Y")         inputDate = inputDate.strftime(format)     else:         print(f"Input date is not valid: {inputDate}")         print_reports(interactive,aws_account,aws_account_number)     myclient = connect_db()     mydb = myclient["aws_inventories"]     instance_col = "ec2_list_" + inputDate     instance_col = mydb[instance_col]     mongo_export_to_file(interactive, aws_account, aws_account_number,instance_col,date=inputDate)

This is all my code in this script.

Why is this happening and how to I correct that?