psycopg2 insert multiple rows with one query

Question

I need to insert multiple rows with one query  number of rows is not constant   so I need to execute query like this one   INSERT INTO t  a  b  VALUES  1  2    3  4    5  6     The only way I know is  args     1 2    3 4    5 6   args str       join cursor mogrify   s    x     for x in args  cursor execute  INSERT INTO t  a  b  VALUES   args str    but I want some simpler way

User · Answer

New execute values method in Psycopg 2 7   data     1  x     2  y    insert query    insert into t  a  b  values  s  psycopg2 extras execute values       cursor  insert query  data  template None  page size 100     The pythonic way of doing it in Psycopg 2 6   data     1  x     2  y    records list template       join    s     len data   insert query    insert into t  a  b  values     format records list template  cursor execute insert query  data    Explanation  If the data to be inserted is given as a list of tuples like in  data     1  x     2  y      then it is already in the exact required format as    the values syntax of the insert clause expects a list of records as in  insert into t  a  b  values  1   x    2   y   Psycopg adapts a Python tuple to a Postgresql record     The only necessary work is to provide a records list template to be filled by psycopg    We use the data list to be sure of the template length records list template       join    s     len data     and place it in the insert query  insert query    insert into t  a  b  values     format records list template    Printing the insert query outputs  insert into t  a  b  values  s  s   Now to the usual Psycopg arguments substitution  cursor execute insert query  data    Or just testing what will be sent to the server  print  cursor mogrify insert query  data  decode  utf8      Output   insert into t  a  b  values  1   x    2   y

User · Answer

The cursor copyfrom solution as provided by  jopseph sheedy   https   stackoverflow com users 958118 joseph-sheedy  above  https   stackoverflow com a 30721460 11100064  is indeed lightning fast    However  the example he gives are not generically usable for a record with any number of fields and it took me while to figure out how to use it correctly    The IteratorFile needs to be instantiated with tab-separated fields like this  r is a list of dicts where each dict is a record            f   IteratorFile   0  t 1  t 2  t 3  t 4   format r  id            r  type            r  item            r  month            r  revenue    for r in records    To generalise for an arbitrary number of fields we will first create a line string with the correct amount of tabs and field placeholders       t   t       t    and then use  format   to fill in the field values     list r values     for r in records           line     t  join          len records 0             f   IteratorFile line format  list r values     for r in records    complete function in gist here

User · Answer

I built a program that inserts multiple lines to a server that was located in another city     I found out that using this method was about 10 times faster than executemany  In my case tup is a tuple containing about 2000 rows  It took about 10 seconds when using this method   args str       join cur mogrify    s  s  s  s  s  s  s  s  s    x  for x in tup  cur execute  INSERT INTO table VALUES     args str     and 2 minutes when using this method   cur executemany  INSERT INTO table VALUES  s  s  s  s  s  s  s  s  s    tup

User · Answer

Using aiopg - The snippet below works perfectly fine        items    10  11  12  13        group   1     tup     gid  pid  for pid in items      args str       join  str s  for s in tup         insert into group values  1  10    1  11    1  12    1  13      yield from cur execute  INSERT INTO group VALUES     args str

User · Answer

A snippet from Psycopg2 s tutorial page at Postgresql org  see bottom       A last item I would like to show you is how to insert multiple rows using a dictionary  If you had the following     namedict      first name   Joshua    last name   Drake                  first name   Steven    last name   Foo                  first name   David    last name   Bar         You could easily insert all three rows within the dictionary by using     cur   conn cursor   cur executemany    INSERT INTO bar first name last name  VALUES    first name s    last name s      namedict    It doesn t save much code  but it definitively looks better

User · Answer

Another nice and efficient approach - is to pass rows for insertion as 1 argument   which is array of json objects   E g  you passing argument      id  18  score  1     id  19  score  5      It is array  which may contain any amount of objects inside  Then your SQL looks like   INSERT INTO links  parent id  child id  score   SELECT 123   r- gt  gt  id    int   r- gt  gt  score    int  FROM unnest  1  json    as r    Notice  Your postgress must be new enough  to support json

User · Answer

executemany accept array of tuples  https   www postgresqltutorial com postgresql-python insert           array of tuples         vendor list     value1             insert multiple vendors into the vendors table          sql    INSERT INTO vendors vendor name  VALUES  s       conn   None     try            read database configuration         params   config             connect to the PostgreSQL database         conn   psycopg2 connect   params            create a new cursor         cur   conn cursor             execute the INSERT statement         cur executemany sql vendor list            commit the changes to the database         conn commit             close communication with the database         cur close       except  Exception  psycopg2 DatabaseError  as error          print error      finally          if conn is not None              conn close

User · Answer

Finally in SQLalchemy1 2 version  this new implementation is added to use psycopg2 extras execute batch   instead of executemany when you initialize your engine with use batch mode True like   engine   create engine       postgresql psycopg2   scott tiger host dbname       use batch mode True    http   docs sqlalchemy org en latest changelog migration 12 html change-4109  Then someone would have to use SQLalchmey won t bother to try different combinations of sqla and psycopg2 and direct SQL together

