Export SQL Server Table to CSV using Python

In this guide, you’ll see the full steps to export SQL Server table to a CSV file using Python.

The Example

Assume that you want to export the following table (called the ‘dbo.product‘ table) from SQL Server to CSV using Python:

Here are the steps that you may follow.

Steps to Export SQL Server Table to CSV using Python

Step 1: Install the Pyodbc Package

To start, install the Pyodbc package using this command:

pip install pyodbc

Step 2: Connect Python to SQL Server

To connect Python to SQL Server you’ll need the server and database name.

For example, let’s suppose that we are given the information below:

• The server name is: RON-SQLEXPRESS
• The database name is: test_database

Therefore, the code to connect Python to SQL Server would look as follows:

import pyodbc

conn = pyodbc.connect(
    "Driver={SQL Server};"
    "Server=RON-SQLEXPRESS;"
    "Database=test_database;"
    "Trusted_Connection=yes;"
)

Step 3: Export the SQL Server Table to CSV using Python

For the final step, you may use the Pandas package to export the table from SQL Server to CSV.

You’ll need:

• The query to get the results to be exported. For our example, the query is: SELECT * FROM test_database.dbo.product
• The path where the CSV file will be saved. For our example, the path is: C:\Users\Ron\Desktop\exported_data.csv

Once you retrieved the above information, you’ll need to add the following syntax into the code:

import pandas as pd

sql_query = pd.read_sql_query(
    """
    SELECT * FROM test_database.dbo.product
    """,
    conn
)

df = pd.DataFrame(sql_query)

df.to_csv(r"C:\Users\Ron\Desktop\exported_data.csv", index=False)

When applying pd.read_sql_query, don’t forget to place the connection string variable at the end. In our case, the connection string variable is conn.

Putting all the components together:

import pandas as pd
import pyodbc

conn = pyodbc.connect(
    "Driver={SQL Server};"
    "Server=RON-SQLEXPRESS;"
    "Database=test_database;"
    "Trusted_Connection=yes;"
)

sql_query = pd.read_sql_query(
    """ 
    SELECT * FROM test_database.dbo.product
    """,
    conn
)

df = pd.DataFrame(sql_query)

df.to_csv(r"C:\Users\Ron\Desktop\exported_data.csv", index=False)

Run the code in Python (adjusted to your database connection information and path), and your CSV file will be exported to your specified location.

Once you open the file, you would see this data:

You may also want to check the following guide for the steps to import a CSV file into SQL Server using Python.