Create a SQL table from Pandas dataframe using SQLAlchemy
Last Updated : 26 Jan, 2022
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)
Explanation:
- dialect - Name of the DBMS
- driver - Name of the DB API that moves information between SQLAlchemy and the database.
- Username, Password - DB User credentials
- host: port - Specify the type of host and port number.
- Database - Database name
Example:
Python3 engine = create_engine( 'postgresql+psycopg2://scott:tiger@localhost:5432/mydatabase')
The above example creates a Dialect object specific to PostgreSQL, and a Pool object which establishes a DBAPI connection at localhost:5432 when a connection request is received.
SQLAlchemy includes many Dialect implementations for the most common databases like Oracle, MS SQL, PostgreSQL, SQLite, MySQL, and so on. To load the dataframe to any database, SQLAlchemy provides a function called to_sql().
Syntax: pandas.DataFrame.to_sql(table_name, engine_name, if_exists, schema, index, chunksize, dtype)
Explanation:
- table_name - Name in which the table has to be stored
- engine_name - Name of the engine which is connected to the database
- if_exists - By default, pandas throws an error if the table_name already exists. Use 'REPLACE' to replace this dataset with the old one or "APPEND" to add the data to the existing table.
- index - (bool), Adds index column to the table that identifies each row uniquely.
For this example, we can use an inbuilt, in-memory-only SQLite database, which is one of the easiest ways to test things, but then the procedure is just the same for all the other databases supported by SQLAlchemy. You can download the sample dataset here.
Let us first Import the necessary dataset. Now, let's Establish the connection with an in-memory-only SQLite database and make it interactable to python using pysqlite driver. Next, we shall load the dataframe to be pushed to our SQLite database using the to_sql() function as shown.
Python3 # import the necessary packages import pandas from sqlalchemy import create_engine # Create the engine to connect to the inbuilt # sqllite database engine = create_engine("sqlite+pysqlite:///:memory:") # Read data from CSV which will be # loaded as a dataframe object data = pandas.read_csv('superstore.csv') # print the sample of a dataframe data.head() # Write data into the table in sqllite database data.to_sql('loan_data', engine) Output:
output In order to check whether the dataframe is uploaded as a table, we can query the table using SQLAlchemy as shown below,
Python3 from sqlalchemy import text # establish the connection with the engine object with engine.connect() as conn: # let's select the column credit_history # from the loan data table result = conn.execute(text("SELECT Credit_History FROM loan_data")) # print the result for row in result: print(row.Credit_History) Output:
Similar Reads
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_
2 min read
Bulk Insert to Pandas DataFrame Using SQLAlchemy - Python 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 statem
3 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 wher
4 min read
Connecting to SQL Database using SQLAlchemy in Python In this article, we will see how to connect to an SQL database using SQLAlchemy in Python. To connect to a SQL database using SQLAlchemy we will require the sqlalchemy library installed in our python environment. It can be installed using pip - !pip install sqlalchemyThe create_engine() method of sq
3 min read
Sqlalchemy Core With Text SQL For Date Range SQLAlchemy Core is a low-level SQL abstraction layer of SQLAlchemy, a popular Python Object Oriented Mapping(ORM) library. It provides a way to interact with relational databases wing python code, allowing developers to write SQL Queries in a more object-oriented manner. SQLAlchemy is a python libra
2 min read