Python批量导入文本数据到数据库的方法

最近在工作中碰到一个问题，需要快速的将一些历史数据导入到数据库中（CSV格式），经过考虑决定使用python来实现。

主要需要解决以下两个问题：

1、CSV格式的解释

2、数据的批量写入（性能考虑）

一、CSV格式的解释

CSV格式其实就是文本文件，使用open函数打开文件，然后循环访问就可以。

先将文件内容读取到内存中。

def read( self):

"""读取文件的内容，将文件内容作为列表返回。"""

lines = []

with open( self.file_name, 'rt', - 1, encoding= "GB2312") as file:

lines = file.readlines()

count = len(lines)

if count > 0:

del lines[ 0] #去掉第一行表头内容。

return lines

二、批量更新数据库内容

数据批量写入数据库有三种形式

1、方法1：将每一行要写的数据转为insert的SQL语句，然后将这些语句拼接成一个长长的字符串，程序一次性的把长长的sQL串传送到服务器上。

def execute( self, sql):

"""批量处理SQL语句。"""

cursor = self.conn.cursor()

#cursor.execute("begin transaction") #这个语句不能要，会导致数据写不到数据库中。

cursor.execute(sql)

self.conn.commit() #提交数据到服务器上。

2、方法2：将每一行要写的数据转为insert的SQL语句，将这些SQL语句一句一句的送给服务器运行，但是不提交事务，到最后才一次性提交

def execute_batch( self, sqls):

"""批量执行SQL语句

sqls => sql语句列表，每一个sql语句都是以分好结尾。

"""

cursor = self.conn.cursor()

for s in sqls:

cursor.execute(s)

self.conn.commit()

3、方法3：使用python库的executemany方法

def execute_many( self, sqlvalue):

"""参数化执行多个语句"""

cursor = self.conn.cursor()

cursor.executemany(sqlvalue[ 0], sqlvalue[ 1])

self.conn.commit()

三、例子

1、文件内容读取

class TickFile():

"""tick数据文件"""

def __init__( self, fileName):

self.file_name = fileName

def read( self):

"""读取文件的内容，将文件内容作为列表返回。"""

lines = []

with open( self.file_name, 'rt', - 1, encoding= "GB2312") as file:

lines = file.readlines()

count = len(lines)

if count > 0:

del lines[ 0] #去掉第一行表头内容。

return lines

2、数据库访问包装类：

class SqlServerProxy():

"""SQL server数据库访问代理类。"""

def __init__( self, server, user, password, database):

self.server = server

self.user = user

self.password = password

self.database = database

self.conn = self.__create_connection()

def close( self):

self.conn.close()

def __create_connection( self):

conn = pymssql.connect( server= self.server, user= self.user, password= self.password, database= self.database)

return conn

def execute( self, sql):

"""批量处理SQL语句。"""

cursor = self.conn.cursor()

#cursor.execute("begin transaction") #这个语句不能要，会导致数据写不到数据库中。

cursor.execute(sql)

self.conn.commit() #提交数据到服务器上。

def execute_many( self, sqlvalue):

"""参数化执行多个语句"""

cursor = self.conn.cursor()

cursor.executemany(sqlvalue[ 0], sqlvalue[ 1])

self.conn.commit()

def execute_batch( self, sqls):

"""批量执行SQL语句

sqls => sql语句列表，每一个sql语句都是以分好结尾。

"""

cursor = self.conn.cursor()

for s in sqls:

cursor.execute(s)

self.conn.commit()

3、解释数据写入类：

class SqlWriter():

"""SQL数据写入类"""

def __init__( self, sqlproxy):

if not isinstance(sqlproxy, SqlServerProxy):

raise ReferenceError( "参数sqlproxy不是SqlServerProxy类型。")

self.sql_proxy = sqlproxy

def write_many( self, product, symbol, lines):

sqlvalue = self.__lines_to_sqlmany(product, symbol, lines)

self.sql_proxy.execute_many(sqlvalue)

def write( self, product, symbol, lines):

"""将数据写入到数据库中。"""

sqls = []

for line in lines:

sql = self.__line_to_sql(product, symbol, line)

sqls.append(sql)

print(sql)

self.sql_proxy.execute_batch(sqls) #将数据保存到数据库中去。

def __lines_to_sqlmany( self, product, symbol, lines):

sql = "INSERT INTO Tick_" + product + "(InfoSeq, Symbol, TradeTime, LastPrice, Volumne, AskPrice1, AskVolume1,BidPrice1,BidVolume1,OpenPosition) VALUES ( \

%d ,%s ,%s ,%d ,%d ,%d ,%d ,%d ,%d ,0);"

values = []

for line in lines:

arr = line.split( ",")

timestring = "%s %s.%s" % (arr[ 0], arr[ 1], arr[ 2])

sqldata = ( 0, symbol, timestring, arr[ 3], arr[ 8], arr[ 4], arr[ 5], arr[ 5], arr[ 7])

values.append(sqldata)

return sql, values

def __line_to_sql( self, product, symbol, line):

"""将line的数据转换为SQL语句。"""

arr = line.split( ",")

timestring = "%s %s.%s"%(arr[ 0], arr[ 1], arr[ 2])

print( "timestring = %s"%(timestring))

sql = "INSERT INTO Tick_%s(InfoSeq, Symbol, TradeTime, LastPrice, Volumne, AskPrice1, AskVolume1,BidPrice1,BidVolume1,OpenPosition) VALUES ( \

%d \

,'%s' \

,'%s' \

,%s \

,%s \

,%s \

,%s \

,%s \

,%s \

,0 \

);"%(product, 0, symbol, timestring, arr[ 3], arr[ 8], arr[ 4], arr[ 5], arr[ 5], arr[ 7])

return sql

备注：需要引用的python包

import pymssql

import os

import pymssql