Search code examples
pythondatabasepostgresqlponyorm

Python Pony ORM Insert multiple values at once


I'm trying to insert multiple values into my postgres database using Pony ORM. My current approach is very inefficient:

from pony.orm import *

db = Database()

class Names(db.Entity):
    first_name = Optional(str)
    last_name = Optional(str)


family = [["Peter", "Mueller"], ["Paul", "Meyer"], ...] 


@db_session
def populate_names(name_list)
    for name in name_list:
        db.insert("Names", first_name=name[0], last_name=name[1])


if __name__ == "__main__":
    db.bind(provider='postgres', user='', password='', host='', database='')
    db.generate_mappings(create_tables=True)
    populate_names(family)

This is just a short example but the structure of the input is the same: a list of lists. I'm extracting the data from several xml files and insert one "file" at a time.

Does anyone has an idea on how to put several rows of data into one insert query in Pony ORM?


Solution

  • Pony doesn't provide something special for this, you can use execute_values from psycopg2.extras. Get connection object from db to use it.

    from psycopg2.extras import execute_values
    ...
    names = [
        ('はると', '一温'),
        ('りく', '俐空'),
        ('はる', '和晴'),
        ('ひなた', '向日'),
        ('ゆうと', '佑篤')
    ]
    
    @db_session
    def populate_persons(names):
        sql = 'insert into Person(first_name, last_name) values %s'
        con = db.get_connection()
        cur = con.cursor()
        execute_values(cur, sql, names)
    
    populate_persons(names)
    

    execute_values is in Fast execution helpers list so I think that iе should be the most efficient way.