How to Load Data into SQL Server in an ETL (pandas)

The Load step is the final stage of an ETL process: after extracting and transforming your data, you need to write it into a destination table. The most common way to load data into SQL Server from Python is to write a pandas DataFrame with the to_sql method and a connection created with SQLAlchemy. This guide shows you, step by step and with a simple example, how to do it reliably.

Prerequisites

• Python 3 installed and a basic knowledge of pandas.
• A reachable SQL Server database and permissions to create or write to a table.
• The ODBC Driver for SQL Server (for example, Driver 18) installed on your machine.
• The pandas, SQLAlchemy and pyodbc libraries.

Step 1: Install the libraries

Start by installing the three libraries you need. pyodbc is the driver that connects Python to SQL Server, and SQLAlchemy handles the connection on top of it. Using SQLAlchemy is the approach recommended by pandas, because it simplifies managing the connection.

pip install pandas sqlalchemy pyodbc

Step 2: Prepare the transformed DataFrame

Imagine you have finished the transformation step and your data is ready in a DataFrame. For this example, we will create a small, already-clean sales dataset.

import pandas as pd

df = pd.DataFrame({
    "id_venda": [1, 2, 3],
    "produto": ["Teclado", "Rato", "Monitor"],
    "valor": [29.90, 15.50, 189.00],
})
print(df)

Note that the pandas index (the 0, 1, 2 numbers on the left) is not part of your data; in the load step we will ignore it.

Step 3: Create the connection to SQL Server

With SQLAlchemy, the connection is defined by a connection string. Create an engine, which represents the connection to the destination database.

from sqlalchemy import create_engine

url = (
    "mssql+pyodbc://user:password@server/database"
    "?driver=ODBC+Driver+18+for+SQL+Server"
)
engine = create_engine(url, fast_executemany=True)

Replace user, password, server and database with your own values. The fast_executemany=True option greatly speeds up inserting many rows into SQL Server. Good practice: do not leave the password written in the code — read it from an environment variable (for example, with os.environ) and build the connection string from there.

Step 4: Load the data with to_sql

Now you just write the DataFrame into the destination table. The to_sql method creates the table (if it does not exist yet) and inserts the rows.

df.to_sql(
    "vendas",
    con=engine,
    schema="dbo",
    if_exists="append",
    index=False,
    chunksize=1000,
)

It is worth understanding each parameter:

• if_exists: "append" adds to the existing rows; "replace" drops and recreates the table; "fail" raises an error if the table already exists.
• index=False: avoids writing the pandas index as an extra column.
• chunksize=1000: sends the rows in batches of 1000, which helps when the volume is large.

By default, pandas lets SQLAlchemy choose the column types. If you need control, use the dtype parameter to set the SQL type of each column. Also note that, on SQL Server, you should speed up the load with fast_executemany=True on the engine (Step 3) rather than method="multi", which can hit the parameter limit.

Step 5: Wrap the load in a function

In a real ETL, it is a good idea to isolate the load in a function so you can reuse and test it easily.

def carregar(df, tabela, engine):
    df.to_sql(tabela, con=engine, schema="dbo",
              if_exists="append", index=False, chunksize=1000)
    print(f"Carregadas {len(df)} linhas para {tabela}.")

carregar(df, "vendas", engine)

Verify the result

To confirm the data landed in the table, read it back with pandas and count the rows.

total = pd.read_sql("SELECT COUNT(*) AS n FROM dbo.vendas", engine)
print(total)

If the number matches the rows you loaded, the Load step went well. Alternatively, run SELECT TOP 5 * FROM dbo.vendas in SQL Server Management Studio to see the first records.

Conclusion

You can now load a DataFrame into SQL Server and, with that, close the Extract–Transform–Load cycle in Python. From here, you can schedule the script, add error handling, and swap if_exists="append" for an upsert strategy when you need to update records instead of only inserting them. Which part of your ETL will you automate next?