From Oracle to Outlook - automating SQL query output using Python - Part I

Hello all,

It's been a while since my last post. Things have been busy especially in Australian payroll legislation space - Payday Super, AU Legislative changes after budget, etc. etc. 📜

As I have got some breathing space for now, so here is an attempt from my end to share knowledge around how i have been using Python 🐍 to fetch data from on premise Oracle Database and then capturing the same in outlook email message.

This can be a very useful thing for anyone who want to send a summary email to his/her team or management. The design of my solution is quite generic and the good thing is, you can use the template and can add queries as per your requirement and the output will just get appended in the outlook mail body 

I have made this into a two-part blog post. In this first part i'll be writing about the building blocks and components. The next part will have details with a real example

From a high level, the flow is:

1. Setup logging and directories
2. Load database credentials
3. Open the Oracle connection
4. Build the list of SQL query definitions
5. Execute each SQL query and convert results to HTML
6. Assemble one HTML email body from all sections
7. Create an Outlook draft with that HTML content

Please note - I am assuming that you are familiar with python and can install the mentioned libraries. If not, then I'll have to request you to get the basic steps around python installation and oracle client installation. This should not be an issue in today's age of AI and LLM models :) 

Coming back to our agenda, my script uses a small, focused set of libraries

• cx_Oracle for the database connection to on‑prem Oracle. This is the bridge from Python into the Oracle client stack installed on your machine. It uses the username, password, and DSN values you provide to open a normal Oracle session, so anything you can query in TOAD or SQL Developer you can query from this script.
• pandas for shaping query results and converting them to HTML tables
• win32com.client for automating Outlook via COM
• logging, pathlib, dataclasses, datetime, etc. from the standard library for structure, diagnostics and configuration

=====

Next is, instead of hard‑coding credentials, the script refers a simple flat file under a credentials folder sitting in the same directory as this python script:

• BASE_DIR is the folder containing the script file.
• CREDENTIAL_DIR = BASE_DIR / "credentials"
• LOG_DIR = BASE_DIR / "logs"
• CREDENTIAL_FILE = CREDENTIAL_DIR / "db_credentials.txt"

The credential file uses a very simple key=value format:

username=your_db_username

password=your_db_password

dsn=(DESCRIPTION=<your database tns details>)

A helper function parse_key_value_file() reads this file, skips comments and blank lines, and returns a dictionary. load_db_credentials() then validates that all required keys (username, password, dsn) are present and wraps them in a small DbCredentials dataclass. This keeps credentials out of the source file while still being simple enough for you to maintain.

Logging is configured to write to a timestamped file under a 'logs' directory in the same base path, using setup_logging(). Each step of the process (loading credentials, connecting to the DB, running each query, building the email) writes to the log

====

The key to making my script modular is treating each report section as data and NOT as a hard-wired code. A QueryDefinition dataclass describes each section:

• name: section title displayed in the email
• description: short description under the heading
• sql: the actual SQL text to run
• empty_message: what to show if the query returns no rows

The function - build_query_definitions() returns a list of these definitions. 

To add a new report section, you simply add another QueryDefinition to this list – no need to touch database or email plumbing.

I feel this should be enough to grasp in one post. Please stay tuned for the next and final part of this post