Python：CSV到MySQL数据库

先直接提供Python编写的打开并读取CSV文件内的数据到MySQL数据库的代码。

import pymysql

def CSV_SQL():
    #链接数据库(注:db是数据库的名字, connect_timeout连接超时设定)
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root',
                           password='wiseasy', db='db_test', charset='utf8', connect_timeout=1000)
    #创建数据库对象
    cursor = conn.cursor()
    #执行创表语句(创建表，并基于字段名和字段对应列的容量)
    sql01 = 'create table if not exists CSV_text (T1 varchar(50), T2 varchar(50), T3 varchar(50), ' 
            'T4 varchar(50), T5 varchar(50), T6 varchar(50), T7 varchar(50))' 
    cursor.execute(sql01)
    #读取CSV文件并导入数据库
    #打开文件，用with打开可以不用去特意关闭file了，python3不支持file()打开文件，只能用open()
    with open('D:\Moves\测试文本.csv', encoding='utf-8') as lines:
        for line in lines.readlines():
            line = line.strip()
            list = line.split(',', -1)
            T1 = list[0]
            T2 = list[1]
            T3 = list[2]
            T4 = list[3]
            T5 = list[4]
            T6 = list[5]
            T7 = list[6]
            print(T1, T2, T3, T4, T5, T6, T7)
            #执行插入表数据语句
            sql02 = 'insert into CSV_text (T1, T2, T3, T4, T5, T6, T7) value(%s, %s, %s, %s, %s, %s, %s)' 
            cursor.execute(sql02, (T1, T2, T3, T4, T5, T6, T7))
    conn.commit() #提交事务
    conn.close() #关闭连接

if __name__ == '__main__':
    CSV_SQL()

 下面放出CSV文件的测试文本内容。

He,didn’t,say,any,more,but,we’ve,always,been,unusually
communicative,in,a,reserved,way,and,I,understood,that,he
meant,a,great,deal,more,than,that.,In,consequence,I’m
inclined,to,reserve,all,judgments,a,habit,that,has,opened
up,many,curious,natures,to,me,and,also,made,me
the,victim,of,not,a,few,veteran,bores.,end1,end2

执行Python命令后，检查MySQL数据库 。

MySQL查看导入的表的命令：

SELECt * FROM db_test.csv_text;

MySQL删除导入的表的命令：

DROP TABLE db_test.csv_text;