SQLAlchemy原始SQL与表达式语言语句 [英] SQLAlchemy raw sql vs expression language statements

问题描述

当通过SQLA-Expression-Language语句在MySQL-DB中插入多行时，fe

When inserting multiple rows in a MySQL-DB via a SQLA-Expression-Language statement, f.e.

Foo.__table__.insert().execute([{'bar': 1}, {'bar': 2}, {'bar': 3}])

与执行同一任务的原始 sql语句相比，速度极慢，即

it´s extremly slow, when compared to the execution of a "raw" sql statement for the same task, i.e.

engine.execute("insert into foo (bar) values (1),(2),(3)")

这是什么原因？ SQLA不能生成单个大容量插入语句并因此执行多个插入吗？由于orm的速度限制，我需要一种快速的方法来一次添加几千行，但是SQLA-Expression-Language-Version太慢了。因此，我需要自己编写原始sql吗？文档对此不太清楚。

What is the reason for this? Can´t SQLA generate a single bulk insert statement and therefore executes multiple inserts? Due to the speed limits of the orm, i need a fast way to add several thousand rows at once, but the SQLA-Expression-Language-Version is too slow. So, do i need to write the raw sql by myself? The documentation isn't too clear about this.

我使用ORM插入，带有预分配PK的ORM和SQLA批量插入进行了速度测试（请参阅 SQLA批量插入速度）像这样（ https://gist.github.com/3341940 ）：

I ran a speed test with the ORM insert, the ORM with preassigned PK and the SQLA bulk insert (see SQLA bulk insert speed) like this (https://gist.github.com/3341940):

• SqlAlchemy ORM：500条记录的总时间9.61418914795 secs


• SqlAlchemy ORM pk给出：500条记录的总时间9.56391906738 secs


• SqlAlchemy核心：500条记录的总时间9.5362598896秒


• SQLAlchemy RAW字符串执行：500条记录的总时间1.233677秒

• SqlAlchemy ORM: Total time for 500 records 9.61418914795 secs
• SqlAlchemy ORM pk given: Total time for 500 records 9.56391906738 secs
• SqlAlchemy Core: Total time for 500 records 9.5362598896 secs
• SQLAlchemy RAW String Execution: Total time for 500 records 1.233677 secs

如您所见，这三个版本之间几乎没有区别。仅执行原始字符串插入（其中所有记录都包含在原始sql语句中）的执行速度明显加快。因此，对于快速插入，SQLA似乎不是最佳选择。

As you can see, there is practically no difference between the three versions. Only the execution of a raw string insert, where all the records are included in the raw sql statement is significantly faster. Thus, for fast inserts, SQLA seems sub-optimal.

推荐答案

似乎最近才支持具有多个值的特殊INSERT。 （0.8未发布），您可以在本节底部看到有关executemany（用列表执行）和多值插入之间的区别的注释：

It seems that the special INSERT with multiple values only became recently supported(0.8 unreleased), you can see the note at the bottom of this section regarding the difference between executemany(what execute with a list does) and a multiple-VALUES INSERT:

http：//docs.sqlalchemy。 org / ru / latest / core / expression_api.html＃sqlalchemy.sql.expression.Insert.values

这应该解释您看到的性能差异。您可以尝试安装开发版本并使用链接中提到的更改后的调用语法重复测试，以确认。

This should explain the performance difference you see. You could try installing the development version and repeating the tests with the altered calling syntax, mentioned in the link, to confirm.

这篇关于SQLAlchemy原始SQL与表达式语言语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！