Import Multiple SQL Tables using Pandas read_sql

In this tutorial, you’ll learn how to import multiple SQL tables into DataFrames using Pandas read_sql.

You’ll learn how to connect to the database using SQLAlchemy, retrieve table names, and execute custom SQL queries to import tables.

 

 

Connecting to the SQL Database Using SQLAlchemy

Before you can import SQL tables into Pandas, establishing a connection to your SQL database is essential.

SQLAlchemy, a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python, enables you to interact with your database in a more Pythonic way.

Here’s how you can set up a connection using SQLAlchemy:

from sqlalchemy import create_engine
DATABASE_TYPE = 'postgresql'
DBAPI = 'psycopg2'
ENDPOINT = 'your-db-endpoint'  # e.g., 'localhost'
USER = 'your-username'
PASSWORD = 'your-password'
PORT = 5432
DATABASE = 'your-database-name'

# A string that contains the database connection information
DATABASE_URL = f"{DATABASE_TYPE}+{DBAPI}://{USER}:{PASSWORD}@{ENDPOINT}:{PORT}/{DATABASE}"
engine = create_engine(DATABASE_URL)

Output:

Engine(postgresql+psycopg2://your-username:***@your-db-endpoint:5432/your-database-name)

The code snippet above creates an engine object that represents the core interface to the database. This object maintains a pool of connections ready for use whenever you execute a query.

It’s a good practice to keep these credentials secure and not hard-coded in your scripts.

 

Retrieving Table Names from the Database

Once the connection to your SQL database is established, the next step is to query the database to fetch the names of the tables available for selection.

Pandas does not directly provide a function to list out tables, but SQLAlchemy’s engine connection can be used to retrieve this information. Here’s how you can list all the available tables in your database:

with engine.connect() as connection:
    available_tables = connection.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
    table_names = [table_name[0] for table_name in available_tables]

Output:

['table1', 'table2', 'table3', ...]

The SQL command passed to connection.execute() retrieves the names of all tables within the ‘public’ schema of your database, which is a default schema in PostgreSQL.

 

Reading Multiple Tables Individually

To import multiple tables into Pandas, you’ll often iterate through table names and read each one separately.

Here’s how to read each table individually using a for loop:

import pandas as pd
dataframes = {}

# Iterating over each table name and reading them into separate DataFrames
for table_name in table_names:
    query = f"SELECT * FROM {table_name}"
    dataframes[table_name] = pd.read_sql(query, engine)
print(dataframes)

Output:

{
    'table1': DataFrame_object_table1,
    'table2': DataFrame_object_table2,
    'table3': DataFrame_object_table3,
    ...
}

In this approach, you loop over the table_names list and execute a SQL query to select all columns from each table.

The read_sql function then executes the query using the connection from the SQLAlchemy engine, returning a DataFrame for each table.

 

Customizing the Import Process

Perhaps you only need a subset of data or you want to perform some preprocessing steps directly within the SQL query.

Here’s how you can specify custom SQL queries for each table:

custom_queries = {
    'table1': "SELECT column1, column2 FROM table1 WHERE condition",
    'table2': "SELECT column3, column4 FROM table2",
}
custom_dataframes = {}
for table_name, query in custom_queries.items():
    custom_dataframes[table_name] = pd.read_sql(query, engine)
print(custom_dataframes)

Output:

{
    'table1': DataFrame_object_with_custom_query_table1,
    'table2': DataFrame_object_with_custom_query_table2,
    ...
}

In this code block, you define a custom_queries dictionary where each key-value pair corresponds to a table name and its associated custom SQL query.

 

Joining Tables in SQL vs. Pandas

When dealing with multiple related tables, you may need to join them to create a comprehensive dataset.

You can perform this join operation either directly in SQL before importing the data or in Pandas after importing the tables as separate DataFrames.

Joining in SQL

Pros:

• SQL databases are optimized for join operations, which can be more efficient, especially with large datasets.
• Reduces the amount of data to be imported into Pandas, which can be beneficial if you’re working with limited memory resources.

Cons:

• Requires a deep understanding of SQL to execute complex joins or to handle database-specific join behaviors.

Techniques for Merging in Pandas

Pros:

• Offers more flexibility as you can easily modify the join conditions and parameters without needing to run a new SQL query.
• Pandas provides a straightforward syntax for joins, which may be more familiar to Python users than SQL syntax.
• Easier to debug and iterate over, as the data is already in the Python environment.

Cons:

• Requires that all the data be loaded into Pandas first, which can be memory-intensive.

Here’s how to merge two DataFrames in Pandas:

# Assuming 'table1' and 'table2' are related and have been imported as DataFrames
merged_dataframe = pd.merge(dataframes['table1'], dataframes['table2'], on='common_column', how='inner')

Output:

  common_column  column1_from_table1  column2_from_table1  column3_from_table2  column4_from_table2
0        common_value              data1                data2                data3                data4
...

In this example, you use Pandas’ merge function to join two DataFrames on a common column.

The how parameter specifies the type of join: ‘inner’, ‘left’, ‘right’, or ‘outer’.

 

Using SQL Queries to Import Multiple Tables

You can import multiple related tables as a single DataFrame by performing JOIN operations within SQL.

Here’s how you can use SQL JOIN operations to import multiple tables:

join_query = """
SELECT *
FROM table1
JOIN table2 ON table1.common_column = table2.common_column
JOIN table3 ON table1.other_common_column = table3.another_common_column
"""

joined_dataframe = pd.read_sql(join_query, engine)

Output:

  common_column  column1_from_table1  column2_from_table1  column1_from_table2  column2_from_table3  ...
0        common_value              data1                data2                data3                data4  ...
...

This SQL query performs a JOIN operation across multiple tables based on common columns.

The result is imported into a single Pandas DataFrame using the read_sql function.