代码之家 › 专栏 › 技术社区 › Kyle Brandt

使用sqlacalchemy更快地将记录插入表中

sqlalchemy sqlite mysql sql python

Kyle Brandt · 技术社区 · 15 年前

我正在解析一个日志,并使用sqlachemy和python将其插入到mysql或sqlite中。现在我打开了一个到数据库的连接,当我循环遍历每一行时,在解析完之后插入它(这只是一个大表,对于SQL来说不是很有经验)。循环完成后,我关闭连接。汇总代码为:

log_table = schema.Table('log_table', metadata,
                         schema.Column('id', types.Integer, primary_key=True),
                         schema.Column('time', types.DateTime),
                         schema.Column('ip', types.String(length=15))
....
engine = create_engine(...)
metadata.bind = engine
connection = engine.connect()
....
for line in file_to_parse:
    m = line_regex.match(line)
    if m:
        fields = m.groupdict()
        pythonified = pythoninfy_log(fields) #Turn them into ints, datatimes, etc
        if use_sql:
            ins = log_table.insert(values=pythonified)
            connection.execute(ins)
            parsed += 1

我的两个问题是:

有没有一种方法可以加速这个基本框架中的插入?可能有一个插入队列,一些插入线程,一些批量插入,等等?
当我使用MySQL时,大约有120万条记录的插入时间是15分钟。对于sqlite,插入时间是一个多小时。DB引擎之间的时间差是正确的,还是意味着我做了非常错误的事情?

3 回复 | 直到 15 年前

Donal Fellows 15 年前

您应该尝试的一件大事是围绕多个插入放置一个事务,因为将数据库提交到磁盘确实需要很长时间。您将需要决定批处理级别,但第一次粗略的尝试是围绕整个批处理包装一个事务。

Conspicuous Compiler 15 年前

不知道桌上引擎(Myisam?YNANDB?),模式和索引,很难评论您在那里使用的两个数据库之间的具体情况。

但是,当使用这样的MySQL时,您可能会发现将数据写入临时文本文件的速度要快得多,然后 use the LOAD DATA INFILE syntax 把它全部加载到数据库中。看起来像 you can call the execute method on your connection object 运行执行此操作所需的SQL。

此外,如果您对逐行添加内容有着严格的要求,并且每次都要重新创建表,那么您可以在程序中验证键约束,并且仅在插入所有行之后才添加这些约束,从而节省数据库在每次插入时进行约束检查的时间。

Kyle Brandt 15 年前

我做了以下工作来实现一些批处理:

inserts = []
insert_every = 1000
for line in file_to_parse:
    m = line_regex.match(line)
    if m:
        fields = m.groupdict()
        if use_sql: #This uses Globals, Ick :-/
            inserts.append(pythonified)
            if (parsed % insert_every) == 0:
                connection.execute(log_table.insert(), inserts)
                inserts = []
            parsed += 1
if use_sql:
    if len(inserts) > 0:
        connection.execute(log_table.insert(), inserts)

这不使用事务,但以一种非常懒惰的方式,它允许我使用一个较小的示例将插入/解析阶段从大约13秒变为大约2秒。我将看到MySQL和SQLite之间的区别,现在使用完整的示例进行了这个更改。

我找到了这个的基本信息 here .

结果:
引擎:非分组插入时间(分钟):分组插入时间(分钟)
SQLITE:61:8
MySQL:15:2.5

我没有在mysql和sqlite之间刷新缓存,这可能有源文本文件,但我不认为这是一个相对重要的区别。