将CSV文件导入MySQL数据库

import re
import pymysql

# 连接数据库
config = {'host': 'localhost',
          'port': 3306,
          'user': 'root',
          'passwd': '你的密码',
          'charset': 'utf8',
          'local_infile': 1
          }
conn = pymysql.connect(**config)
cur = conn.cursor()


# load_csv函数，参数分别为csv文件路径，表名称，数据库名称
def load_csv(csv_file_path, table_name, database):
    # 打开csv文件
    file = open(csv_file_path, 'r', encoding='utf-8')
    # 读取csv文件第一行字段名，创建表
    reader = file.readline()
    b = reader.split(',')
    # print(b)
    colum = ''
    for a in b:
        a = re.sub('\s','',a)
        colum = colum + a + ' varchar(255),'
    colum = colum[:-1]
    # 编写sql，create_sql负责创建表，data_sql负责导入数据
    create_sql = 'create table if not exists ' + table_name + ' ' + '(' + colum + ')' + ' DEFAULT CHARSET=utf8'
    data_sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES" % (
    csv_file_path, table_name)
    # print(data_sql)
    # 使用数据库
    cur.execute('use %s' % database)
    # 设置编码格式
    cur.execute('SET NAMES utf8;')
    cur.execute('SET character_set_connection=utf8;')
    # 执行create_sql，创建表
    cur.execute(create_sql)
    # 执行data_sql，导入数据
    cur.execute(data_sql)
    conn.commit()
    # 关闭连接
    conn.close()
    cur.close()

from: https://zhuanlan.zhihu.com/p/45533287

如果报错：

 (1148, 'The used command is not allowed with this MySQL version')

就在MySQL里面执行：

set global local_infile='ON';

from: https://blog.csdn.net/AXIMI/article/details/89054799