Export Pandas DataFrame to SQL Server using to_sql()

In this tutorial, you’ll learn how to export Python’s Pandas DataFrame to SQL Server using to_sql function and pyodbc module.

You’ll learn how to:

1. Set up a connection to a SQL Server database using pyodbc.
2. Convert a Pandas DataFrame to a format suitable for SQL operations.
3. Use the to_sql function to transfer data from a DataFrame to a SQL Server database.

 

 

Connecting to SQL Server with pyodbc

You can use pyodbc to establish a connection between your Python environment and a SQL Server database.

First, ensure you have pyodbc installed. If not, you can install it using pip:

!pip install pyodbc

Next, let’s import pyodbc and create a connection object. This object will serve as the bridge between your Python code and the SQL Server database.

import pyodbc
server = 'your_server_name'
database = 'your_database_name'
username = 'your_username'
password = 'your_password'
connection_string = f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'
conn = pyodbc.connect(connection_string)

 

Writing DataFrames to SQL Server: Using to_sql()

After establishing a connection with SQL Server, the next step is to transfer data from a Pandas DataFrame to the SQL Server database using to_sql() method.

Let’s consider a sample DataFrame that you might want to write to your SQL Server database.

import pandas as pd
data = {
    'CustomerID': [1001, 1002, 1003],
    'Name': ['Customer A', 'Customer B', 'Customer C'],
    'Plan': ['Basic', 'Premium', 'Standard'],
    'MonthlyCharge': [20, 50, 35]
}
df = pd.DataFrame(data)
print(df)

Output:

   CustomerID        Name      Plan  MonthlyCharge
0        1001  Customer A     Basic             20
1        1002  Customer B   Premium             50
2        1003  Customer C  Standard             35

Now, let’s write this DataFrame to your SQL Server database using to_sql():

df.to_sql(name='Customers', con=conn, if_exists='replace', index=False)

 

Handling Schema Differences

When working with DataFrames and SQL databases, one common challenge is managing schema differences between the DataFrame and the SQL table.

The schema includes aspects like column names, data types, and constraints.

To handle schema differences, you can take the following steps:

1. Inspect the existing table schema: Before transferring data, understand the schema of the target SQL table. This includes column names, data types, and any constraints like primary keys or unique indexes.
2. Align DataFrame schema with SQL table: Modify your DataFrame to match the schema of the SQL table. This might involve renaming columns, changing data types, or reordering columns to match the table’s structure.
3. Use dtype argument in to_sql(): Pandas to_sql() method allows you to specify data types for each column using the dtype argument. This is useful when you need to explicitly define data types to match those in the SQL table.

Let’s see an example where we align the DataFrame’s schema with an existing SQL table schema:

# Assume the SQL table 'Customers' has the following schema:
# CustomerID (int), FirstName (varchar), PlanType (varchar), Charge (float)
df_renamed = df.rename(columns={'Name': 'FirstName', 'Plan': 'PlanType', 'MonthlyCharge': 'Charge'})
sql_data_types = {
    'CustomerID': sqlalchemy.types.Integer(),
    'FirstName': sqlalchemy.types.VARCHAR(length=255),
    'PlanType': sqlalchemy.types.VARCHAR(length=255),
    'Charge': sqlalchemy.types.Float()
}
df_renamed.to_sql(name='Customers', con=conn, if_exists='append', index=False, dtype=sql_data_types)

 

Append Rows to SQL Server

To append new rows to an existing table, you use the to_sql() method with the if_exists='append' argument.

This ensures that the new data is added to the table without altering the existing data.

new_data = {
    'CustomerID': [1004, 1005],
    'FirstName': ['Customer D', 'Customer E'],
    'PlanType': ['Basic', 'Premium'],
    'Charge': [25, 60]
}
new_df = pd.DataFrame(new_data)
new_df.to_sql(name='Customers', con=conn, if_exists='append', index=False)