Send Email Attachment from Redshift query (PostgreSQL) with Python

Back in early days of the pandemic I was once experiencing a rather small calamity where the incoming API data source to the data warehouse was corrupted due to external factors, hence I could not relied on the validity of the data and subsequently I had to inform the business users to temporarily stop using and looking at the downstream BI reporting system. Furthermore, the team had to prioritize fixing an issue in the production environment only if it was a business critical issue due to resource constraint as many employees in the team had been stood down, so I had to find another way on the creation of regular daily and weekly reports to my stakeholders with minimum manual efforts required. Below is what I have coded to automate the process to derive the query from Amazon Redshift (or other similar PostgreSQL based environment) and saved the query results to a csv or excel file onto a local or shared network drive and then afterwards send the final file to the recipient using SMTP. Although the solution below was a short term, it helped me immensely on focusing on other task at the time!

Data scientist — passionate in tackling business problem with data, byte by byte