Bulk insert with SQLAlchemy ORM
database, mysql, orm, python, sqlalchemy
Solution
SQLAlchemy introduced that in version `1.0.0`:
Bulk operations - SQLAlchemy docs
With these operations, you can now do bulk inserts or updates!
For instance, you can do:
s = Session()
objects = [
User(name="u1"),
User(name="u2"),
User(name="u3")
]
s.bulk_save_objects(objects)
s.commit()
Here, a bulk insert will be made.
Problem
Is there any way to get SQLAlchemy to do a bulk insert rather than inserting each individual object. i.e., doing: ``` INSERT INTO `foo` (`bar`) VALUES (1), (2), (3) ``` rather than: ``` INSERT INTO `foo` (`bar`) VALUES (1) INSERT INTO `foo` (`bar`) VALUES (2) INSERT INTO `foo` (`bar`) VALUES (3) ``` I've just converted some code to use sqlalchemy rather than raw sql and although it is now much nicer to work with it seems to be slower now (up to a factor of 10), I'm wondering if this is the reason. May be I could improve the situation using sessions more efficiently. At the moment I have `autoCommit=False` and do a `session.commit()` after I've added some stuff. Although this seems to cause the data to go stale if the DB is changed elsewhere, like even if I do a new query I still get old results back? Thanks for your help!