使用python将csv文件快速转存到mysql
使用python将csv文件快速转存到mysql
因为一些工作需要，我们经常会做一些数据持久化的事情，例如将临时数据存到文件里，又或者是存到数据库里。
对于一个规范的表文件（例如csv），我们如何才能快速将数据存到mysql里面呢？
这个时候，我们可以使用python来快速编写脚本。
正文
对于一个正式的csv文件，我们将它打开，看到的数据是这样的：
这个数据很简单，只有三个列，现在我们要使用python将它快速转存到mysql。
既然使用python连接mysql，我们就少不了使用pymysql这个模块。
使用pip进行快速安装：
pip install pymysql
安装结束，我们使用pymysql连接数据库：
import pymysql
con = pymysql.connect(user="root",
passwd="root",
db="test",
host="47.95.xxx.xxx",
local_infile=1)user是连接数据库的用户名，passwd是连接数据的密码，db是你想要连接数据库的名字，host是你要连接数据库的主机，如果就是自己的电脑，就填127.0.0.1。我们这边是将csv批量写到数据库，需要设置local_infile参数，如果不添加会报错。
连接完数据库我们便可以使用游标来执行sql语句了：
cur = con.cursor()定义好了游标我们就可以使用execute方法来执行sql语句了。
cur.execute("set names utf8")
cur.execute("SET character_set_connection=utf8;")下面我们来打开我们的csv文件，读取里面的内容，我们需要提取第一行列名的信息，然后创建表：
with open(file_path, 'r', encoding='utf8') as f:
reader = f.readline()
print(reader)
devide = reader.split(',') # 做成列表
devide[-1] = devide[-1].rstrip('\n') # 去除最后的换行符
print(devide)默认读出来的数据就是一行字符串，现在我们通过“，”提取我们的列名，并且去除我们最后一个列名的换行符，这样我们就能得到所有的列名了。
下面我们需要创建表，在创建表之前我们需要将每个列指定一下格式：
column = ''
for dd in devide:
column = column + dd + ' varchar(255),'拼接好后我们需要将最后一个列的逗号去掉
col = column.rstrip(',')这样我们就可以写创建表的sql语句了：
table_name = "TBexport"create_table_sql = 'create table if not exists {} ({}) DEFAULT CHARSET=utf8'\
.format(table_name, col)cur.execute(create_table_sql)下面我们可以向表中插数据了：
首先要介绍一下，mysql支持csv数据的导入，以下是sql的语法：
LOAD DATA INFILE '文件名'
REPLACE INTO TABLE 表名
CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
那这边我们根据上面这个语句去拼写我们需要插入数据的语句：
file_path = "export.csv"data = 'LOAD DATA LOCAL INFILE \'' + file_path \
+ '\'REPLACE INTO TABLE ' \
+ table_name \
+ 'CHARACTER SET UTF8 FIELDS TERMINATED BY \',' \
'\' ENCLOSED BY \'\"\' ' \
'LINES TERMINATED BY \'\n\' IGNORE 1 LINES;'cur.execute(data.encode('utf8'))最后一步，提交事务。
（事务保证他们的连贯性，只要一步错就会进行回滚）
con.commit()记得关闭游标和数据库连接。
cur.close()
con.close()结果：
完整代码：
import pymysql
# file_path = "exam.csv"
# table_name = 'update_time_table'
file_path = "export.csv"
table_name = "TBexport"
try:
con = pymysql.connect(user="root",
passwd="root",
db="test",
host="47.95.20x.xxx",
local_infile=1)
con.set_charset('utf8')
cur = con.cursor()
cur.execute("set names utf8")
cur.execute("SET character_set_connection=utf8;")
with open(file_path, 'r', encoding='utf8') as f:
reader = f.readline()
print(reader)
devide = reader.split(',') # 做成列表
devide[-1] = devide[-1].rstrip('\n') # 去除最后的换行符
print(devide)
column = ''
for dd in devide:
#如果标题过长，只能存成text格式
if dd == "标题":
column = column + dd + ' TEXT,'
else:
column = column + dd + ' varchar(255),'
col = column.rstrip(',') # 去除最后一个多余的，
# print(column[:-1])
create_table_sql = 'create table if not exists {} ({}) DEFAULT CHARSET=utf8'.format(table_name, col)
print(create_table_sql)
data = 'LOAD DATA LOCAL INFILE \'' + file_path + '\'REPLACE INTO TABLE ' + table_name + ' CHARACTER SET UTF8 FIELDS TERMINATED BY \',\' ENCLOSED BY \'\"\' LINES TERMINATED BY \'\n\' IGNORE 1 LINES;'
cur.execute(create_table_sql)
cur.execute(data.encode('utf8'))
print(cur.rowcount)
con.commit()
except:
print("发生错误")
con.rollback()
finally:
cur.close()
con.close()代码也可以去github：
https://github.com/johnturingwu/csv_to_mysql
点击阅读原文可直达