All/Python/local/area/db.py

import json
import pymysql


def read(file: "str") -> "dict":
    with open(file, "r", encoding="utf-8") as fp:
        data = json.load(fp)
        fp.close()
    return data


def insert(db, cursor, data: "list[tuple]"):
    # create sql
    values = []
    for it in data:
        if it[4] is not None:  # village
            values.append(f"('{it[0]}', '{it[1]}', '{it[2]}', null, '{it[4]}')")
        else:  # other
            values.append(f"('{it[0]}', '{it[1]}', '{it[2]}', '{it[3]}', null)")
    values_str = ", ".join(values)
    sql = f"INSERT INTO area_all(id, name, level, child, type) VALUES {values_str};"

    # do insert
    try:
        res = cursor.execute(sql)
        db.commit()
        print("success:", len(data), res)
    except Exception as e:
        print(e)
        print(sql)
        db.rollback()
        exit(-1)


def split(arr: "list", batch: "int") -> "list[list]":
    size = len(arr)
    if size <= batch:
        return [arr]
    step = size // batch
    res = [arr[i * batch: (i + 1) * batch] for i in range(step)]
    if size % batch:
        res.append(arr[step * batch:])
    return res


def main():
    db = pymysql.connect(host="47.96.4.54", port=10052, user="root", password="hm123456789", database="dam_subsidy")
    cursor = db.cursor()
    data = read("db_data-1.json")
    batch = 1024

    for k, ll in data.items():
        steps = split(ll, batch)
        for step in steps:
            insert(db, cursor, step)

    db.close()


if __name__ == "__main__":
    main()