How to read and write SQL data with pandas

Reading and writing SQL data with pandas lets a Python analysis move rows between a database table and a DataFrame without exporting an intermediate CSV file. It fits small reporting scripts, validation jobs, and data handoffs where SQL remains the storage layer and pandas handles filtering or shaping.

The read_sql() function loads a SQL query or, with a SQLAlchemy connection, a table name into a DataFrame. DataFrame.to_sql() writes rows back to a database table and can create, append to, or replace an existing table depending on if_exists.

A local SQLite database keeps the database disposable while SQLAlchemy keeps the connection pattern close to PostgreSQL, MySQL, and other supported engines. Keep user-supplied values in bound query parameters, and treat table names plus if_exists behavior as controlled application settings.

1. Install SQLAlchemy if the active Python environment does not already have it.

   $ python3 -m pip install SQLAlchemy

   SQLite is included with Python, but SQLAlchemy gives pandas the same connection style used for PostgreSQL, MySQL, and other database engines.
   Related: How to install pandas with pip

2. Create a SQL round-trip script.

   sql_roundtrip.py

   from pathlib import Path
    
   import pandas as pd
   from sqlalchemy import create_engine, text
    
    
   database = Path("orders.db")
   engine = create_engine(f"sqlite:///{database}")
    
   orders = pd.DataFrame(
       {
           "order_id": ["A100", "A101", "A102"],
           "customer": ["Ada", "Lin", "Mira"],
           "status": ["open", "closed", "open"],
           "total_usd": [125.50, 88.00, 212.25],
       }
   )
    
   with engine.begin() as connection:
       orders.to_sql(
           "orders",
           connection,
           if_exists="replace",
           index=False,
       )
    
   with engine.connect() as connection:
       table = pd.read_sql("orders", connection)
       open_orders = pd.read_sql(
           text(
               "SELECT order_id, customer, total_usd "
               "FROM orders "
               "WHERE status = :status "
               "ORDER BY order_id"
           ),
           connection,
           params={"status": "open"},
       )
       row_count = connection.execute(text("SELECT COUNT(*) FROM orders")).scalar_one()
    
   print(open_orders.to_string(index=False))
   print()
   print(open_orders.dtypes)
   print()
   print(f"table columns: {', '.join(table.columns)}")
   print(f"database rows: {row_count}")
   print(f"query rows: {len(open_orders)}")
   print(f"order IDs match: {open_orders['order_id'].tolist() == ['A100', 'A102']}")

   if_exists=“replace” drops the existing table before inserting rows. Use append or another controlled loading path when the target table already contains data that must remain.

3. Run the script to write the table and read a filtered query back into pandas.

   $ python3 sql_roundtrip.py
   order_id customer  total_usd
       A100      Ada     125.50
       A102     Mira     212.25
   
   order_id         str
   customer         str
   total_usd    float64
   dtype: object
   
   table columns: order_id, customer, status, total_usd
   database rows: 3
   query rows: 2
   order IDs match: True

   The database row count includes the closed row that the filtered query omitted. Matching order IDs confirm that read_sql() returned the expected subset after to_sql() wrote the table.

4. Remove the sample database and script when the round trip is confirmed.

   $ rm orders.db sql_roundtrip.py