mac mysql创建本地数据库文件_手把手教你MAC本地数据库的安装与使用【三】：用已有数据文件建表...-CSDN博客

本文链接：https://blog.csdn.net/weixin_29052717/article/details/113899886

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

在上一篇中，我们学会了如何用Python脚本连接数据库并执行sql，完成建表、插入数据等操作；而平常我们还会经常碰到一种情况：把已有的数据文件导入数据库中，建为一张数据表，这篇将手把手教你怎么做到这个操作，如何避开里面的坑

耗时：应不超过30分钟

准备：python相关的一系列准备，和【二】一样，这里就不重复了

一个我们将拿来操作的.csv数据文件，没有的话，我们用excel新建一个表，数据格式如下(第一行列名，第二行以下都是数据，具体内容不限)，然后存储为.csv格式，我将其命名为mysql_test_data.csv

csv文件的实貌如下，可以看到每一个值之间是以 , (半角逗号)作为分隔符的

准备就绪，开始写代码，我们新建一个python脚本，mysql_loadfile.py

(1)Enabling local data loading capability (重要！)

出于安全考虑，MySQL默认是禁用读取本地文件的

首先在终端登入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' " \