Tukukkk

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)

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

1. 
   

   the values syntax of the insert clause expects a list of records as in

   
   
   

   insert into t (a, b) values (1, 'x'),(2, 'y')

   
   



2. 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')

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) 

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.

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.

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 ;)

To nicely insert into DB a list of rows, using user given batch size and with psycopg2 !

def get_batch(iterable, size=100):

    for i in range(0, len(iterable), size):

        yield iterable[i: i + size]





def insert_rows_batch(table, rows, batch_size=500, target_fields=None):

    """

    A utility method to insert batch of tuples(rows) into a table

    NOTE: Handle data type for fields in rows yourself as per your table 

    columns' type.



    :param table: Name of the target table

    :type table: str



    :param rows: The rows to insert into the table

    :type rows: iterable of tuples



    :param batch_size: The size of batch of rows to insert at a time

    :type batch_size: int



    :param target_fields: The names of the columns to fill in the table

    :type target_fields: iterable of strings

    """

    conn = cur = None

    if target_fields:

        target_fields = ", ".join(target_fields)

        target_fields = "({})".format(target_fields)

    else:

        target_fields = ''



    conn = get_conn() # get connection using psycopg2

    if conn:

        cur = conn.cursor()

    count = 0



    for mini_batch in get_batch(rows, batch_size):

        mini_batch_size = len(mini_batch)

        count += mini_batch_size

        record_template = ','.join(["%s"] * mini_batch_size)

        sql = "INSERT INTO {0} {1} VALUES {2};".format(

            table,

            target_fields,

            record_template)

        cur.execute(sql, mini_batch)

        conn.commit()

        print("Loaded {} rows into {} so far".format(count, table))

    print("Done loading. Loaded a total of {} rows".format(count))

    if cur:cur.close()

    if conn:conn.close()

If you want UPSERT (Insert+Update) as well in postgres with batches: postgres_utilities

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->>'id')::int, (r->>'score')::int 

FROM unnest($1::json) as r 

Notice: Your postgress must be new enough, to support json

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)

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) > 0:  # INSERT fails if no rows

    insert_query = SQLAlchemyModelName.__table__.insert().values(rows)

    session.execute(insert_query)

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

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..