Open In App

Bulk insert with SQLAlchemy ORM in Python

Improve
Improve
Like Article
Like
Save
Share
Report

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:

 

 



Last Updated : 05 Feb, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads