How to output csv data in mongodb using python

Question

Trying to input csv data in mongodb using python.i'm for beginners for python and mongodb .. i used the following code

import csv import json import pandas as pd import sys, getopt, pprint from pymongo import MongoClient #CSV to JSON Conversion csvfile = open('C://test//final-current.csv', 'r') jsonfile = open('C://test//6.json', 'a') reader = csv.DictReader( csvfile ) header= [ "S.No", "Instrument Name", "Buy Price", "Buy Quantity", "Sell Price", "Sell Quantity", "Last Traded Price", "Total Traded Quantity", "Average Traded Price", "Open Price", "High Price", "Low Price", "Close Price", "V" ,"Time"] #fieldnames=header output=[] for each in reader: row={} for field in header: row[field]=each[field] output.append(row) json.dump(output, jsonfile, indent=None, sort_keys=False , encoding="UTF-8") mongo_client=MongoClient() db=mongo_client.october_mug_talk db.segment.drop() data=pd.read_csv('C://test//6.json', error_bad_lines=0) df = pd.DataFrame(data) records = csv.DictReader(df) db.segment.insert(records) 

but the output is in this format

 /* 0 */ { "_id" : ObjectId("54891c4ffb2a0303b0d43134"), "[{\"AverageTradedPrice\":\"0\"" : "BuyPrice:\"349.75\"" } /* 1 */ { "_id" : ObjectId("54891c4ffb2a0303b0d43135"), "[{\"AverageTradedPrice\":\"0\"" : "BuyQuantity:\"3000\"" } /* 2 */ { "_id" : ObjectId("54891c4ffb2a0303b0d43136"), "[{\"AverageTradedPrice\":\"0\"" : "ClosePrice:\"350\"" } /* 3 */ { "_id" : ObjectId("54891c4ffb2a0303b0d43137"), "[{\"AverageTradedPrice\":\"0\"" : "HighPrice:\"0\"" } 

Actually, I want the output to be pleasant for one id, all other fields should be displayed as subtypes for example:

  _id" : ObjectId("54891c4ffb2a0303b0d43137") AveragetradedPrice :0 HighPrice:0 ClosePrice:350 buyprice:350.75 

Please help me Out.Thanks in advance

Answer 1

Thank you for your suggestion. This is one revised code:

 import csv import json import pandas as pd import sys, getopt, pprint from pymongo import MongoClient #CSV to JSON Conversion csvfile = open('C://test//final-current.csv', 'r') reader = csv.DictReader( csvfile ) mongo_client=MongoClient() db=mongo_client.october_mug_talk db.segment.drop() header= [ "S No", "Instrument Name", "Buy Price", "Buy Quantity", "Sell Price", "Sell Quantity", "Last Traded Price", "Total Traded Quantity", "Average Traded Price", "Open Price", "High Price", "Low Price", "Close Price", "V" ,"Time"] for each in reader: row={} for field in header: row[field]=each[field] db.segment.insert(row) 

Answer 2

The easiest way - using pandas my code

 import json import pymongo import pandas as pd myclient = pymongo.MongoClient() df = pd.read_csv('yourcsv.csv',encoding = 'ISO-8859-1') # loading csv file df.to_json('yourjson.json') # saving to json file jdf = open('yourjson.json').read() # loading the json file data = json.loads(jdf) # reading json file 

Now you can paste this JSON into your Mangodb database: -]

Answer 3

There is a better way with less import if you have a title bar in your CSV.

 from pymongo import MongoClient import csv # DB connectivity client = MongoClient('localhost', 27017) db = client.db collection = db.collection # Function to parse csv to dictionary def csv_to_dict(): reader = csv.DictReader(open(FILEPATH)) result = {} for row in reader: key = row.pop('First_value') result[key] = row return query # Final insert statement db.collection.insert_one(csv_to_dict()) 

Hope that helps

Answer 4

Why do you insert data one at a time? Look at this.

 import pandas as pd from pymongo import MongoClient client = MongoClient(<your_credentials>) database = client['YOUR_DB_NAME'] collection = database['your_collection'] def csv_to_json(filename, header=None): data = pd.read_csv(filename, header=header) return data.to_dict('records') collection.insert_many(csv_to_json('your_file_path')) 

Remember that this can cause the application to crash if the file is too large.