使用Python将csv文件批量化导入SQL Server

一开始的时候，是想用bulk insert的方法直接将csv文件导入数据库的，后来发现这一方法在SQL语法上是可行的，但是由于Python需要pymmsql包来与SQL Server交互，而这个包似乎并不支持这一语句=。= 无奈只能另想办法，曲线救国，先将csv文件读取到内存中，然后用cursor.executemany的方法将数据导入。虽然慢是慢了点，不过好歹实现了目的。如果有大神有更快的办法，还请赐教

在网上下了所有股票91年至今每天的交易数据，下下来之后发现是每个股票一个csv文件，那么总共有好几千的文件。这么多的文件指望用手动的方法在MSSQL中导入是不可能的，所以只能用脚本来实现。在发现bulk insert似乎行不通以后，只能转而使用executemany指令来实现。

1.首先需要下载pymssql包 http://www.pymssql.org/en/latest/

2.在插入之前，由于bulk insert不行，那么就需要先将csv文件读入到内存中，然后用insert…指令插入。如果文件中列的设置与数据库表的列的设置不一致，则还需要修改，筛选文件中的内容，挑选出要插入的那几列。在读取csv文件的过程中，还需要注意一个问题，就是现在网上大部分的程序都是reader=csv.reader(open('file_name.csv', 'rb'))的格式，但是我在用的时候发现会报错

_csv.Error: iterator should return strings, not bytes (did you open the file in text mode?)

后来在http://bugs.python.org/msg82661中，找到了问题所在。

Sorry, folks, we’ve got an understanding problem here. CSV files are
typically NOT created by text editors. They are created e.g. by “save
as csv” from a spreadsheet program, or as an output option by some
database query program. They can have just about any character in a
field, including \r and \n. Fields containing those characters should
be quoted (just like a comma) by the csv file producer. A csv reader
should be capable of reproducing the original field division. Here for
example is a dump of a little file I just created using Excel 2003:
… This sentence in the documentation is NOT an error: “”“If csvfile
is a file object, it must be opened with the ‘b’ flag on platforms
where that makes a difference.”“”

根据上面这段话，将代码改成下面这样就OK了：

reader=csv.reader(open('file_name.csv', 'r'))
for item in reader:
    print(item)

3.在将csv文件读入到内存中之后，需要比对文件中列与待插入的数据表列的异同，需要设立一个数组来表示两者间的映射关系。这就需要从数据库中获得待插入表的列名信息。可以用如下sql语句获得：

select name from syscolumns where id=object_id(N'your_table_name')

然而在实际操作过程中，可能会因为没指定数据库而报错。所以还需要再加上一句，如下面所示：

use your_database_name;select name from syscolumns where id=object_id(N'your_table_name')

在python中，返回的列名为’name’格式，需要进行一些简单的处理才能使用

4.使用executemany指令插入。executemany的使用方法如下

