Bulk insert of list of dictionary

Question

I have defined my model class like below:

class MedicalPlan(Base):
    __tablename__ = "medical_plans"

    id = Column(Integer, nullable=False , primary_key=True)
    issuer_id = Column(Integer, ForeignKey('issuers.id'), nullable=False)
    service_area_id = Column(Integer).... and so 

I have created a session using below code using which I am doing most of my sql operations:

def get_session():
    engine = create_engine('postgresql+psycopg2://postgres:postgres@localhost/db')
    Session = sessionmaker(engine)
    return Session()

I have my list of dictionary which I want to insert as a bulk insert.

In documentation, I can see where I can insert using something like below:

connection.execute(table.insert(), [ 
        {'id':'12','name':'a','lang':'eng'},
        {'id':'13','name':'b','lang':'eng'},
        {'id':'14','name':'c','lang':'eng'},
    ]
)

My question is how can I perform such operation using my model class in the way I have provided the mapping.

Something like this does not work:

conn.execute(MedicalPlans.insert(), list_of_dict)

Answer 1

The table of the mapped class should be available as

MyTable.__table__

hence MedicalPlan.__table__.insert() should work.

---

There are other options in the question linked to by Ilja, but most are not as efficient - bulk_insert_mappings would come close.