Python3测试mysql插入数据代码（chatgpt生成）

实现的功能：先连接mysql数据库，然后读取某个目录所有以txt文件命名后缀的json内容文件，解析出对应的key和value，然后插入数据到mysql数据库，最后关闭数据库连接

import os
import json
import pymysql
import re

"""
尝试插入json文件到 MySQL 数据库。
dbInfo: MySQL数据库信息
table_name:  MySQL数据中表名
table_items_obj:  MySQL数据中表头各项名称
json_directory_path: 本地的json文件路径
"""
dbInfo = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '887910',
    'db': 'testpython'
}

table_name = 'products'

table_items_obj = ('ProductKey', 'AppKey', 'AndroidKey', 'VersionKey', 'VersionValue', 'FileName', 'Filedate')
json_directory_path = os.getcwd()


def connect_to_database(host, port, user, password, database):
    """
    尝试连接到 MySQL 数据库并返回连接对象和状态。

    :param host: 数据库主机地址
    :param port: 数据库端口号
    :param user: 数据库用户名
    :param password: 数据库密码
    :param database: 要连接的数据库名
    :return: (connection) - 连接对象
    """
    connection = None
    status_message = "数据库尝试连接...\n"

    try:
        # 尝试连接到数据库
        connection = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database
        )
        status_message = status_message+"数据库连接成功\n"

        # 检查连接状态
        if connection.open:
            status_message = status_message+"检查数据库状态：已连接\n"
        else:
            status_message = status_message+"检查数据库状态：未连接\n"

    except Error as e:
        # 捕获连接错误
        status_message = status_message+f"连接数据库错误: {e}"+'\n'
    print(status_message+'\n')
    return connection


def close_database_connection(connection):
    """
    关闭数据库连接。

    :param connection: 数据库连接对象
    """
    if connection and connection.open:
        connection.close()
        print("数据库连接已关闭")
    else:
        print("数据库连接已关闭或数据库对象无效")

def insert_one_data_to_database(connection,table_name,table_items,table_values):

    """
    插入一条数据到数据库

    :param connection: 数据库连接对象
    :param table_name: 数据库表名
    :param table_values:表头项名称
    :param table_values:表数据各项的值
    """

    if not(connection and connection.open):
        print("数据库连接已关闭或数据库对象无效")
        return None

    print('table_name:',table_name)
    print('table_items:', table_items)
    print('table_values:', table_values)

    """
    #table_items = ('productID', 'productName', 'price', 'nums', 'date')
    #sql_table_items='(productID, productName, price, nums, date)'
    """
    sql_table_items = f"({', '.join(table_items)})"

    """
    #table_values = ('00004', '桔子', 9.88, 10, '2024-08-18 11:03:02)
    #sql_table_values='(%s, %s, %s, %s, %s)'
    """
    # 生成占位符
    sql_table_values="("
    for index, value in enumerate(table_values):
        if index == len(table_values) - 1:
            sql_table_values += '%s)'
        else:
            sql_table_values += '%s,'

    """
    table_name=sales
    sql_table_items='(productID, productName, price, nums, date)'
    sql_insert_cmd=
        INSERT INTO sales  (productID, productName, price, nums, date) 
        VALUES (%s, %s, %s, %s, %s)
    """
    sql_insert_cmd = "INSERT INTO "+table_name+" "+sql_table_items+"\n"+"VALUES "+sql_table_values+"\n"

    print('sql insert cmd:', sql_insert_cmd)
    try:
        with connection.cursor() as cursor:
            # 执行插入操作
            cursor.execute(sql_insert_cmd, table_values)
            # 提交事务
            connection.commit()
            print("sql插入数据命令执行，数据插入成功")
    except Error as e:
        print(f"sql插入数据命令执行,插入数据错误: {e}")

