How to Export SQL Server Table to CSV using Python

In this guide, you’ll see the complete steps to export SQL Server table to CSV using Python. The same principles to be reviewed can be used to export SQL query results to a CSV file.

The Example

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

How to Export SQL Server Table 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

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

In order to connect Python to SQL server, you’ll need the server name, as well as the database name.

In my case:

  • The Server Name is: RON\SQLEXPRESS
  • The Database Name is: TestDB

So the code to connect Python to SQL Server would look as follows (you may review this guide for the complete steps to connect Python to SQL Server):

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=TestDB;'
                      '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:

  • To install the Pandas package if you haven’t already done so (you can install the Pandas package using: pip install pandas)
  • The query to get the results to be exported. For our example, the query is: select * from TestDB.dbo.Person
  • The path where the CSV file will be saved. In my case the path is: C:\Users\Ron\Desktop\export_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 TestDB.dbo.Person
                              '''
                              ,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\export_data.csv', index = False) # place 'r' before the path name to avoid any errors in the path

Putting all the components together:

import pandas as pd
import pyodbc 

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

sql_query = pd.read_sql_query(''' 
                              select * from TestDB.dbo.Person
                              '''
                              ,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\export_data.csv', index = False) # place 'r' before the path name to avoid any errors in the path

Run the code (adjusted to your database connection information and path), and your CSV file will be exported to the location that you specified:

Export SQL Table to CSV using Python

And this is how the data would look like:

Example of data