1

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(["test@test.com","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?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Max
  • 597
  • 7
  • 21
  • 1
    You should consider using `COPY` instead of inserts. – Ilja Everilä Jul 15 '19 at 19:58
  • Some pointers: https://stackoverflow.com/questions/13125236/sqlalchemy-psycopg2-and-postgresql-copy, https://stackoverflow.com/questions/43317376/how-can-i-use-psycopg2-extras-in-sqlalchemy. Another option is `use_batch_mode`: https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-connect-arguments, which will handle issuing multiple inserts in a single roundtrip for you, if using `executemany` style (passing multiple values). – Ilja Everilä Jul 15 '19 at 20:09

1 Answers1

3

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

Ronald Das
  • 1,142
  • 11
  • 17
  • No need to import it specifically from the dialect. The "plain" inserts support multiple values, if the dialect in use supports it. – Ilja Everilä Jul 15 '19 at 20:29
  • 1
    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? – Max Jul 16 '19 at 06:48
  • @Max , you have to remove the column from your dictionary. – Ronald Das Aug 07 '19 at 08:01