Bulk insert with SQLAlchemy ORM in Python

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: