Scan all json files in folder and then update or insert into DB…

Usage

Update or Insert
python3 sm.py
Delete
python3 sm.py d TagID
Query
python3 sm.py q

Config.ini
[JSON_FILES_PATH]
Json_Files_Path = ./json_files/
[DB_CONFIG]
DB_Ip = 10.62.8.10
DB_Usr = root
DB_Pws = 1234
DB_Name = dbName
DB_Table = tableName

/json_files/xx.json
{
“t1”: [
{“TagID”: “th0”, “DevID”: “DevID0”, “ModelIdx”: “0”, “ModelType”: “device_name_0”},
{“TagID”: “th1”, “DevID”: “DevID1”, “ModelIdx”: “1”, “ModelType”: “device_name_1”}
],
“t2”: [
{“TagID”: “th2”, “DevID”: “DevID2”, “ModelIdx”: “2”, “ModelType”: “device_name_2”}
]
}

DB struct
dbName
tableName
TagID varchar(32) Primary Key
DevID varchar(32) Primary Key
ModelIdx int(3)
ModelType varchar(30)
ModelOrder tinyint(3)

sm.py

# -*- coding: utf-8 -*-
import MySQLdb as mdb
import json
import os
import sys
import configparser

SENSORS_MAPPING_PATH = "./json_files/"
DB_IP = '10.62.8.10'
DB_USR = 'root'
DB_PWS = '1234'
DB_NAME = 'dbName'
DB_TABLE = 'tableName'

def CheckDBPrimaryKey():
    con = mdb.connect(DB_IP, DB_USR, DB_PWS, DB_NAME);
    with con:
        cur = con.cursor() 
        cur.execute("""
        SELECT COUNT(1)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = '%s'
        AND TABLE_NAME = '%s'
        AND COLUMN_KEY = 'PRI';"""
        % (DB_NAME, DB_TABLE) )
        con.commit()
        if cur.fetchone()[0]:
            return True
    return False

def UpdateDB(tagID, devID, modelIdx, modelType):    
    con = mdb.connect(DB_IP, DB_USR, DB_PWS, DB_NAME);
    with con:
        cur = con.cursor()  
        cur.execute("""
        INSERT INTO %s (TagID, DevID, ModelIdx, ModelType) VALUES ('%s','%s','%s','%s')                                  
        ON DUPLICATE KEY UPDATE 
        ModelIdx = VALUES(ModelIdx), ModelType = VALUES(ModelType);"""  
        % (DB_TABLE, tagID, devID, modelIdx, modelType) )        
        con.commit()

def ParserFiles():
    files = os.listdir(SENSORS_MAPPING_PATH)
    files_json = [i for i in files if i.endswith('.json')]
    for fj in files_json:
        with open(os.path.join(SENSORS_MAPPING_PATH, fj) , 'r') as jsonData:
            d = json.load(jsonData)         
            for v in d.values():          
                for c in range(len(v)):
                    try:
                        if v[c]['TagID']!="" and v[c]['DevID']!="": 
                            UpdateDB(v[c]['TagID'],v[c]['DevID'],v[c]['ModelIdx'],v[c]['ModelType']) 
                        else:  
                            print('TagID or DevID is None')     
                    except KeyError:
                        print('I got a KeyError')
                        pass
                    except:
                        print('I got a Error') 

def DeleteDB(tagID, devID=None):    
    con = mdb.connect(DB_IP, DB_USR, DB_PWS, DB_NAME);
    with con:
        cur = con.cursor()   
        cur.execute("DELETE FROM  %s WHERE tagID = '%s'" % (DB_TABLE,tagID))
        con.commit() 
        print('Delete %s Finished !!!'%(tagID))

def QueryDB():  
    con = mdb.connect(DB_IP, DB_USR, DB_PWS, DB_NAME);
    with con:
        cur = con.cursor()  
        cur.execute("SELECT * FROM  %s" % (DB_TABLE))
        rows = cur.fetchall()
        for row in rows:
            print(row)

def Main(): 
    global SENSORS_MAPPING_PATH  
    global DB_IP  
    global DB_USR
    global DB_PWS
    global DB_NAME
    global DB_TABLE    
    config = configparser.ConfigParser()
    config.read('Config.ini')
    try:
        SENSORS_MAPPING_PATH = config.get('JSON_FILES_PATH', 'Json_Files_Path') 
        DB_IP = config.get('DB_CONFIG', 'DB_Ip')  
        DB_USR = config.get('DB_CONFIG', 'DB_Usr')
        DB_PWS = config.get('DB_CONFIG', 'DB_Pws')
        DB_NAME = config.get('DB_CONFIG', 'DB_Name')
        DB_TABLE = config.get('DB_CONFIG', 'DB_Table')         
        if len(sys.argv) < 2:
            if CheckDBPrimaryKey():
                ParserFiles()   
            else:
                print('ERROR!!! DB Primary Key NOT FOUND~~')
        else:
            if sys.argv[1] == 'd':         
                DeleteDB(sys.argv[2])           
            if sys.argv[1] == 'q':
                QueryDB()
    except:
        print('ERROR INI!!!' )

if __name__ == '__main__':
    Main()