How to export a SQL Server table into a CSV file in Python?

Python code example to extract SQL Server data from a table into a CSV file, using the pyodbc and the csv modules.

In this tutorial, we will learn how to export a SQL Server table into a CSV file using only Python scripts. We will go through 4 main steps. The first one is creating a sample table in SQL Server. The second one is connecting to the SQL instance. And the third one is to retrieve data from the SQL Server table using a basic select statement.

Then we’ll export the content of the table to a CSV file using the csv library in Python. After that we’ll wrap up all the steps into a unique script. By the end of this tutorial, you will know how to export data from a SQL Server database to a CSV file using Python. But first you need a sample SQL table to export.

Table of Contents

1. Create a sample MS SQL table to export

This code creates a sample SQL Server table named dbo.employees in a database and inserts four sample rows. The table has four columns: id, name, department, and salary. And the id column is set as the primary key of the table.

The INSERT INTO statements insert four rows of data into the dbo.employees table. Each row has values for each of the columns in the order they were specified, i.e. : id, name, department, and salary.

CREATE TABLE dbo.employees (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   department VARCHAR(50),
   salary INT
);

INSERT INTO employees VALUES (1, 'John Smith', 'Sales', 50000);
INSERT INTO employees VALUES (2, 'Jane Doe', 'Marketing', 60000);
INSERT INTO employees VALUES (3, 'Bob Johnson', 'IT', 70000);
INSERT INTO employees VALUES (4, 'Alice Wong', 'HR', 55000);

Once done, technically you need to perform 3 simple steps, using the scripts provided below.

2. Connect to the SQL Server database

Indeed, technically the steps are very similar to the Excel export using Python, but of course the export module used in the next step is different in this case. The first step is to connect to the SQL Server database using the pyodbc module.

In my case i use a SQL Server 2019 installed on a Windows 10 machine. And I use Visual Studio 2019 to develop my Python scripts. Here is an example code that establishes a connection to a SQL Server database. I use a Windows authentication so I do not enter a user and a password.

# import the pyodbc module to manage the odbc conection
import pyodbc

# declare some variables to store the connections details
driver = 'SQL Server'
server = 'localhost'
database = 'Expert-Only'

# connect to the local SQL Server database
connection = pyodbc.connect(f'DRIVER={driver};'
                            f'SERVER={server};'
                            f'DATABASE={database};'
                            f'Trusted_Connection=yes;')

Make sure to replace the placeholders with the actual values for your SQL Server connection string: driver, server, database, and if need be username and password. In this case the code would be more like this:

import pyodbc

# connect to the SQL Server database
server = 'localhost'
database = 'database_name'
username = 'username'
password = 'password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                      f'SERVER={server};'
                      f'DATABASE={database};'
                      f'UID={username};'
                      f'PWD={password}')

3. Retrieve data from the SQL Server table

Once the connection to the SQL Server database is established, we can retrieve the data from the table using a T-SQL query. Here is an example code that retrieves all the rows from the dbo.employees table we created in the first step. Make sure to replace employees with the actual name of your SQL Server table, of course.

# retrieve the data from the employees table
cursor = connection.cursor()
cursor.execute('SELECT * FROM dbo.employees')
data = cursor.fetchall()

4. Create the CSV file and export the data into it

Finally, we can create a CSV file using the csv module and write the data to it. Here is an example code that creates a new CSV file named employees.csv and writes the retrieved data to it. Once again, Make sure to replace the path and name to the file with the actual name that you want to give to your CSV file.

# import the Python csv module
import csv

# create a new CSV file and write data to it
with open("C:\data\employees.csv", mode='w', newline='') as file:
    writer = csv.writer(file)
    writer.writerows(data)

5. Full Python code example to export SQL Server table in a CSV file

The full Python code example provided below demonstrates all the steps at once. I.e., how to export the SQL Server data into a CSV file using Python. Indeed, the script imports the necessary libraries, i.e. pyodbc and csv and sets up a connection to the database. Then it executes a SQL query to select the data. And then writes the data to a new CSV file using the csv library.

The headers are extracted from the cursor description and written to the file first, followed by the data itself. Once the export is complete, the cursor and the database connection are closed. This code can be easily modified to fit your specific use case by adjusting the connection string, table name, and file name.

import pyodbc
import csv

# Step 1: Connect to the SQL Server instance

# declare some variables to store the connections details
driver = 'SQL Server'
server = 'localhost'
database = 'Expert-Only'

# connect to the local SQL Server database
connection = pyodbc.connect(f'DRIVER={driver};'
                            f'SERVER={server};'
                            f'DATABASE={database};'
                            f'Trusted_Connection=yes;')

# Step 2: Retrieve data from the SQL Server table
cursor = connection.cursor()
cursor.execute('SELECT * FROM dbo.employees')
data = cursor.fetchall()

# Step 3: Write data to the CSV file
with open("C:\data\employees.csv", mode='w', newline='') as file:
    writer = csv.writer(file)
    writer.writerow([x[0] for x in cursor.description])  # write header
    for row in data:
        writer.writerow(row)

# Step 4: Close the database connection
cursor.close()
connection.close()

The result is a CSV file exported directly in the C:\data\ folder, with the same content as the original MS SQL table.

***How to export SQL Server table into a CSV file in Python? Result file in Windows.***

Of course , you also need to replace and set these variables with your own values:

driver
server
database

And if you are not using a Windows account to execute the Python script:

user name
password

6. Conclusion on exporting SQL Server data to CSV in Python

In conclusion, after following all the steps outlined in this Python tutorial, you should now be able to export a SQL Server table to a CSV file. Remember to first connect to your SQL Server database using pyodbc, then retrieve the data from the table, and finally write the data to a CSV file using the csv module. Exporting data from a SQL Server database to a CSV file can be a useful skill for many data analysis and reporting tasks.

By automating this process with Python, you can save yourself a lot of time and effort compared to doing it manually in Excel or other software. Indeed, Python provides a powerful set of tools for working with databases and data files. By mastering these tools, you can greatly enhance your productivity as a data analyst or scientist.