My team spends hours every Friday manually pulling data from three different SQL databases and formatting it into a "Master Excel" sheet for management. I want to build a Python script that automates the queries, joins the data, and applies specific cell formatting and charts. Which libraries are best for maintaining the "look and feel" of the original Excel template?
3 answers
You should use a combination of Pandas for the data manipulation and Openpyxl for the formatting. Pandas is great at the read_sql and merge functions, which will handle your three sources easily. Once you have your final dataframe, you use Openpyxl to write it to an existing Excel template. This allows you to keep your logos, headers, and conditional formatting intact while just updating the raw data. We automated a similar 6-hour weekly task for our finance department, and now the script runs in 45 seconds. Just make sure to use XlsxWriter if you need to generate complex charts programmatically from within Python.
Have you considered using a SQL abstraction layer like SQLAlchemy to make your queries more maintainable across different DB types?
Don't forget the Email library in Python; you can automate the final step of sending that Excel sheet to your manager as an attachment!
Great tip, Barbara. We use the smtplib library to send out the final report at 5:00 PM every Friday automatically. It’s the ultimate "set it and forget it" workflow.
Christopher, SQLAlchemy is a must-have here. It handles the connection pooling and prevents SQL injection risks. We also use it to map the database tables to Python classes, which makes the logic much cleaner. For Kevin's report, he can use SQLAlchemy to pull data from PostgreSQL, MySQL, and SQL Server using the same syntax. It makes the "Master Excel" script much more robust if the database schema ever changes, as you only have to update the mapping in one place.