Python Script to Query Database and Create a CSV File From the Query Results. Uses pyodbc

odbc-query-to-csv.py

# Prototype python script to query database using pyodbc
# Prerequisites
# 
# Pyodbc
# https://github.com/mkleehammer/pyodbc
# pip install pyodbc
#
# This example uses Microsoft SQL Server's pubs sample database 
# https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs


import pyodbc
import os
import csv
from datetime import datetime

# ODBC connection string
connectionString = "Driver={ODBC Driver 17 for SQL Server};Server=.;Database=pubs;Trusted_Connection=yes;"

# SQL select statement
# will include field names in first row of csv file so friendly column name aliases are specified in the SQL statement
sqlStatement = """
SELECT
	emp_id AS [Employee ID]
	, lname AS [Last Name]
	, fname AS [First Name]
FROM dbo.employee
ORDER BY emp_id;
"""

# directory to write files to, change as needed
directory = "C:\\Users\\Jeremy\\Documents\\python\\query-protoype\\temp"

# will write files using this value plus a date/time to create unique file names
filenameBase = "report"


def create_filename(extension):
    now = datetime.now()
    fname = filenameBase + "_" + now.strftime("%Y-%m-%d_%H%M%S%f") + "." + extension
    return fname



def get_records(addHeaderRow=True):
    conn = pyodbc.connect(connectionString)
    cursor = conn.cursor()
    cursor.execute(sqlStatement)
    records = cursor.fetchall()

    if(addHeaderRow):
        columns = [column[0] for column in cursor.description]
        records.insert(0, columns)

    return records

 

def create_directory_structure():
    # create .\temp if not exists
 
    if not os.path.exists(directory):
        os.mkdir(directory)

def write_to_csv(records, filename):
    os.chdir(directory)

    

    with open(filename, 'w', newline='') as file:
        writer = csv.writer(file)
        writer.writerows(records)

def main():
    create_directory_structure()

    
    localFilename = create_filename("csv")

    records = get_records(addHeaderRow=True)

    write_to_csv(records, localFilename)
    

if __name__ == "__main__":
    main()