背景:该系列终于可以收个尾了,没有看过该同名系列【一】【二】的同学一定要看完,跟着步骤一步步做下来
在上一篇中,我们学会了如何用Python脚本连接数据库并执行sql,完成建表、插入数据等操作;而平常我们还会经常碰到一种情况:把已有的数据文件导入数据库中,建为一张数据表,这篇将手把手教你怎么做到这个操作,如何避开里面的坑
耗时:应不超过30分钟
准备:
- python相关的一系列准备,和【二】一样,这里就不重复了
- 一个我们将拿来操作的.csv数据文件,没有的话,我们用excel新建一个表,数据格式如下(第一行列名,第二行以下都是数据,具体内容不限),然后存储为.csv格式,我将其命名为mysql_test_data.csv
csv文件的实貌如下,可以看到每一个值之间是以 , (半角逗号)作为分隔符的
准备就绪,开始写代码,我们新建一个python脚本,mysql_loadfile.py
(1)Enabling local data loading capability (重要!)
出于安全考虑,MySQL默认是禁用读取本地文件的[1],因此我们必须要手动开启读取本地文件的功能,具体步骤如下:
首先在终端登入MySQL,不清楚这一步的同学请去看该系列第一篇:
PATH
输入自己设置的密码后,进入到sql模式,写以下语句
SET GLOBAL local_infile=1;
随后输入q后回车,退出mysql,再以以下语句重新连接
mysql --local-infile=1 -u root -p
这样前期的准备工作就完成了,我们开始写Python脚本
(2)老样子,先导入pymysql模块,创建连接对象和光标对象
import
(3)建表
sql的建表语句形如下,其中的tablename为你想要使用的表名, column_name为列名,data_type规定列的数据类型(例如 varchar、integer、decimal、date 等等); size 规定表中列的最大长度(字节为单位); default charset = xxx,指定特定的字符集
IF NOT EXISTS判断名为table_name的表是否不存在,仅在table_name表不存在时,才执行建表
CREATE
在Python脚本中建表:
- 读取csv第一行作为新建表的列名
- 以字符串拼接的方式写SQL建表语句
table_name = 'mysql_test_data'
# 将infile_path改为你的数据文件的绝对路径
infile_path = '/Users/jerry/PycharmProjects/zhihuzhuanlan_examples/mysql_test_data.csv'
# 打开csv文件
with open(infile_path, 'r', encoding='utf-8-sig') as f:
# 读取csv文件第一行字段名,创建表
reader = f.readline()
# 清除'','t','n','r'字符
reader = reader.strip()
print(reader)
# 以 , 为分隔符将首行切分为一个list,在本例中是['a','b','c','d']
column_names = reader.split(',')
print(column_names)
column = ' ('
for column_name in column_names:
column = column + column_name + ' varchar(255),'
# 将最后一个' varchar(255),'中的 半角逗号(,) 去掉
column = column[:-1]
column += ')'
create_sql = 'create table if not exists '+table_name+column+' default charset=utf8'
cursor.execute(create_sql)
这里有几点需要注意的:
- 所用的文件路径必须是绝对路径,在Pycharm里面简便复制文件路径的方法为右键单击文件,选择“copy path”
- with open(infile_path, 'r', encoding='utf-8-sig') as f: , 关于with open可查阅该说明,需要注意的是如果不指定encoding='utf-8-sig',读取.csv文件时会在首行首位出现‘ufeff’乱码
- strip()可查阅该说明,split()可查阅该说明
(4)将数据插入建好的表中
写导入本地文件的SQL语句,语法如下
load
据此,我们在mysql_loadfile.py添加以下:
# 用于分行续写字符串
load_sql = ""
"load data local infile '%s' "
"into table %s "
"fields terminated by ',' "
"lines terminated by 'n' "
"ignore 1 lines" % (infile_path, table_name)
# 打印上述sql语句,检查一下是否正确
print(load_sql)
cursor.execute(load_sql)
这里有几点需要注意的:
- %s为占位符,后接% (value, value)将值插入到%s占位符的字符串中,详细信息可参考该篇
- lines terminated by 'n',而不是'n';前者打印输出为'n',后者打印输出为回车,可以自行尝试
(5)收尾
# 查询新建表的所有内容,看是否正确
该系列终于结束了,如果有收获的同学请以任意形式支持一下(码字不易...),如果有任何问题欢迎交流
参考
- ^详情见官方说明 https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html