How to have something like fast_executemany with SQLAlchemy 1.3.18 with Sybase to improve the speed of insert into?

Question

I've made a program to insert into a Sybase database in Python, i use pandas 1.0.4 and SQLAlchemy 1.3.18.

I've made the link between sybase and my script, but actually the speed of insert into tables is really really slow (10 minutes for 30k rows...)

In SQL Server, with the same code (except the parameter fast_executemany=True in create_engine, and parameters method='multi', chunksize = 500 for dataframe.to_sql()), i've made the insert into 3 secondes.

DO you have any idea to fix this?

Have a nice day !

Answer 1

It looks like Sybase ASE does not support a multiple values VALUES clause in INSERT. You could form a UNION of SELECT statements, if you need to insert multiple values in a single statement instead of using executemany to simply issue many INSERT statements:

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)

Pass the function as the method when calling to_sql():

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