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