Python 函数实现将实验结果数据导入 MySQL 数据库

{/'title/': /'Python 函数实现将实验结果数据导入 MySQL 数据库/', /'description/': /'本文介绍了三个 Python 函数，分别用于将 txt、csv 和 xlsx 文件中的实验结果数据导入 MySQL 数据库。函数使用了 pymysql 和 pandas 库，并提供了使用示例。/', /'keywords/': /'Python, MySQL, 数据导入, 爬虫, 实验结果, txt, csv, xlsx, pymysql, pandas/', /'content/': /'以下是三个函数 txt_to_mysql、csv_to_mysql、excel_to_mysql 的实现：//n//npython//nimport pymysql//nimport pandas as pd//n//ndef txt_to_mysql(file_path, db_name, table_name)://n # 连接数据库//n conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db=db_name)//n cursor = conn.cursor()//n//n # 创建数据表//n create_table_sql = f/'CREATE TABLE IF NOT EXISTS {table_name} (id INT AUTO_INCREMENT PRIMARY KEY, content TEXT)/'//n cursor.execute(create_table_sql)//n//n # 读取txt文件内容//n with open(file_path, 'r', encoding='utf-8') as f://n content = f.read()//n//n # 插入数据//n insert_sql = f/'INSERT INTO {table_name} (content) VALUES ('{content}')/'//n cursor.execute(insert_sql)//n//n # 提交并关闭连接//n conn.commit()//n cursor.close()//n conn.close()//n//ndef csv_to_mysql(file_path, db_name, table_name)://n # 连接数据库//n conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db=db_name)//n cursor = conn.cursor()//n//n # 读取csv文件内容//n data = pd.read_csv(file_path)//n//n # 创建数据表//n create_table_sql = f/'CREATE TABLE IF NOT EXISTS {table_name} (id INT AUTO_INCREMENT PRIMARY KEY, {', '.join(data.columns)} VARCHAR(255))/'//n cursor.execute(create_table_sql)//n//n # 插入数据//n for index, row in data.iterrows()://n values = /'', '/'.join(row.values.astype(str))//n insert_sql = f/'INSERT INTO {table_name} ({', '.join(data.columns)}) VALUES ('{values}')/'//n cursor.execute(insert_sql)//n//n # 提交并关闭连接//n conn.commit()//n cursor.close()//n conn.close()//n//ndef excel_to_mysql(file_path, db_name, table_name)://n # 连接数据库//n conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db=db_name)//n cursor = conn.cursor()//n//n # 读取excel文件内容//n data = pd.read_excel(file_path)//n//n # 创建数据表//n create_table_sql = f/'CREATE TABLE IF NOT EXISTS {table_name} (id INT AUTO_INCREMENT PRIMARY KEY, {', '.join(data.columns)} VARCHAR(255))/'//n cursor.execute(create_table_sql)//n//n # 插入数据//n for index, row in data.iterrows()://n values = /'', '/'.join(row.values.astype(str))//n insert_sql = f/'INSERT INTO {table_name} ({', '.join(data.columns)}) VALUES ('{values}')/'//n cursor.execute(insert_sql)//n//n # 提交并关闭连接//n conn.commit()//n cursor.close()//n conn.close()//n//n//n使用示例：//n//npython//n# 将实验3的爬虫结果存储到Mysql//ntxt_to_mysql('suesnews_result.txt', 'mydatabase', 'experiment3')//n//n# 将实验4的爬虫结果存储到Mysql//ncsv_to_mysql('当当搜索关键词_单页面.csv', 'mydatabase', 'experiment4_single_page')//ncsv_to_mysql('当当搜索关键词_多页面.csv', 'mydatabase', 'experiment4_multiple_pages')//n//n# 将实验5的爬虫结果存储到Mysql//nexcel_to_mysql('豆瓣电影TOP250信息及评论.xlsx', 'mydatabase', 'experiment5')//n//n//n请注意，上述示例假设你已经在本地安装了MySQL数据库，并且数据库的主机名、端口号、用户名、密码等信息都正确。你需要根据你自己的实际情况修改这些连接信息。/'}/