Read SQL Database Table into a Pandas DataFrame using SQLAlchemy

read_sql_table() is a Pandas function used to load an entire SQL database table into a Pandas DataFrame using SQLAlchemy. It allows you to access table data in Python by providing only the table name and database connection, without writing any SQL query.

Example: This example creates a small SQLite database, inserts data into a table and then reads that table into a Pandas DataFrame.

import pandas as pd
from sqlalchemy import create_engine, text

db = create_engine("sqlite:///data.db")

with db.begin() as con:
    con.execute(text("CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT)"))
    con.execute(text("DELETE FROM users"))  # avoid duplicates
    con.execute(text("INSERT INTO users VALUES (1,'John'),(2,'Emma')"))

df = pd.read_sql_table("users", db)
print(df)

Output

Explanation:

• create_engine("sqlite:///data.db") creates a connection to the SQLite database file.
• db.begin() opens a transaction so that inserted data is saved (committed).
• con.execute(text("CREATE TABLE...")) creates the users table if it does not exist.
• con.execute(text("INSERT INTO users...")) adds two rows into the users table.
• pd.read_sql_table("users", db) reads the full users table into a DataFrame.

Syntax

pd.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

Parameters:

• table_name: name of the SQL table to read
• con: SQLAlchemy engine or connection
• schema (optional): database schema
• index_col: column(s) to use as index
• coerce_float: convert numeric-like values to float
• parse_dates: columns to convert to datetime
• columns: columns to select
• chunksize: rows per chunk

Examples

Example 1: This program creates a contacts table, inserts two records, and loads the table into a Pandas DataFrame.

import pandas as pd
from sqlalchemy import create_engine, text

db = create_engine("sqlite:///contacts.db")

with db.begin() as con:
    con.execute(text("CREATE TABLE IF NOT EXISTS contacts (id INTEGER, name TEXT, email TEXT)"))
    con.execute(text("INSERT INTO contacts VALUES (1,'Liam','liam@gmail.com'),(2,'Sophia','sophia@gmail.com')"))

df = pd.read_sql_table("contacts", db)
print(df)

Output:

Explanation: pd.read_sql_table("contacts", db) loads all rows from the contacts table into the DataFrame df.

Example 2: This program stores student records in a database and reads them into a DataFrame.

import pandas as pd
from sqlalchemy import create_engine, text

db = create_engine("sqlite:///students.db")

with db.begin() as con:
    con.execute(text("CREATE TABLE IF NOT EXISTS students (id INTEGER, name TEXT, marks INTEGER)"))
    con.execute(text("INSERT INTO students VALUES (1,'Oliver',85),(2,'Mia',90)"))

df = pd.read_sql_table("students", db)
print(df)

Output:

Explanation: pd.read_sql_table("students", db) reads the full students table and stores it in df.

Example 3: This example creates an employee table and retrieves it using read_sql_table().

import pandas as pd
from sqlalchemy import create_engine, text

db = create_engine("sqlite:///employee.db")

with db.begin() as con:
    con.execute(text("CREATE TABLE IF NOT EXISTS employee (id INTEGER, name TEXT, salary INTEGER)"))
    con.execute(text("INSERT INTO employee VALUES (1,'Noah',50000),(2,'Ava',60000)"))

df = pd.read_sql_table("employee", db)
print(df)

Output:

Explanation: pd.read_sql_table("employee", db) fetches the entire employee table and stores it in df.