Python Forum
Load data from json into MySQL
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Load data from json into MySQL
#1
Hi gurus,

I need to take data from JSON API rest and put it into MySQL database table. I try this with for loop through json list which contains dictionaries for data for transaction but I want it to do width map and lambda function. Loop take some time and this job will run every minutes and I need to take data and load quickly. also I want to put this in asynchronise mode and run the same process for different api rest in the same time. Because that I want to put this logic in map and lambda sytnax, not through for loop syntax.

import mysql.connector as mysql

json_list_of_transactions = [{'amount': '0.05900000', 'date': '1521806276', 'tid': '59790535', 'type': '0', 'price': '8509.96'}, {'amount': '0.04400000', 'date': '1521806270', 'tid': '59790528', 'type': '1', 'price': '8512.08'}, {'amount': '0.03730108', 'date': '1521806268', 'tid': '59790527', 'type': '1', 'price': '8514.21'}]


db = mysql.connect(user='root', password='mysql#1234', host = 'localhost', database='DB_DEV')

cursor = db.cursor()

cursor.execute("TRUNCATE TABLE BTCUSD_TXN_H;")
db.commit()

transaction_sql = (
    "insert into BTCUSD_TXN_H"
    "(DATE_TIMESTAMP, DATE_ID, TID, TYPE, VOLUMEN, PRICE)"
    "values (%(date)s, from_unixtime(%(date)s), %(tid)s, %(type)s, %(amount)s, %(price)s)")

for transaction in json_list_of_transactions:
   cursor.execute(transaction_sql, transaction)

#this for loop I want change and try with lambda and map expression like this 
## (it works without errors but not load data. Why? What is wrong?
#map(lambda transaction: cursor.execute(transaction_sql, transaction), json_list_of_transactions )

db.commit()

cursor.close()
db.close()
Reply
#2
(Mar-23-2018, 12:13 PM)chevanton1988 Wrote:
#this for loop I want change and try with lambda and map expression like this 
## (it works without errors but not load data. Why? What is wrong?
#map(lambda transaction: cursor.execute(transaction_sql, transaction), json_list_of_transactions )

map() creates a map-object, which doesn't actually do much of anything until you iterate over it. Check it:
>>> def call_me(param):
...   print("I was called with: {0}".format(param))
...
>>> call_me(42)
I was called with: 42
>>> map(call_me, range(5))
<map object at 0x0374B290>
>>> list(map(call_me, range(5)))
I was called with: 0
I was called with: 1
I was called with: 2
I was called with: 3
I was called with: 4
[None, None, None, None, None]
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  encrypt data in json file help jacksfrustration 1 70 Yesterday, 05:16 PM
Last Post: deanhystad
  Load data in Oracle muzokh 0 191 Mar-08-2024, 11:19 PM
Last Post: muzokh
  JSON Dump and JSON Load foxholenoob 8 982 Oct-12-2023, 07:21 AM
Last Post: foxholenoob
  Mysql and mysql.connector error lostintime 2 612 Oct-03-2023, 10:25 PM
Last Post: lostintime
  Using pyodbc&pandas to load a Table data to df tester_V 3 748 Sep-09-2023, 08:55 PM
Last Post: tester_V
  Read nested data from JSON - Getting an error marlonbown 5 1,310 Nov-23-2022, 03:51 PM
Last Post: snippsat
  Reading Data from JSON tpolim008 2 1,032 Sep-27-2022, 06:34 PM
Last Post: Larz60+
  Load multiple Jason data in one Data Frame vijays3 6 1,500 Aug-12-2022, 05:17 PM
Last Post: vijays3
  Convert nested sample json api data into csv in python shantanu97 3 2,728 May-21-2022, 01:30 PM
Last Post: deanhystad
  Struggling with Juggling JSON Data SamWatt 7 1,823 May-09-2022, 02:49 AM
Last Post: snippsat

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020