how to Insert JSON objects to MySQL database?

Question

I'm new to python and trying to work out how to insert some JSON into MySQL database in different python code. It works fine when run separately, but not works when i try to connect 2nd to 1st script. How to links 2nd python in 1st python script so it can works together?

I have 1st code like bellow, this code serves to send the image to API and generate a json file data.json

import glob
import argparse
import requests
import json
import time
import os
import cv2
import numpy as np
from pprint import pprint

import json_mysql

def main():
    result = []

    regions = ['id']

    time_to_wait = np.inf
    time_counter = 0

    while True:
        files = glob.glob(os.path.join("./image_dir*/*.jpg"))
        files.sort(key=os.path.getmtime)
        for file in files:
            if os.path.isfile(file):
                with open(file, 'rb') as fp:
                    response = requests.post(
                        'https://API/',
                        data=dict(regions=regions),
                        files=dict(upload=fp),
                        headers={'Authorization': 'Token ' + 'XXXX'})
                    result.append(response.json())
                    with open('data.json', 'w') as outfile:
                        json.dump(result, outfile)
                time.sleep(1)

                pprint(response.json())

                os.remove(file)

        time.sleep(1)
        time_counter += 1
        if time_counter > time_to_wait: break
        print("waiting for file... ")


if __name__ == '__main__':
    main()
    json_mysql.create_db()

it generate json file like this:

And 2nd code is for create and store 'data.json' to MySQL database:

from urllib.request import urlopen
import urllib
import json
import sys
import pymysql

def dbconnect():
    try:
        db = pymysql.connect(
            host="localhost",
            user="root",
            passwd="YYYY",
        )
    except Exception as e:
        sys.exit("Can't connect to Database")
    return db


def create_db():
    db_name="plate_recognizer"
    table_name="vehicles"
    
    db = dbconnect()
    cursor = db.cursor()

    cursor.execute("SET sql_notes = 0;")

    cursor.execute("CREATE DATABASE IF NOT EXISTS {}".format(db_name))

    cursor.execute("SET sql_notes = 0;")

    cursor.execute(
        """CREATE TABLE IF NOT EXISTS {}.{}(time varchar(150),plate varchar(20),region varchar(150), score varchar(20), filename varchar(50), tipe varchar(10));""".format(db_name, table_name))

    cursor.execute("SET sql_notes = 1;")

    with open('data.json') as f:
        data = json.load(f)

    for i in data:
        cursor.execute(
            """INSERT INTO {}.{}(time, plate, region, score, filename, tipe) VALUES(%s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE plate =%s """.format
            (db_name, table_name),
            (i['timestamp'], i['results'][0]['plate'].upper(), i['results'][0]['region']['code'], i['results'][0]['score'], i['filename'], i['results'][0]['vehicle']['type'], i['results'][0]['plate'].upper()))

    db.commit()
    db.close()

if __name__ == '__main__':
    create_db()

Thanks in advance.

"not works" means what? Do you get an error? Can you describe the outcome? — tadman
– tadman, Commented Jul 30, 2020 at 4:30
i mean when i run 1st python, it can't link 2nd python script that will store json data to sql database — scalartensor
– scalartensor, Commented Jul 30, 2020 at 4:43
- @Akina, i'm sorry. i want to run two script together. it can't store json to mysql when i run 1st code. but it works when i just run 2nd python code. — scalartensor
– scalartensor, Commented Jul 30, 2020 at 4:48
Edit your post text and add the question into it explicitly. — Akina
– Akina, Commented Jul 30, 2020 at 4:51

jerrymouse · Accepted Answer · 2020-07-30 05:27:46Z

You should divide your code in several methods. Each method should be responsible to perform a particular task, independent of other methods. For example you can use an insert_data() method to just insert your data.

# creates your db
def create_db():
    db_name="plate_recognizer"
    table_name="vehicles"
    
    db = dbconnect()
    cursor = db.cursor()

    cursor.execute("SET sql_notes = 0;")

    cursor.execute("CREATE DATABASE IF NOT EXISTS {}".format(db_name))

    cursor.execute("SET sql_notes = 0;")

    cursor.execute(
        """CREATE TABLE IF NOT EXISTS {}.{}(time varchar(150),plate varchar(20),region varchar(150), score varchar(20), filename varchar(50), tipe varchar(10));""".format(db_name, table_name))

    cursor.execute("SET sql_notes = 1;")

# pass data to be inserted
def insert_data(cursor, data):
    for i in data:
            cursor.execute(
                """INSERT INTO {}.{}(time, plate, region, score, filename, tipe) VALUES(%s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE plate =%s """.format
                (db_name, table_name),
                (i['timestamp'], i['results'][0]['plate'].upper(), i['results'][0]['region']['code'], i['results'][0]['score'], i['filename'], i['results'][0]['vehicle']['type'], i['results'][0]['plate'].upper()))

        db.commit()
        db.close()

def main():

    db = dbconnect()
    cursor = db.cursor()
    
    # your logic
    while True:
        files = glob.glob(os.path.join("./image_dir*/*.jpg"))
        files.sort(key=os.path.getmtime)
        for file in files:
            if os.path.isfile(file):
                with open(file, 'rb') as fp:
                    response = requests.post(
                        'https://API/',
                        data=dict(regions=regions),
                        files=dict(upload=fp),
                        headers={'Authorization': 'Token ' + 'XXXX'})
                    result.append(response.json())
                    with open('data.json', 'w') as outfile:
                        json.dump(result, outfile)
                time.sleep(1)

                pprint(response.json())

                # insert data in db
                insert_data(cursor, response.json())

                os.remove(file)

        time.sleep(1)
        time_counter += 1
        if time_counter > time_to_wait: break
        print("waiting for file... ")

Collectives™ on Stack Overflow

how to Insert JSON objects to MySQL database?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related