Python将csv文件高效导入数据库，高效导出到csv文件（Python 导出csv）

Python将csv文件高效导入数据库，高效导出到csv文件（Python 导出csv）

实验题目：csv文件导入数据库

将StudentInfo.csv文件中的信息导入数据库StudentTest

代码

import pymysql# 连接 StudentTest数据库config = {'host': 'localhost','port': 3306,'user': 'root','passwd': '1414141','db' : 'StudentTest'，'local_infile': 1}conn = pymysql.connect(**config) # 执行 SQL 创建 表tb_student cursor = conn.cursor() sql = "CREATE TABLE tb_student(ID INT NOT NULL, name VARCHAR(20) NULL,score INT NULL,final INT NULL, PRIMARY KEY (ID))" cursor.execute(sql) conn.close() # CSV数据高效导入 MYSQLcursor = conn.cursor()sql = " LOAD DATA LOCAL INFILE '{0}' INTO TABLE {1} CHARACTER SET GBK FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES "try: cursor.execute(sql.format("D:/studentInfo.csv", "tb_student")) conn.commit()except Exception as e: print(e) conn.rollback()cursor = conn.cursor()sql = "select * from tb_student"cursor.execute(sql)rows = cursor.fetchall()ls2 = list(map(list, rows))conn.close()print(ls2)

结果

数据库表

数据库导出到csv文件

数据库StudentTest中的信息导出到csvdata.csv文件中

代码

import pymysql# 连接 StudentTest数据库config = {'host': 'localhost','port': 3306,'user': 'root','passwd': '1414141','db' : 'StudentTest'，'local_infile': 1}conn = pymysql.connect(**config)cursor = conn.cursor()sql = " select * into outfile '{0}' CHARACTER SET GBK FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' from(select '学号','姓名 ','平均成绩','期末成绩' union select * from tb_student) as b "try: cursor.execute(sql.format("D:/csvdata.csv",)) conn.commit()except Exception as e: print(e) conn.rollback()conn.close()

导出成功，查看

CSV文件

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。