Working with relational databases, it is often the requirement to write and execute raw queries in SQL language for the extraction of data, and save the results returned in the form of arrays or data frames. There comes an extremely handy python package by the name of “SQLAlchemy” that provides an effective way to connect with the common databases including PostgreSQL, MySQL, and Oracle. Using SQLAlchemy, we can transform the data in python to perform analysis to streamline the flow of data.

• Installing the SQL package

To install the SQLAlchemy package, you need to execute the following command on your python terminal:

• pip install sqlalchemy
• Connecting to the Database

The “Engine” is the starting point for any SQLAlchemy application. It’s “home base” for the actual database and it’s DBAPI, delivered to the SQLAlchemy application through a connection pool and a Dialect, which describes how to talk to a specific kind of database/DBAPI combination.

An Engine references both a Dialect and a Pool, which together interpret the DBAPI’s module functions as well as the behavior of the database.

We can create an engine by calling the create_engine() function:

from sqlalchemy import create_engine

engine = create_engine(‘postgresql://username:password@localhost:port_number/database_name’)

connection = engine.connect()

After successful connection with the database using the create_engine() function, we can execute different SQL queries using sqlalchemy functions to extract data from the database in use using the connection.execute() function:

query = “Select * tablename”

results = connection.execute(query)

We can also move in the reverse order by dumping a pandas data frame into a table of our relational database using the established function in the following way:

import pandas as pd

data = {“Column_1”:[1,2,3,4],”Column2”:[“a”,”b”,”c”,”d”]}

df = pd.DataFrame(data)

df.to_sql(“Table_Name”,if_exists = “replace”)

Here using the “df.to_sql()” function allows to store columns within the dataframe with their respective data types inside the database table without specifically creating a new table and specifying their respective data types.

SQLAlchemy provides the application developers with full power and flexibility of SQL in Python. The package is a full suite of well-known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into simple python language.