0

export data from MongoDB to Excel by python , so far I can extract data and print out the data.

I'm stocking on saving data to excel file
(problem: can save as new file, but only save one line of data)

  • the code is below:
import pandas as pd
from pymongo import MongoClient
import pymongo
from json2excel import Json2Excel
import json
from bson.objectid import ObjectId
from bson import json_util

client = pymongo.MongoClient("mongodb://localhost:27017/")
# Database Name
db = client["(practice_10_14)-0002"]
# Collection Name
col = db["(practice_10_24)read_MongoDB_to_Excel"]
#  Find All: It works like Select * query  of SQL. 
x = col.find()
for data in x:
    list_01 = []
    list_01.append(data)
    print(data)


print("= = = = = ")
df = pd.DataFrame(data,index=[0])
 
# select two columns
for y in df:
    print(y)

print("= = = = = ")
print(type(list_01))


df = pd.DataFrame(list_01)
writer = pd.ExcelWriter('test10.24.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='welcome', index=False)
writer.save()
  • here is the whole output in case someone want to take a look:
C:\Users\chuan\OneDrive\Desktop\10.24_excel_to_mongoDB>python mongoDB_to_excel.py
{'_id': ObjectId('6348d73be94317989175dc2d'), 'name': 'Joy', 'ID': 100998, 'Age': 23, 'time': datetime.datetime(2022, 10, 17, 9, 11, 54)}
{'_id': ObjectId('6348d73be94317989175dc2e'), 'name': 'Tom', 'ID': 0, 'Age': 73, 'time': datetime.datetime(2022, 10, 17, 9, 11, 49)}
{'_id': ObjectId('6348d73be94317989175dc2f'), 'name': 'Crise', 'ID': 100768, 'Age': 17, 'time': datetime.datetime(2022, 10, 17, 9, 11, 44)}
{'_id': ObjectId('6348d73be94317989175dc30'), 'name': 'Rose', 'ID': 100566, 'Age': 98, 'time': datetime.datetime(2022, 10, 17, 9, 11, 35)}
{'_id': ObjectId('6348d73be94317989175dc31'), 'name': 'Kim', 'ID': 0, 'Age': 44, 'time': datetime.datetime(2022, 10, 17, 9, 11, 24), 'Update_ID': '99999'}
= = = = =
_id
name
ID
Age
time
Update_ID
= = = = =
<class 'list'>
C:\Users\chuan\OneDrive\Desktop\10.24_excel_to_mongoDB\mongoDB_to_excel.py:52: FutureWarning: save is not part of the public API, usage can give in unexpected results and will be removed in a future version
  writer.save()
j ton
  • 229
  • 9

1 Answers1

1

you put list_01 = [] inside for loop, so this will clear everytime when u insert new data, that why only left the last one

the correct code:

import pandas as pd
from pymongo import MongoClient
import pymongo
from json2excel import Json2Excel
import json
from bson.objectid import ObjectId
from bson import json_util

client = pymongo.MongoClient("mongodb://localhost:27017/")
# Database Name
db = client["(practice_10_14)-0002"]
# Collection Name
col = db["(practice_10_24)read_MongoDB_to_Excel"]
#  Find All: It works like Select * query  of SQL. 
x = col.find()
list_01 = []
for data in x:
    list_01.append(data)
    print(data)


print("= = = = = ")
df = pd.DataFrame(data,index=[0])
 
# select two columns
for y in df:
    print(y)

print("= = = = = ")
print(type(list_01))


print(list_01)
df = pd.DataFrame(list_01)
writer = pd.ExcelWriter('test10.24.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='welcome', index=False)
writer.save()
j ton
  • 229
  • 9