Python: How to export data to Excel with xlsxwriter

In this video, we'll fetch information from a database and export it to ExcelShow more

The pyodbc library to connect to databases:
https://pypi.org/project/pyodbc/

The xlsxwriter library:
https://pypi.org/project/XlsxWriter/

xlsxwriter documentation:
https://xlsxwriter.readthedocs.io/

Connection strings:
https://www.connectionstrings.com

Always use a VPN and protect your privacy. Get a great offer on Nord VPN here:
https://go.nordvpn.net/aff_c?offer_id=15&aff_id=30592&url_id=902
Nord VPN has a no logs policy so all your activity is private.

Evil Programmer Merchandise (world wide):
https://teespring.com/stores/evil-programmer

-------------------------------------------------------
Script:
-------------------------------------------------------
import xlsxwriter, pyodbc

#put database connection string here
conn = pyodbc.connect(" ")

#cursor
cur = conn.cursor()

#sql statement to retrieve product category
sql = "SELECT DISTINCT Production.ProductCategory.[Name] AS [Category] FROM Production.ProductCategory WHERE Production.ProductCategory.[Name] IS NOT NULL"

#execute sql
cur.execute(sql)

#results
rs = cur.fetchall()

#Create workbook
wb = xlsxwriter.Workbook("Report.xlsx")

sql = "SELECT Production.Product.ProductID AS [Product ID], Production.Product.[Name] AS [Product], Production.ProductModel.[Name] AS [Model], Production.ProductCategory.[Name] AS [Category], Production.ProductSubcategory.[Name] AS [Sub Category] FROM Production.Product LEFT JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID LEFT JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID LEFT JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE Production.ProductCategory.[Name] IS NOT NULL AND Production.ProductCategory.[Name]="

for rec in rs:
#get records for each category
cur.execute(sql + "'" + rec.Category + "'")
records = cur.fetchall()

print("Category: " + rec.Category)

#create a worksheet for each category
ws = wb.add_worksheet(rec.Category)

#column headers
columns = [column[0] for column in cur.description]

#header
ws.write_row(0,0,columns)

rownum = 1
for record in records:
ws.write_row(rownum,0,record)
rownum += 1

#close workbook
wb.close()

cur.close()
del cur

conn.close()

print("Completed!")

----------------------------------------------------------------------

The AdventureWorks2017 SQL query for products:

USE AdventureWorks2017;

SELECT
Production.Product.ProductID AS [Product ID],
Production.Product.[Name] AS [Product],

Production.ProductModel.[Name] AS [Model],

Production.ProductCategory.[Name] AS [Category],

Production.ProductSubcategory.[Name] AS [Sub Category]

FROM
Production.Product

LEFT JOIN
Production.ProductModel
ON
Production.Product.ProductModelID = Production.ProductModel.ProductModelID

LEFT JOIN
Production.ProductSubcategory
ON
Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID

LEFT JOIN
Production.ProductCategory
ON
Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID

WHERE Production.ProductCategory.[Name] IS NOT NULL