Bulk insert with SQLAlchemy ORM in Python

Last Updated : 28 Apr, 2025

In this article, we will see how to insert or add bulk data using SQLAlchemy in Python.

SQLAlchemy is among one of the best libraries to establish communication between python and databases. We have used the PostgreSQL database for this article.

Create a database for demonstration:

CREATE DATABASE TEST;

Create a connection to the Postgres database

Python3

from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2:/\
/postgres:root@localhost:5432/test",echo=True)

Create a Table Student

Python3

from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()

students = Table('students', meta,
                 Column('id', Integer, primary_key = True),
                 Column('name', String),
                 Column('lastname', String),
                )
meta.create_all(engine)

Output:

Now, login to the database "test" and check the table name "students" created.

BULK INSERT Bulk insert with SQLAlchemy ORM

In PostgreSQL, we can add bulk data into tables using COPY COMMAND, IMPORT, and through generate_series.

generate_series

Python3

engine.execute("INSERT INTO students (id, name, lastname)\
               SELECT gt,'Scott Derrickson','Derrickson'\ 
               FROM generate_series(4,10) as gt")

Output:

Copy command

Python3

engine.execute("COPY students(id,name,lastname)\ 
               FROM 'PATH'  DELIMITER ',' CSV HEADER")

Output:

Bulk insert with SQLAlchemy ORM in Python

nidhixpcd

Improve

Article Tags :

Practice Tags :

python

Bulk insert with SQLAlchemy ORM in Python

Create a database for demonstration:

Create a connection to the Postgres database

Create a Table Student

BULK INSERT Bulk insert with SQLAlchemy ORM

generate_series

Copy command

Similar Reads

Thank You!

What kind of Experience do you want to share?