cursor.executemany(
    "INSERT INTO persons VALUES (%d, %s, %s)",
    [(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike T.', 'Sarah H.')])
# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()

这里又两个要注意的地方，一是指令的数据部分
[(1, 'John Smith', 'John Doe'),(2, 'Jane Doe', 'Joe Dog'),(3, 'Mike T.', 'Sarah H.')]
是一个list，而list的组成单元必须要是tuple或者Dictionary，也就是元组或词典形式，否则会报错。而csv读取到内存中是二维数组的形式，因此需要用tuple()函数转化成元组形式才能插入

第二个要注意的部分就是执行完cursor.executemany指令以后，一定要执行conn.commit()，否则是没有效果的。

5.性能提升。为了提高插入的速度，目前我把文件的读取跟插入分开了，也就是说不是读取一个文件就插入一次，而是读取多个文件后统一插入一次。另外，还可以想办法通过多线程同时读取文件的方式，进一步提高运行速率

目前代码如下

# @function :   import csv data into mysql database
# @author:      multiangle
# @date:        2015/8/14

import pymssql
import os
import csv

class MSSQL_Interface :
    def __init__(self,host,user,pwd,dbname):
        self.host=host
        self.user=user
        self.pwd=pwd
        self.dbname=dbname

        self.connect_db()

    def connect_db(self):
        try:
            self.conn=pymssql.connect(user=self.user,password=self.pwd,host=self.host,database=self.dbname,charset="utf8")
            self.cur=self.conn.cursor()
        except:
            print("ERROR: fail to connect mssql")

    def __del__(self):
        self.cur.close()
        self.conn.close()

    def get_col_name(self,table_name):
        q1="use "+self.dbname+';'
        q2="select name from syscolumns where id=object_id(N'"+table_name+"')"
        query=q1+q2
        self.cur.execute(query)
        col_pre_name=self.cur.fetchall()
        col_num=col_pre_name.__len__()
        col_name=[]
        for item in col_pre_name:
            col_name.append(item[0])
        return col_name

    def read_csv(self,path,table_col): #需要输入表的列名
        file=csv.reader(open(path,'r'))
        data_list=[]
        num=0
        for row in file:
            if num==0:
                file_col=row
                mapping_array=[-1]*table_col.__len__()
                for i in range(0,table_col.__len__()):
                    try:
                        temp_index=file_col.index(table_col[i])
                        mapping_array[i]=temp_index         #mapping_arry[i]记录表中第i列的数据在待导入文件的第几列
                    except:
                        pass
            else:
                temp_new=[]
                for i in range(0,table_col.__len__()):
                    if mapping_array[i]>=0:
                        temp_new.append(row[mapping_array[i]])
                    else:
                        temp_new.append("''")
                data_list.append(tuple(temp_new))
            num=num+1
        return data_list

    def import_csv_file_weak(self,path,table_name):
        #会先将欲引入的文件与表的列对比，值选择导入数据表中已经存在的列的内容，对于不存在与数据表中的数据，则不导入
        table_col=self.get_col_name(table_name)
        data_list=self.read_csv(path,table_col)

        q0="use "+self.dbname+';'
        q1="insert into "+table_name+" values("
        temp_q=""
        for i in range(0,table_col.__len__()):
            temp_q=temp_q+"%s"
            if i<table_col.__len__()-1 :
                temp_q=temp_q+','
        query=q0+q1+temp_q+")"
        # try:
        self.cur.executemany(query,data_list)
        self.conn.commit()

    def import_folder(self,path,table_name):
        #只能处理一个文件夹内文件格式统一的文件
        table_col=self.get_col_name(table_name)
        file_list=os.listdir(path)
        extension_name=file_list[0][file_list[0].index('.')+1:file_list[0].__len__()]
        if extension_name=='csv':
            temp_data_list=[]
            temp_file_list=""
            for name in file_list:
                file_path=path+'\\'+name
                temp_file_list=temp_file_list+name+'\t'
                temp_data_list=temp_data_list+self.read_csv(file_path,table_col)
                if temp_data_list.__len__()>20000: #如果超过1W行，则读写一次
                    try:
                        self.import_list_weak(temp_data_list,table_name)
                        print(temp_file_list+" insert success, "+str(temp_data_list.__len__())+" lines is influenced")
                        temp_data_list=[]
                        temp_file_list=""
                    except:
                        print("--------------------------------------------------ERROR: "+temp_file_list+" insert fail--------------------------------------------------")


    def import_list_weak(self,data_list,table_name):    #输入已经整理好的list格式
        q0="use "+self.dbname+';'
        q1="insert into "+table_name+" values("
        temp_q=""
        for i in range(0,data_list[0].__len__()):
            temp_q=temp_q+"%s"
            if i<data_list[0].__len__()-1 :
                temp_q=temp_q+','
        query=q0+q1+temp_q+")"

        self.cur.executemany(query,data_list)
        self.conn.commit()

if __name__ == '__main__':
    host='127.0.0.1'
    user='sa'
    pwd='admin'
    db='multiangle_investment'
    item=MSSQL_Interface(host,user,pwd,db)
    item.import_folder('D:\\multiangle\\Stock\\Data\\History\\overview-data-sh','stock_past_data')