执行本地sql_手把手教你MAC本地数据库的安装与使用【三】：用已有数据文件建表...

背景：该系列终于可以收个尾了，没有看过该同名系列【一】【二】的同学一定要看完，跟着步骤一步步做下来

在上一篇中，我们学会了如何用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="$PATH":/usr/local/mysql/bin #设置(local)环境变量
mysql -u root -p 

输入自己设置的密码后，进入到sql模式，写以下语句

SET GLOBAL local_infile=1;

随后输入q后回车，退出mysql，再以以下语句重新连接

mysql --local-infile=1 -u root -p

这样前期的准备工作就完成了，我们开始写Python脚本

（2）老样子，先导入pymysql模块，创建连接对象和光标对象

import pymysql

# 我们这次用【二】中提到的另一种方式创建连接对象
# 也可以用connection = pymysql.connect(host = 'localhost', user = 'root', password = '******',  db = 'mysql', local_infile = 1)
config = {
    'host':'localhost',
    'user':'root',
    'password':'******', # 输入你的数据库密码
    'db':'mysql', # 输入你要连接的数据库名，这里我们可以使用默认建立好的mysql；你也可以使用该系列第一篇结尾里提到的show databases来查看本地的数据库名
    'local_infile':1 # 值可以是1或True
}
connection = pymysql.connect(**config) # **表示函数中解包
cursor = connection.cursor()

（3）建表

sql的建表语句形如下，其中的tablename为你想要使用的表名， column_name为列名，data_type规定列的数据类型（例如 varchar、integer、decimal、date 等等）; size 规定表中列的最大长度（字节为单位）; default charset = xxx，指定特定的字符集

IF NOT EXISTS判断名为table_name的表是否不存在，仅在table_name表不存在时，才执行建表

CREATE TABLE table_name IF NOT EXISTS
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
) default charset=utf8;

在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 data local infile 文件绝对路径
into table 将新建的数据表表名
fields terminated by 字段的分隔符 #对于.csv文件而言是','
lines terminated by 数据行的分隔符 #通常是'n'
ignore 1 lines # 忽略掉第一行数据，因为第一行是字段名

据此，我们在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）收尾

# 查询新建表的所有内容，看是否正确
select_sql = "select * from %s" % table_name
cursor.execute(select_sql)
result = cursor.fetchall()
print(result)

# 提交事务
connection.commit()

# 关闭连接
connection.close()

# 关闭游标
cursor.close()

该系列终于结束了，如果有收获的同学请以任意形式支持一下（码字不易...），如果有任何问题欢迎交流

参考

1. ^详情见官方说明 https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html