How to Connect to SQL Databases from Python Using SQLAlchemy and Pandas

Extract SQL tables, insert, update, and delete rows in SQL databases through SQLAlchemy

Photo by Pascal Müller on Unsplash (Modify by Author)

In a data science project, we often need to interact with Relational databases, such as, extracting tables, inserting, updating, and deleting rows in SQL tables. To accomplish these tasks, Python has one such library, called SQLAlchemy. It supports popular SQL databases, such as PostgreSQL, MySQL, SQLite, Oracle, Microsoft SQL Server, and others. Even better, it has built-in functionalities, which can be integrated with Pandas. Together, SQLAlchemy and Pandas are a perfect match to handle data management.

Install Libraries

Besides SQLAlchemy and pandas, we would also need to install a SQL database adapter to implement Python Database API. For example, we need to install “psycopg2” or “pg8000” for PostgreSQL, “mysql-connector-python” or “oursql” for MySQL, “cx-Oracle” for Oracle SQL Database, “pyodbc” or “pymssql” for Microsoft SQL Server and others. In this article, I will discuss how to integrate PostgreSQL with Python, therefore, let’s install “psycopg2”.

Open the anaconda prompt or command prompt and type the following commands.

pip install SQLAlchemy
pip install pandas 
pip install psycopg2

Import Libraries

import sqlalchemy
import pandas as pd

Create Connection to the Database

First of all, let’s create a connection with the PostgreSQL database using “create_engine()” function based on a URL. A URL usually consists of dialect, driver, username, password, hostname, database name as well as optional arguments for additional configuration. The typical form of a database URL looks like “dialect+driver://username:password@host:port/database”.

For example,

Microsoft SQL Server: “mssql+pyodbc://username:password@host:port/database”
MySQL: “mysql+mysqlconnector://username:password@host:port/database”
PostgreSQL: “postgresql+psycopg2://username:password@host:port/database”

url = 'postgresql+psycopg2://username:password@host:port/database'
engine = sqlalchemy.create_engine(url)

We can also include optional arguments inside a “create_engine()” function. For example,

we can add “-csearch_path=schema_name” to override the current session’s search path in PostgreSQL. This is equivalent to writing a query, “SET search_path TO schema_name”.
isolation_level=”AUTOCOMMIT”: allows us to turn on the auto-commit feature. That means we don’t need to write additional codes, such as, “connection.commit()” and “connection.rollback()”. “connection.commit()” would commit any changes to the SQL database while “connection.rollback()” would discard any changes. For SQLAlchemy version ≤ 2.0, we can use “autocommit=True”, which is depreciated after 2.0.

engine = sqlalchemy.create_engine(url, connect_args={'options': '-csearch_path=schema_name'}, isolation_level="AUTOCOMMIT")

Run a SQL Query using SQLAlchemy

Once we create a connection, we can interact with the SQL database in Python. Let’s start with the simplest query, “SELECT * FROM table”.

from sqlalchemy.sql import text
sql = '''
    SELECT * FROM table;
'''
with engine.connect() as conn:
    query = conn.execute(text(sql))         
df = pd.DataFrame(query.fetchall())

There are a few key functions we will use.

text(): SQLAlchemy allows users to use the native SQL syntax within Python with the function, “text()”. It would pass a textual statement to the SQL database mostly unchanged. Therefore, we can use the native SQL syntax, such as, DELETE, UPDATE, INSERT, SELECT, Full-text Search and others, within a Python framework.
with engine.connect(): This function returns a SQL Connection object. By using it with a Python context manager (e.g., with statement), the “Connection.close()” function will be automatically involved at the end of the block of codes.
fetchall(): This function would return row objects, which can be integrated with Pandas to create a data frame.

In the following examples, we would UPDATE, INSERT, DELETE rows in a SQL table. The only difference from SELECT is we write “conn.execute(text(sql))” instead of “query = conn.execute(text(sql))” because we’re not extracting a table.

# Update rows in a SQL table
sql = '''
    UPDATE table 
    SET col='abc'
    WHERE condition;
'''
with engine.connect() as conn:
    conn.execute(text(sql))

# Insert new rows in a SQL table
sql = '''
    INSERT INTO df
    VALUES 
       (1, 'abc'),
       (2, 'xyz'),
       (1, 'abc');
'''
with engine.connect() as conn:
    conn.execute(text(sql))

# Delete rows in a SQL table
sql = '''
    DELETE FROM df
    WHERE condition;
'''
with engine.connect() as conn:
    conn.execute(text(sql))

Running SQL queries can be very flexible in Python. We can set up a for-loop to run multiple SQL queries based on different conditions. For example:

