如何在Sybase的SQLAlchemy 1.3.18中使用fast_executemy之类的东西来提高插入速度

Question

我用Python编写了一个插入Sybase数据库的程序，我使用pandas 1.0.4和SQLAlchemy 1.3.18。

我已经在sybase和我的脚本之间建立了链接，但实际上插入表的速度真的很慢(30k行需要10分钟…(

在SQL Server中，使用相同的代码(除了create_engine中的参数fast_executemony=True，dataframe.to_SQL((中的参数method='multii'，chunksize=500(，我在3秒内插入了该代码。

你有什么办法解决这个问题吗？

祝你今天愉快！

Answer 1

如果使用SAP ASE ODBC驱动程序，则外部SAP ASE(Sybase(方言确实支持fast_executemany。

Answer 2

Sybase ASE似乎不支持INSERT中的多值VALUES子句。如果需要在单个语句中插入多个值，而不是使用executemany简单地发出多个INSERT语句，则可以形成SELECT语句的UNION：

from sqlalchemy import union_all, select, literal
def sybase_insert(sqltable, conn, keys, data_iter):
sel = union_all(*[select([literal(v) for v in row]) for row in data_iter])
ins = sqltable.table.insert().from_select(keys, sel)
conn.execute(ins)

调用to_sql():时将函数作为method传递

In [11]: pd.DataFrame({"A": range(3)}).to_sql("foo", con=engine, method=sybase_insert)
2020-08-10 08:29:44,474 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("foo")
2020-08-10 08:29:44,474 INFO sqlalchemy.engine.base.Engine ()
2020-08-10 08:29:44,475 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("foo")
2020-08-10 08:29:44,475 INFO sqlalchemy.engine.base.Engine ()
2020-08-10 08:29:44,477 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE foo (
"index" BIGINT, 
"A" BIGINT
)

2020-08-10 08:29:44,477 INFO sqlalchemy.engine.base.Engine ()
2020-08-10 08:29:44,477 INFO sqlalchemy.engine.base.Engine COMMIT
2020-08-10 08:29:44,478 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_foo_index ON foo ("index")
2020-08-10 08:29:44,478 INFO sqlalchemy.engine.base.Engine ()
2020-08-10 08:29:44,478 INFO sqlalchemy.engine.base.Engine COMMIT
2020-08-10 08:29:44,479 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-08-10 08:29:44,481 INFO sqlalchemy.engine.base.Engine INSERT INTO foo ("index", "A") SELECT ? AS anon_1, ? AS anon_2 UNION ALL SELECT ? AS anon_3, ? AS anon_4 UNION ALL SELECT ? AS anon_5, ? AS anon_6
2020-08-10 08:29:44,481 INFO sqlalchemy.engine.base.Engine (0, 0, 1, 1, 2, 2)
2020-08-10 08:29:44,481 INFO sqlalchemy.engine.base.Engine COMMIT