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.