For i in [value_1, value_2, value_3, ...]:
 if condition_1:
  sql = '''sql_query_1'''
 elif condition_2:
  sql = '''sql_query_2'''
 else:
  sql = '''sql_query_3'''
  
 with engine.connect() as conn:
  conn.execute(text(sql))

Run Multiple SQL Queries

Running multiple SQL queries in a single block is also straightforward. We just need to separate statements with semicolons. The simple implementation with SQLAlchemy makes it easy to interact with SQL in Python.

sql = '''
    DROP TABLE IF EXISTS df;
    CREATE TABLE df(
            id SERIAL PRIMARY KEY,
            salary integer
    );
    INSERT INTO df (salary)
    VALUES 
            (400),
            (200),
            (3001);
    SELECT * FROM df;
'''
with engine.connect() as conn:
    query = conn.execute(text(sql))         
df = pd.DataFrame(query.fetchall())

Store SQL Table in a Pandas Data Frame Using “read_sql”

We’ve mentioned “fetchall()” function to save a SQL table in a pandas data frame. Alternatively, we can also achieve it using “pandas.read_sql”. Since SQLAlchemy is integrated with Pandas, we can use its SQL connection directly with “con = conn”.

with engine.connect() as conn:
    df = pd.read_sql('SELECT * FROM table_name WHERE condition', con = conn)

Insert DataFrame into an Existing SQL Database using “to_sql”

To insert new rows into an existing SQL database, we can use codes with the native SQL syntax, INSERT, mentioned above. Alternatively, we can use “pandas.DataFrame.to_sql” with an option of “ if_exists=‘append’ ” to bulk insert rows to a SQL database. One benefit of this method is we can take full advantage of Pandas functionalities, such as, importing external data files and transforming raw data. So we can have a Pandas DataFrame that is compatible (e.g., having the same columns and data types as the SQL table) and ready to be inserted into an existing SQL database.

df = pd.read_excel('sample.xlsx')
df.to_sql('table_name', con=engine, if_exists='append', index= False)

Create a New SQL Database using “to_sql”

“pandas.DataFrame.to_sql” also works on creating a new SQL database. As you can see from the following example, we import an external data from a excel spreadsheet and create a new SQL table from the pandas DataFrame.

from sqlalchemy.types import Integer, Text, String, DateTime

df = pd.read_excel('sample.xlsx')
df.to_sql(
    name = "table_name", 
    con = engine,
    if_exists = "replace",
    schema='shcema_name',   
    index=False,
    chunksize=1000,
    dtype={
       "col_1_name": Integer,
       "col_2_name": Text,
       "col_3_name": String(50),
       "col_4_name": DateTime
     }
)

There are a few important arguments we need to specify with “to_sql()” function in order to create a new SQL table properly.

name: It indicates the SQL table name that we write the new data into. It could be new SQL table name or the existing SQL table name.
con: it indicates the SQL connection engine we will be using.
if_exists: This argument would indicate what to do if a table with the name “table_name” already exists in the database. Passing “replace” would drop all rows in the existing table and replace it with the current pandas data frame. Passing “append”, as mentioned above, would only append the pandas data frame into the existing SQL table.
schema: This argument would take the schema name where you would save your new SQL table. It is not required if you’ve already specified the schema name in the connection.
index: This argument would indicate whether we would create a additional column (i.e., Index) in the new SQL table for DataFrame’s index. If we set the if_exists parameter to be “replace”, the code usually won’t have an error. But if we set the if_exists parameter to be “append”, the code might have an error (e.g., “Unknown column ‘index’ in ‘field list’”) because the Index column is not available in the existing SQL table. In this case, we need to specify “index=False” to resolve the error.
chuncksize: This argument would specify the number of rows in each batch to be inserted at a time. By default, all rows will be written at once.
dtype: This argument would specify the datatype of columns in the new SQL table. The datatypes we used are from sqlalchemy.types. If we set the if_exists parameter to be “replace”, the code usually won’t have an error because we will replace the existing SQL table. But if we set the if_exists parameter to be “append”, the code might have an error due to unmatched data types between the new SQL table the existing SQL table. In this case, we need to carefully specify the dtype argument so that we have inconsistent data types between the new SQL table the existing SQL table.

If you would like to explore more about how to work with SQL in Python, please check out my articles:

Thank you for reading!

If you enjoy this article, please click the Clap icon. If you would like to see more articles from me and thousands of other writers on Medium. You can:

Subscribe to my newsletter to get an email notification whenever I post a new article.
Sign up for a membership to unlock full access to everything on Medium.