如何高效使用Python将数据库数据导入导出为CSV文件？

本文共计518个文字，预计阅读时间需要3分钟。

实验题目：使用csv文件导入数据库——将StudentInfo.csv文件中的信息导入数据库StudentTest

代码：pythonimport pymysql

连接数据库conn=pymysql.connect(host='localhost', user='your_username', password='your_password', database='your_database')

创建游标对象cursor=conn.cursor()

打开csv文件with open('StudentInfo.csv', 'r', encoding='utf-8') as f: # 读取csv文件内容 lines=f.readlines()

遍历每行数据for line in lines: # 去除行首行尾的空格和换行符 line=line.strip() # 分割每行数据 data=line.split(',')

# 构建插入语句 sql=INSERT INTO StudentTest (name, age, gender) VALUES (%s, %s, %s) # 执行插入语句 cursor.execute(sql, data)

提交事务conn.commit()

关闭游标和连接cursor.close()conn.close()

实验题目：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数据高效导入 MYSQL
cursor = 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文件