我怎样才能在炼金术中使用额外的心理材料呢？

Question

我想将大量条目(~600 k)上传到PostgreSQL DB中的一个简单表中，每个条目包含一个外键、一个时间戳和3个浮点数。然而，每个条目执行所描述的核心批量插入需要花费60 ms，因此整个执行需要10小时，我发现这是executemany()方法的一个性能问题，但是已经用psycopg2 2.7中的execute_values()方法解决了。

我运行的代码如下：

#build a huge list of dicts, one dict for each entry
engine.execute(SimpleTable.__table__.insert(),
               values) # around 600k dicts in a list

我发现这是一个常见的问题，但是我还没有在sqlalchemy中找到一个解决方案。在某些情况下，有没有办法告诉sqlalchemy调用execute_values()？有没有其他方法可以实现巨大的插入，而无需自己构建SQL语句？

谢谢你的帮助！

Answer 1

这并不是你想要找到的答案，因为这并不能解决试图指示SQLAlchemy使用心理临时附加程序的问题，并且需要某种程度的手动SQL，但是:您可以使用raw_connection()从引擎访问底层的抄送自连接，这允许使用抄送自。

import io
import csv
from psycopg2 import sql

def bulk_copy(engine, table, values):
    csv_file = io.StringIO()
    headers = list(values[0].keys())
    writer = csv.DictWriter(csv_file, headers)
    writer.writerows(values)

    csv_file.seek(0)

    # NOTE: `format()` here is *not* `str.format()`, but
    # `SQL.format()`. Never use plain string formatting.
    copy_stmt = sql.SQL("COPY {} (" +
                        ",".join(["{}"] * len(headers)) +
                        ") FROM STDIN CSV").\
        format(sql.Identifier(str(table.name)),
               *(sql.Identifier(col) for col in headers))

    # Fetch a raw psycopg connection from the SQLAlchemy engine
    conn = engine.raw_connection()
    try:
        with conn.cursor() as cur:
            cur.copy_expert(copy_stmt, csv_file)

        conn.commit()

    except:
        conn.rollback()
        raise

    finally:
        conn.close()

然后

bulk_copy(engine, SimpleTable.__table__, values)

与执行INSERT语句相比，这应该要快得多。在这台机器上移动60万条记录大约需要8秒，~13秒/记录。您还可以将原始连接和光标与附加程序包一起使用。

Answer 2

同时，(从SqlAlchemy 1.2.0)可以在create_engine()函数上使用use_batch_mode标志。见文档。它使用来自execute_batch()的psycopg.extras函数。