Load json to mysql newbie


#1

Dear all
I am newbie in Python, i have already try to surf about the solution for this, but not yet got the answer.

I have a json file it is dekrip.json.
The content of that file is as follows

{"kode_booking":"CC-CD2V4X","nik":"9109014802120001","nama_pemohon":"anindya keisha","tgl_datang":"Jan 29, 2018","waktu_datang":"14:01-15:00","kantor_imigrasi":"Kantor Imigrasi Malang","alamat":"Jl. Raya Panjisuroso No.4, Polowijen, Blimbing, Kota Malang, Jawa Timur 65126"}

I would like to load that file and insert the value to mysql database.
Could anyone help me to do this.

Thanks for any kind help.
Best Regards
Mchoud


#2

What have you done so far? We can help you, but in the end, you will have to write most of the code to make the program work


#3
import os
import json

# read JSON file which is in the next parent folder
file = 'C://dekrip.json'
json_data= open(file).read()
json_obj = json.loads(json_data)


# do validation and checks before insert
def validate_string(val):
   if val != None:
        if type(val) is int:
            #for x in val:
            #   print(x)
            return str(val).encode('utf-8')
        else:
            return val


# connect to MySQL
con = pymysql.connect(host = 'localhost',user = 'root',passwd = '',db = 'tahfidz',port ='3306')
cursor = con.cursor()


# parse json data to SQL insert
for i, item in enumerate(json_obj):
    kode_booking = validate_string(item.get("kode_booking", None))
    tgl_datang = validate_string(item.get("tgl_datang", None))
    nik = validate_string(item.get("nik", None))

    cursor.execute("INSERT INTO qrcode (kode_booking,  tgl_datang,   nik) VALUES (%s,    %s, %s)", (kode_booking,  tgl_datang,   nik))
con.commit()
con.close()

That is my code, when i tried to execute there is an error msg
raise valueError("no Json object could be decoded’)


#4

i put your code here:

https://repl.it/@stetim94/InsubstantialUglyBlackpanther

which seems fine. I needed to make some changes (file location), but i get a very different error

where do you get a value error? What line?


#5

Hi Stetim
I have already put the code at your given url.
There are 4 error related with init.py line 291 and some


#6

line 291?

uhm… if you edit the repl, you make a copy, my original repl doesn’t get updated, so what you did, i can’t see. You would need to share your repl


#7

I am sorry, so what I have to do to reply, in order to be able to read.


#8

by repl i don’t mean reply, the website on which i put your code is called repl, so i call them repl or bin. You will need to make your own bin so i can replicate the problem


#9

I am sorry, I am absolutely newbie in Python and my English is not good enough so it is hard to explain the real problem.
After surfing some article I have found the solution with php, it is quite simple. May be Python will much easier to solve but, there is an obstacle with my Python knowledge.

Thanks for your help Stetim.

And the following is my php solution script

<?php /* create a connection */ $mysqli = new mysqli("localhost", "root", null, "tahfidz"); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } /* let's say we're grabbing this from an HTTP GET or HTTP POST variable called jsonGiven... */ //$jsonString = $_REQUEST['jsonGiven']; /* but for the sake of an example let's just set the string here */ //$jsonString = '{"name":"jack","school":"colorado state","city":"NJ","id":null}'; $jsonString = file_get_contents('telo.json'); /* use json_decode to create an array from json */ $jsonArray = json_decode($jsonString, true); /* create a prepared statement */ if ($stmt = $mysqli->prepare('INSERT INTO test131 (name, school, city, id) VALUES (?,?,?,?)')) { /* bind parameters for markers */ $stmt->bind_param("ssss", $jsonArray['name'], $jsonArray['school'], $jsonArray['city'], $jsonArray['id']); /* execute query */ $stmt->execute(); /* close statement */ $stmt->close(); } /* close connection */ $mysqli->close(); ?>