python读取csv文件并自动在mysql中建表导数据

"""
根据csv在mysql中建表(表名为文件名,字段为csv中的header,默认所有字段为varchar,如需更改,在数据库中更改即可),并插入数据
"""

import csv
import pymysql


# 建表并生成插入语句
def create_table(file_name, header):
    table_name = file_name.split(".")[0]

    header = [str(i).replace("－", "_").replace("-", "_").replace("(", "_").replace(")", "") for i in header]
    create_sql = "CREATE TABLE `%s` (`id` int(11) NOT NULL AUTO_INCREMENT," % table_name
    for i in header:
        create_sql += "`%s` varchar(255) DEFAULT NULL," % i
    create_sql += "PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
    (create_sql)
    ()
    insert_sql = "insert into %s(%s)values(%s)" % (table_name, ",".join(header), ",".join(['%s'] * len(header)))
    print(insert_sql)
    return insert_sql


# 每一千条插入数据库一次
def insert_data(file_name):
    csv_path = "%s%s" % (dir_path, file_name)
    csv_file = open(csv_path, encoding='utf-8')
    csv_reader_lines = (csv_file)

    insert_datas = []
    for index, i in enumerate(csv_reader_lines):
        if index == 0:
            insert_sql = create_table(file_name, i)
            continue

        sql_data = tuple(i)
        print(sql_data)
        insert_datas.append(sql_data)
        if index + 1 % 1000 == 0:
            (insert_sql, insert_datas)
            ()
            insert_datas = []

    if len(insert_datas) != 0:
        (insert_sql, insert_datas)
        ()


db = (host='localhost', port=3306, user='root', passwd="123zxcv", db='test')
cursor = ()

dir_path = "D:/"
insert_data("")

()