改良版999999999

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

# col_name_file：列名文件，json_file_path：json文件路径
def jsontodb(col_name_file, json_file_path):
    col_names = []
    with open(col_name_file, 'r') as f:
        for line in f.readlines():
            col_names.append(line.strip("\n"))
    print(col_names)

    row = []
    target_file = []

    # 直接处理单个json文件
    if json_file_path.endswith('.json'):
        with open(json_file_path, "r", encoding="utf-8") as file:
            jsonData = json.load(file)
            i = 0
            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([
                        json_file_path, 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']
                    ])

                row_result = {}
                for col_name in col_names:
                    row_result[col_name] = item.get(col_name, "")
                row.append(row_result)

    data = pd.DataFrame(row)
    engine = create_engine('sqlite:///test4.db', echo=False)
    data.to_sql("population", con=engine, if_exists='append')

    conn = sqlite3.connect('test4.db')
    cs = conn.cursor()
    result = cs.execute("SELECT * FROM population")
    print(result.fetchall())
    conn.close()

# Function to extract all zip files and return a list of JSON file paths
def extract_all_zip(zip_dir):
    json_files = []
    for root, dirs, files in os.walk(zip_dir):
        for filename in files:
            if filename.endswith('.zip'):
                zip_file_path = os.path.join(root, filename)
                print(f"Unzipping: {zip_file_path}")
                with zipfile.ZipFile(zip_file_path, 'r') as zip_ref:
                    zip_ref.extractall(root)

    for root, dirs, files in os.walk(zip_dir):
        for filename in files:
            if filename.endswith('.json'):
                json_files.append(os.path.join(root, filename))
    return json_files

if __name__ == "__main__":
    # Replace these with your actual directory and column file paths
    zip_dir = "C:\\Users\\Hasee\\PycharmProjects\\pythonProject7\\defect"
    col_name_file = "C:\\Users\\Hasee\\PycharmProjects\\pythonProject7\\222.txt"
    json_files = extract_all_zip(zip_dir)  # Unzip files and get JSON file paths
    for json_file in json_files:
        jsontodb(col_name_file, json_file)  # Process each JSON file