Open In App

Bulk Insert to Pandas DataFrame Using SQLAlchemy – Python

Last Updated : 28 Dec, 2022
Improve
Improve
Like Article
Like
Save
Share
Report

Let’s start with SQLAlchemy, a Python library that allows communication with databases(MySQL, PostgreSQL etc.) and Python. This library is used as an Object Relational Mapper tool that translates Python classes to tables in relational databases and automatically converts function calls to SQL statements that gives application developers the full power and flexibility of SQL in the programming language communication with databases. SQLAlchemy provides an option that allows developers to create flexible ( format of data transmission is irrelevant or not in a format of data transmission) code to communicate with a different type of database engine. As an ORM tool, SQLAlchemy differs from most others in that it is rooted in a complementarity-oriented approach. The entire processes are exposed within a series of composable, transparent tools rather than being hidden behind a wall of automation.
Using the library, developers can remain in control by automating redundant tasks
of how the database is structured and how SQL is constructed.

Bulk Insert A Pandas DataFrame Using SQLAlchemy in Python

In this article, we will look at how to Bulk Insert A Pandas Data Frame Using SQLAlchemy and also a optimized approach for it as doing so directly with Pandas method is very slow.

Creating a connection and database using SQLAlchemy:

So first we will create an engine for connection with the database and further we can use to_sql() command to push the data with its constraints. we have used vs-code for inserting data frame using SQLAlchemy, you can use any other editor as well after installing the library.

Creating a connection with database using SQLAlchemy engine

Database Created from python in MySQL workbench

Creating a Data frame and inserting it to database using to_sql() function:

Inserting data frame into Database(MySQL)

Table inserted into database

Note :  “Use below sql command to see above results of sql” 

 

Bulk data Insert Pandas Data Frame Using SQLAlchemy:

We can perform this task by using a method “multi” which perform a batch insert by inserting multiple records at a time in a single INSERT statement. But effectiveness(time) for this method is very poor even compared from a default method. 
So to overcome this problem we have another method “callable” with signature(pd_table, conn, keys, data_iter) which is very faster than “multi” method. That can be easily noticed in the below image:

Optimized approach for inserting bulk data into data base using SQLAlchemy.

Code implementation:

Python




from sqlalchemy import event
import pandas as pd
import sqlalchemy
import pymysql
  
from sqlalchemy import create_engine
  
engine = create_engine("mysql+pymysql://" + \
                       "connection name" + ":" +\
                       "password" + "@" + "localhost" +
                       ":" + "3306" + "/" + "database name" \
                       + "?" + "charset=utf8mb4")
# Choose your configuration in the above code.
# replace connection name, password ,database name by yours.
  
conn = engine.connect()
  
dict = {
    # data to insert into data frame
    'id': [1, 2, 3], 'name': ["geeks", "for", "geeks"]
}
# storing data in df variable, hence creating dataframe.
df = pd.DataFrame(dict)
print(df)
# inserting data into database
df.to_sql("table_gfg", conn, if_exists="replace")
  
  
@event.listens_for(engine, "before_cursor_execute")
def receive_before_cursor_execute(conn, 
 cursor, statement, params, context, executemany):
    if executemany:
        cursor.fast_executemany = True
  
  
df.to_sql("table_gfg", conn, index=False, if_exists="append")


Output:

Data from python data frame is inserted into database



Similar Reads

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; [caption width="800"] [/caption]Create a conn
1 min read
SQLalchemy Bulk insert with one to one relation
When working with databases in Python, SQLAlchemy is a popular and powerful library that provides a high-level interface for interacting with relational databases. It offers an Object-Relational Mapping (ORM) layer that allows developers to work with database tables as Python objects. In this article, we will explore how to perform bulk inserts wit
5 min read
Create a SQL table from Pandas dataframe using SQLAlchemy
In this article, we will discuss how to create a SQL table from Pandas dataframe using SQLAlchemy. As the first steps establish a connection with your existing database, using the create_engine() function of SQLAlchemy. Syntax: from sqlalchemy import create_engine engine = create_engine(dialect+driver://username:password@host:port/database) Explana
3 min read
Read SQL database table into a Pandas DataFrame using SQLAlchemy
To read sql table into a DataFrame using only the table name, without executing any query we use read_sql_table() method in Pandas. This function does not support DBAPI connections. read_sql_table()Syntax : pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None) Paramete
2 min read
SQLAlchemy ORM conversion to Pandas DataFrame
In this article, we will see how to convert an SQLAlchemy ORM to Pandas DataFrame using Python. We need to have the sqlalchemy as well as the pandas library installed in the python environment - $ pip install sqlalchemy $ pip install pandasFor our example, we will make use of the MySQL database where we have already created a table named students.
4 min read
Using Sqlalchemy to insert MySQL Timestamp Column Values
This article is about how we can add a timestamp column with values in MYSQL along with other data in an SQL database. Timestamp is quite useful as it provides the time when that particular entity was created in the database. Here we will use SQLAlchemy as it is a popular Python programming SQL toolkit and ORM (Object Relational Mapper) that gives
3 min read
Python | Pandas dataframe.insert()
Pandas insert method allows the user to insert a column in a data frame or series(1-D Data frame). A column can also be inserted manually in a data frame by the following method, but there isn't much freedom here. For example, even column location can't be decided and hence the inserted column is always inserted in the last position in Python. Pand
8 min read
Sqlalchemy core, insert multiple rows from a tuple instead of dict
SQLAlchemy is a popular Python library used for working with databases. SQLAlchemy provides an Object-Relational Mapping (ORM) layer and a Core layer. The ORM layer allows developers to work with databases using Python objects, while the Core layer provides a lower-level interface for SQL-oriented database work. In this article, we'll explore how t
3 min read
How to insert NULL value in SQLAlchemy?
In this article, we will see how to insert NULL values into a PostgreSQL database using SQLAlchemy in Python. For demonstration purposes first, let us create a sample table using SQLAlchemy as shown below Creating a table using SQLAlchmey in PostgreSQL:Import necessary functions from SQLAlchemy package.Establish connection with the PostgreSQL datab
2 min read
Bulk Posting on Facebook Pages using Selenium
As we are aware that there are multiple tasks in the marketing agencies which are happening manually, and one of those tasks is bulk posting on several Facebook pages, which is very time-consuming and sometimes very tedious to do. In this project-based article, we are going to explore a solution that is on the python selenium library and see how we
9 min read
Practice Tags :