Bulk insert with Postgres COPY FROM in SQLAlchemy
SQLAlchemy is nice and comfortable way to work with DB using application abstractions instead of ancient SQL.
But what if you need bulk insert for huge amount of records?
It would be insane to insert each record separately with SQLAlchemy. That will take ages.
Postgres has some recommendation how to do that with COPY FROM. This command takes file and bulk insert records from it very fast.
It will take milliseconds to insert thousands of records. With plain vanilla inserts it would take
hundred times more. If you insert millions of records, the difference could be seconds in
and hours in plain inserts!
The file could be in DB server file system or you can send it by network.
This command could be found in psycopg, usually you use this driver to work with Postgres from SQLAlchemy.
But how to use Postgres
COPY FROM in SQLAlchemy?
In SQLAlchemy you use sessions to work. In my example I created the session but in real application you would use the one you already have.
As you see, we are getting
cursor from SQLAlchemy session.
In cursor you can call copy_expert.
my_csv_string you place file with records to insert - and describe parameters of your file in
COPY FROM command.
In the example above I described it as CSV with header line. Default separator is “
,” and of cause
you can change that.
my_columns_comma_separated list you columns, separating them with “
,”, in exactly the same
order as in the CSV file. Usually you just use the same descriptor you used to create the CSV.
After and before
COPY FROM you use this SQLAlchemy session as usual.
commit() just after
COPY FROM as in my example above.
Or you can do some more operations in this session with SQLAlchemy.