用python导入mysql

#coding: utf-8
'''
@2016/04/13:
    To load raw-data.csv into db.
    Data is from Citeulike.
'''
import re
import mysql.connector

def main():
    # directory on iiot's server
    infile = open('./raw-data.csv','r')
    outfile = open('./error.txt','a+')
    

    #database on iiot's server
    cnx = mysql.connector.connect(user=' ',password=' ',
                                  host = ' ',port =  ,
                                  db=' ')
    cursor = cnx.cursor()
    insert_query = 'insert into article values (%s,%s,%s,%s,%s)'
    for read in infile:
        # 1       2        3             4           5
        # doc.id,"title","citeulike.id","raw.title","raw.abstract"
        try:
            res = re.search('(\d*),"(.*)",(\d*),"(.*)","(.*)"',read)
            print res.group(1)
            data = (res.group(1),res.group(2),res.group(3),res.group(4),res.group(5))
            cursor.execute(insert_query,data)
            cnx.commit()
        except Exception,e:
            outfile.write(str(e)+'\n'+read+'\n')
        
if __name__=='__main__':
    main()

把.csv文件导入mysql,.csv文件以','分隔，在每一项字符串中含有',' 不能用load data local infile .. fields terminated by ','，否则字符串被截断。

用re

转载于:https://www.cnblogs.com/yuchenkit/p/5388117.html