Python函数：将txt、csv和xlsx文件导入MySQL数据库

{"title":"Python函数：将txt、csv和xlsx文件导入MySQL数据库","description":"本文介绍了三个Python函数，分别用于将txt、csv和xlsx文件中的数据导入MySQL数据库。每个函数都包含了连接数据库、创建数据表、读取文件内容并插入数据的步骤。","keywords":"Python, MySQL, 函数, txt, csv, xlsx, 导入, 数据库, 数据, 文件","content":"以下代码展示了三个不同的函数，分别实现了将txt文件、csv文件、xlsx文件存储到MySQL数据库中：\n\n1. txt_to_mysql：将txt文件存储到MySQL数据库中\n\npython\nimport mysql.connector\n\ndef txt_to_mysql(file_path, table_name):\n # 连接MySQL数据库\n conn = mysql.connector.connect(host='localhost', user='root', password='password', database='database')\n\n # 创建游标对象\n cursor = conn.cursor()\n\n # 创建数据表\n create_table_query = f"CREATE TABLE IF NOT EXISTS {table_name} (id INT AUTO_INCREMENT PRIMARY KEY, content TEXT)"\n cursor.execute(create_table_query)\n\n # 读取txt文件内容\n with open(file_path, 'r') as file:\n content = file.read()\n\n # 插入数据\n insert_data_query = f"INSERT INTO {table_name} (content) VALUES (%s)"\n cursor.execute(insert_data_query, (content,))\n\n # 提交事务\n conn.commit()\n\n # 关闭游标和数据库连接\n cursor.close()\n conn.close()\n\n\n2. csv_to_mysql：将csv文件存储到MySQL数据库中\n\npython\nimport mysql.connector\nimport csv\n\ndef csv_to_mysql(file_path, table_name):\n # 连接MySQL数据库\n conn = mysql.connector.connect(host='localhost', user='root', password='password', database='database')\n\n # 创建游标对象\n cursor = conn.cursor()\n\n # 创建数据表\n create_table_query = f"CREATE TABLE IF NOT EXISTS {table_name} (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)"\n cursor.execute(create_table_query)\n\n # 读取csv文件内容并插入数据\n with open(file_path, 'r') as file:\n reader = csv.reader(file)\n next(reader) # 跳过标题行\n for row in reader:\n name, age = row\n insert_data_query = f"INSERT INTO {table_name} (name, age) VALUES (%s, %s)"\n cursor.execute(insert_data_query, (name, age))\n\n # 提交事务\n conn.commit()\n\n # 关闭游标和数据库连接\n cursor.close()\n conn.close()\n\n\n3. excel_to_mysql：将xlsx文件存储到MySQL数据库中\n\npython\nimport mysql.connector\nimport openpyxl\n\ndef excel_to_mysql(file_path, table_name):\n # 连接MySQL数据库\n conn = mysql.connector.connect(host='localhost', user='root', password='password', database='database')\n\n # 创建游标对象\n cursor = conn.cursor()\n\n # 创建数据表\n create_table_query = f"CREATE TABLE IF NOT EXISTS {table_name} (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)"\n cursor.execute(create_table_query)\n\n # 读取xlsx文件内容并插入数据\n workbook = openpyxl.load_workbook(file_path)\n worksheet = workbook.active\n for row in worksheet.iter_rows(min_row=2, values_only=True):\n name, age = row\n insert_data_query = f"INSERT INTO {table_name} (name, age) VALUES (%s, %s)"\n cursor.execute(insert_data_query, (name, age))\n\n # 提交事务\n conn.commit()\n\n # 关闭游标和数据库连接\n cursor.close()\n conn.close()\n\n\n以上函数中的参数file_path为文件路径，table_name为要存储数据的数据表名称。请根据实际情况修改数据库连接信息和数据表结构。