I have a case study where I need to take data from a REST API do some analysis on the data using an aggregate function, joins etc and use the response data in JSON format to plot some retail graphs.
Approaches being followed till now:
Read the data from JSON store these in python variable and use insert to hit the SQL query. Obviously, it is a costly operation because for every JSON line read it is inserting into the database. For 33k rows it is taking more than 20 mins which is inefficient.
This can be handled in elastic search for faster processing but complex operation like joins are not present in elastic search.
If anybody can suggest what would be the best approach (like preprocessing or post-processing in python) to follow for handling such scenarios it would be helpful.
def store_data(AccountNo) db=MySQLdb.connect(host=HOST, user=USER, passwd=PASSWD, db=DATABASE, charset="utf8") cursor = db.cursor() insert_query = "INSERT INTO cstore (AccountNo) VALUES (%s)" cursor.execute(insert_query, (AccountNo)) db.commit() cursor.close() db.close() return def on_data(file_path): #This is the meat of the script...it connects to your mongoDB and stores the tweet try: # Decode the JSON from Twitter testFile = open(file_path) datajson = json.load(testFile) #print (len(datajson)) #grab the wanted data from the Tweet for i in range(len(datajson)): for cosponsor in datajson[i]: AccountNo=cosponsor['AccountNo'] store_data( AccountNo)