如何使用Python将本地SQL数据库中的所有表分别存储为DataFrame？

Great question! You absolutely can automate pulling every table from your local SQLite database into individual DataFrames (or a structured collection) without writing repetitive SELECT queries for each table. Here's a clean, efficient way to do it:

Step-by-Step Solution #

First, we'll use a dictionary to store each DataFrame—this is the most organized approach, as it lets you reference tables by their original names easily.

1. Set Up the Database Connection #

Start with your existing setup to connect to the SQLite database:

import pandas as pd
from sqlalchemy import create_engine

# Connect to your local SQLite database
engine = create_engine('sqlite:///sql_database_name.sqlite')

2. Fetch All Table Names #

Retrieve the list of all tables in the database. Note that for newer SQLAlchemy versions (v1.4+), engine.table_names() is deprecated—use the updated method instead:

# Get all table names (compatible with most SQLAlchemy versions)
try:
    table_names = engine.table_names()
except AttributeError:
    # For SQLAlchemy v1.4+
    table_names = list(engine.get_table_names())

print("Tables found in database:", table_names)

3. Load All Tables into DataFrames #

Loop through each table name, read it into a DataFrame, and store it in the dictionary:

# Initialize an empty dictionary to hold your DataFrames
table_dataframes = {}

for table in table_names:
    # Use pandas' built-in read_sql_table for simplicity
    table_dataframes[table] = pd.read_sql_table(table, engine)
    
    # If you prefer to stick with your original execute/fetchall approach:
    # rs = engine.execute(f'SELECT * FROM {table}')
    # table_dataframes[table] = pd.DataFrame(rs.fetchall(), columns=rs.keys())

4. Access Your DataFrames #

Now you can access any table's DataFrame using the table name as the dictionary key:

# Example: Access the 'customers' table DataFrame
print(table_dataframes['customers'].head())

Optional: Create Individual Variables (Not Recommended) #

If you really want separate variables for each table (instead of a dictionary), you can use globals(), but this is not ideal for databases with many tables (it clogs your namespace):

for table in table_names:
    # Creates variables like df_customers, df_orders, etc.
    globals()[f'df_{table}'] = pd.read_sql_table(table, engine)

Key Notes #

• This method works for any database supported by SQLAlchemy (PostgreSQL, MySQL, etc.)—just adjust the connection string in create_engine().
• Using a dictionary keeps your code clean, makes it easy to iterate over all tables later, and avoids naming conflicts.

内容的提问来源于stack exchange，提问作者Walter White