Multiple inserts at a time using SQLAlchemy with Postgres

Question

My current single insert code is:

def add_item(a_email,a_first,a_last,a_address,a_tag):
    query = db.insert(table_items).values(email=a_email,firstname=a_first,lastname=a_last,address=a_address,tag=a_tag)
    connection.execute(query)

Problem is, I'm inserting millions of entries and it's taking a LONG time doing it one by one. What is the best way to insert, say, 10 entries at a time? Say I have a list:

my_items = []
my_items.append(["[email protected]","John","Doe","1234 1st Street","Tag1"])

Pretend I appended 10 items into my_items, how can I get all of them into my Postgres database in one go?

Some pointers: stackoverflow.com/questions/13125236/…, stackoverflow.com/questions/43317376/…. Another option is use_batch_mode: docs.sqlalchemy.org/en/13/dialects/…, which will handle issuing multiple inserts in a single roundtrip for you, if using executemany style (passing multiple values). — Ilja Everilä
– Ilja Everilä, Commented Jul 15, 2019 at 20:09

Ronald Das · Accepted Answer · 2019-07-15 20:17:05Z

4

try using insert from sqlalchemy.dialects.postgresql , you will need to pass a list of dictionaries ( with key-value corresponding to your column_name and the data for that row. it basically converts it to a single insert statement for all your rows. An example would be

from sqlalchemy.dialects.postgresql import insert```
...
Data=[{'column1':value1},{'column1':valu1}]
stmt=insert(Table,Data)
db.session().execute(stmt)

As mentioned in the comment you can also use the COPY command

answered Jul 15, 2019 at 20:17

Ronald Das

1,29712 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ilja Everilä Over a year ago

No need to import it specifically from the dialect. The "plain" inserts support multiple values, if the dialect in use supports it.

Max Over a year ago

Hmm this almost worked for me, but it's asking me for 'id' (which is just the primary key). But I don't ever provide the primary key - I always let it be automatically generated. How can I use this method but not provide a column?

Ronald Das Over a year ago

@Max , you have to remove the column from your dictionary.

Collectives™ on Stack Overflow

Multiple inserts at a time using SQLAlchemy with Postgres

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related