How to Export SQL Server Table to CSV using Python

August 20, 2021

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

The Example

Let’s say that you’d like to export the following table (called the ‘dbo.product‘ table) from SQL Server to CSV using Python:

product_id product_name price
1 Computer 800
2 TV 1200
3 Printer 150
4 Desk 400
5 Chair 120
6 Tablet 300

Here are the steps that you may follow.

Steps to Export SQL Server Table to CSV using Python

Step 1: Install the Pyodbc Package

If you haven’t already done so, install the pyodbc package using the command below (under Windows):

pip install pyodbc

You may check the following guide for the instructions to install a package in Python using pip.

Step 2: Connect Python to SQL Server

There are several items that you may retrieve before you connect Python to SQL Server, including the:

  • Server name
  • 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 (note that you’ll need to adjust the code to reflect your server and database information):

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=test_database;'
                      'Trusted_Connection=yes;')

You may review the following guide for the complete steps to connect Python to SQL Server.

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:

  • To install the Pandas package if you haven’t already done so. You can install the Pandas package using this command: pip install pandas
  • 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) # here, the 'conn' is the variable that contains your database connection information from step 2

df = pd.DataFrame(sql_query)
df.to_csv (r'C:\Users\Ron\Desktop\exported_data.csv', index = False) # place 'r' before the path name

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) # here, the 'conn' is the variable that contains your database connection information from step 2

df = pd.DataFrame(sql_query)
df.to_csv (r'C:\Users\Ron\Desktop\exported_data.csv', index = False) # place 'r' before the path name

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 should see this data:

product_id product_name price
1 Computer 800
2 TV 1200
3 Printer 150
4 Desk 400
5 Chair 120
6 Tablet 300

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