99初始版

import os
import json
import pandas as pd
import zipfile
import sqlite3
from sqlalchemy import create_engine


# col_name_file：列名文件，data_dir：json文件目录路径
def jsontodb(col_name_file, data_dir):
    # col_names 列表类型：用于从列名文件中读取列名，并存放在col_names列表中

    col_names = []
    with open(col_name_file, 'r') as f:
        # Read the content of the file line by line
        for line in f.readlines():
            # Split the line into words
            col_names.append(line.strip("\n"))
    # 如果想增加目录列，需要在col_names中添加列名
    print(col_names)

    row = []
    target_file = []

    for jFile in os.listdir(data_dir):
        if jFile.endswith('.json'):
            with open(os.path.join(data_dir, jFile), "r", encoding="utf-8") as file:
                jsonData = json.load(file)
                i = 0
                # 遍历json文件中的每一行
                for item in jsonData:
                    '''
                    ============================下方是您的逻辑代码======================================
                    '''
                    i += 1
                    defectName_1 = item['defectName-1']
                    defectName_2 = item['defectName-2']
                    defectName_3 = item['defectName-3']

                    if "多锡" in defectName_1 or "多锡" in defectName_2 or "多锡" in defectName_3:
                        target_file.append([
                            jFile, i, defectName_1, defectName_2, defectName_3,
                            item['problemCause-1'], item['improvementMeasures-1'],
                            item['problemCause-2'], item['improvementMeasures-2'],
                            item['problemCause-3'], item['improvementMeasures-3'],
                            item['problemCause-4'], item['improvementMeasures-4'],
                            item['problemCause-5'], item['improvementMeasures-5']
                        ])

                    '''
                    ==============================上方是您的逻辑代码============================================
                    '''
                    # 将json中的每一行添加到pandas的每一行
                    row_result = {}
                    for col_name in col_names:
                        # 在每条json数据中获取列名对应的值
                        row_result[col_name] = item.get(col_name, "")
                        # 如果想增加目录列，记得在这个地方存放目录的值
                    print(row_result)
                    # 最后的row的类型是列表中放字典类型，例如：[{},{},{},...]，也是结果存放的地方
                    row.append(row_result)
                    print("=================")
    # row的类型是列表中放字典类型
    data = pd.DataFrame(row)

    # 把 pandas Dataframe 形态的数据一次性导入 SQLite 数据库中，这里我们需要借助 sqlalchemy 工具库（可以通过pip install sqlalchemy轻松安装）
    engine = create_engine('sqlite:///test.db', echo=False)
    # 存储到数据库
    data.to_sql("population", con=engine, if_exists='replace')
    # 查询
    # engine.execute("SELECT * FROM population").fetchall()
    conn = sqlite3.connect('test.db')
    cs = conn.cursor()
    result = cs.execute("SELECT * FROM population")
    print(result.fetchall())
    conn.close()


def extract_all_zip(zip_dir):
    for root, dirs, files in os.walk(zip_dir):
        for filename in files:
            if filename.endswith('.json'):
                print(os.path.join(root, filename))
                return root
            if filename.endswith('.zip'):
                print(os.path.join(root, filename))



if __name__ == "__main__":
    data_dir = extract_all_zip("C:\\Users\\Hasee\\PycharmProjects\\pythonProject7\\defect\\defectfile")
    col_name_file = "C:\\Users\\Hasee\\PycharmProjects\\pythonProject7\\222.txt"
    jsontodb(col_name_file, data_dir)