def get_json_data(data, indent=0,infileName=None,connection=None):
    """
    递归遍历 JSON 数据并打印每个键值对。
    :param data: JSON 数据（字典或列表）。
    :param indent: 当前层级的缩进级别。
    """

    # 示例字符串
    filename = "current_version2024_01_02.txt"

    # 使用正则表达式提取时间信息
    new_date_string = ''
    match = re.search(r'(\d{4})_(\d{2})_(\d{2})', filename)
    if match:
        year, month, day = match.groups()
        # 重新组成时间字符串，例如 "2024-01-02"
        new_date_string = f"{year}-{month}-{day}"
        print(new_date_string)
    else:
        print("No date found")

    values = []
    for category, stores in data.items():
        for store, platforms in stores.items():
            for platform, details in platforms.items():
                for key, value in details.items():
                    values.append((category, store, platform, key, value,infileName,new_date_string))
    print(values)
    for value in values:
        insert_one_data_to_database(connection,table_name,table_items_obj, value)
    return values


def read_json_files(directory,connection):
    """
    读取目录下所有 JSON 文件并打印其内容
    :param directory: 目录路径
    """
    for filename in os.listdir(directory):
        if filename.endswith('.txt'):
            print(f"\n匹配.txt文件成功===========Reading file:============= {filename}")
            file_path = os.path.join(directory, filename)
            with open(file_path, 'r', encoding='utf-8') as file:
                try:
                    data = json.load(file)
                    print(f"\n===========Reading file:============= {filename}")
                    in_indent = 0
                    get_json_data(data, in_indent, filename, connection)
                except json.JSONDecodeError as e:
                    print(f"Error decoding JSON from file {filename}: {e}")
        else:
            print(f"\n非.txt文件，跳过处理===========Ignore file:============= {filename}")





if __name__ == "__main__":
    connect_obj=connect_to_database(dbInfo['host'],dbInfo['port'],dbInfo['user'],dbInfo['password'],dbInfo['db'])
    # items_obj = ('productID', 'productName', 'price', 'nums', 'date')
    # values_obj = ('000010', '桔子', 9.88, 10, '2024-08-18 11:03:02')
    # insert_one_data_to_database(connect_obj,'sales',items_obj,values_obj)
    # 指定你的目录路径

    print(f"\n处理从文件插入数据的目录:",json_directory_path)
    read_json_files(json_directory_path,connect_obj)
    # 调用函数读取和处理 JSON 文件
    # read_and_process_json(r'.\current_version2024_01_01.txt')


    close_database_connection(connect_obj)

测试的JSON文件：

{
  "移动云盘": {
    "华为应用市场": {
      "Android": {
        "version": "V10.4.0"
      }
    },
    "小米应用商店": {
      "Android": {
        "version": "V10.4.0"
      }
    },
    "OPPO软件商店": {
      "Android": {
        "version": "V10.4.0"
      }
    },
    "vivo应用商店": {
      "Android": {
        "version": "V10.4.0"
      }
    },
    "魅族应用商店": {
      "Android": {
        "version": "V10.4.0"
      }
    },
    "三星应用商店": {
      "Android": {
        "version": "V10.3.4"
      }
    },
    "联想乐商店": {
      "Android": {
        "version": "V10.4.0"
      }
    },
    "腾讯应用宝": {
      "Android": {
        "version": "V10.4.0"
      }
    },
    "百度手机助手": {
      "Android": {
        "version": "V10.4.0"
      }
    },
    "豌豆荚": {
      "Android": {
        "version": "V10.4.0"
      }
    },
    "360手机助手": {
      "Android": {
        "version": "V10.4.0"
      }
    },
    "官网": {
      "Windows": {
        "version": "V7.7.0"
      },
      "Mac": {
        "version": "V3.1.2"
      },
      "Android": {
        "version": "V10.4.0"
      },
      "iOS": {
        "version": "V10.4.0"
      },
      "TV": {
        "version": "V6.3.0"
      }
    },
    "App Store": {
      "iOS": {
        "version": "V10.4.0"
      }
    }
  },
 }