User · Answer

Update with psycopg2 2 7   The classic executemany   is about 60 times slower than  ant32  s implementation  called  folded   as explained in this thread  https   www postgresql org message-id 20170130215151 GA7081 40deb76 aryehleib com  This implementation was added to psycopg2 in version 2 7 and is called execute values     from psycopg2 extras import execute values execute values cur       INSERT INTO test  id  v1  v2  VALUES  s         1  2  3    4  5  6    7  8  9        Previous Answer   To insert multiple rows  using the multirow VALUES syntax with execute   is about 10x faster than using psycopg2 executemany    Indeed  executemany   just runs many individual INSERT statements    ant32  s code works perfectly in Python 2  But in Python 3  cursor mogrify   returns bytes  cursor execute   takes either bytes or strings  and     join   expects str instance   So in Python 3 you may need to modify  ant32  s code  by adding  decode  utf-8     args str       join cur mogrify    s  s  s  s  s  s  s  s  s    x  decode  utf-8   for x in tup  cur execute  INSERT INTO table VALUES     args str    Or by using bytes  with b   or b    only     args bytes   b    join cur mogrify    s  s  s  s  s  s  s  s  s    x  for x in tup  cur execute b INSERT INTO table VALUES     args bytes

User · Answer

I ve been using ant32 s answer above for several years  However I ve found that is thorws an error in python 3 because mogrify returns a byte string   Converting explicitly to bytse strings is a simple solution for making code python 3 compatible   args str   b    join cur mogrify    s  s  s  s  s  s  s  s  s    x  for x in tup   cur execute b INSERT INTO table VALUES     args str

User · Answer

All of these techniques are called  Extended Inserts  in Postgres terminology  and as of the 24th of November 2016  it s still a ton faster than psychopg2 s executemany   and all the other methods listed in this thread  which i tried before coming to this answer    Here s some code which doesnt use cur mogrify and is nice and simply to get your head around   valueSQL       s     s     s           as many as you have columns  sqlrows      rowsPerInsert   3   more means faster  but with diminishing returns   for row in getSomeData            row     1   a    yolo                 sqlrows    row         if   len sqlrows  len valueSQL      rowsPerInsert    0                    sqlrows      1   a    yolo   2   b    swag   3   c    selfie                    insertSQL    INSERT INTO  twitter  VALUES         join            join valueSQL         rowsPerInsert                  cur execute insertSQL  sqlrows                  con commit                   sqlrows      insertSQL    INSERT INTO  twitter  VALUES         join            join valueSQL         len sqlrows   cur execute insertSQL  sqlrows  con commit     But it should be noted that if you can use copy from    you should use copy from

User · Answer

If you want to insert multiple rows within one insert statemens  assuming you are not using ORM  the easiest way so far for me would be to use list of dictionaries  Here is an example    t      id  1   start date    2015-07-19 00 00 00    end date    2015-07-20 00 00 00    campaignid   6           id  2   start date    2015-07-19 00 00 00    end date    2015-07-20 00 00 00    campaignid   7           id  3   start date    2015-07-19 00 00 00    end date    2015-07-20 00 00 00    campaignid   8    conn execute  insert into campaign dates               id  start date  end date  campaignid                 values    id s    start date s    end date s    campaignid s                  t    As you can see only one query will be executed   INFO sqlalchemy engine base Engine insert into campaign dates  id  start date  end date  campaignid  values    id s    start date s    end date s    campaignid s   INFO sqlalchemy engine base Engine    campaignid   6   id   1   end date    2015-07-20 00 00 00    start date    2015-07-19 00 00 00      campaignid   7   id   2   end date    2015-07-20 00 00 00    start date    2015-07-19 00 00 00      campaignid   8   id   3   end date    2015-07-20 00 00 00    start date    2015-07-19 00 00 00    INFO sqlalchemy engine base Engine COMMIT

User · Answer

cursor copy from is the fastest solution I ve found for bulk inserts by far  Here s a gist I made containing a class named IteratorFile which allows an iterator yielding strings to be read like a file  We can convert each input record to a string using a generator expression  So the solution would be  args     1 2    3 4    5 6   f   IteratorFile      t    format x 0   x 1   for x in args   cursor copy from f   table name   columns   a    b      For this trivial size of args it won t make much of a speed difference  but I see big speedups when dealing with thousands  of rows  It will also be more memory efficient than building a giant query string  An iterator would only ever hold one input record in memory at a time  where at some point you ll run out of memory in your Python process or in Postgres by building the query string

User · Answer

execute batch  has been added to psycopg2 since this question was posted   It is slower than execute values but simpler to use

User · Answer

If you re using SQLAlchemy  you don t need to mess with hand-crafting the string because SQLAlchemy supports generating a multi-row VALUES clause for a single INSERT statement   rows      for i  name in enumerate rawdata       row              id   i           name   name           valid   True            rows append row  if len rows   gt  0     INSERT fails if no rows     insert query   SQLAlchemyModelName   table   insert   values rows      session execute insert query

[python] psycopg2: insert multiple rows with one query

Examples related to python

Examples related to postgresql

Examples related to psycopg2