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:
Contact Us