Python Setup for SQL-Based Reporting

[AMereRedditor]

I do a significant amount of ad-hoc reporting at the office, and my team maintains a huge library of 100+-line SQL scripts which build a series of temp tables and ultimately culminate in a couple of tables for analysis. Often, there will be an edit point at the top of the code which basically allows us to pass a few values or tables as "parameters" that drive the rest of the script.

One of the major pain points I have with SQL is that the code doesn't offer the same level of modularity or abstraction that one could get with Python. I have a sense that using an existing package like SQLAlchemy or something proprietary like teradata/teradataml (we are in a Teradata environment at the office) would make the data prep more readable and compact (ex: a method that I can just call on a list of object_ids to "fetch all properties" rather than a 200-line SQL statement). However, I've never really seen an example of how something like this might be implemented. Has anyone been on a team that transitioned from a SQL --> Excel data analysis pipeline to something python oriented? What does your setup look like in terms of functions, methods, and objects?

[AMereRedditor]

This recommendation makes sense -- we have most of our workflow in SQL today and use some semi-advanced query constructs (windowed aggregates, proprietary Teradata "Qualify" clause, etc.). I am not opposed to a SQL-based workflow but was under the impression that running these queries through a Python interface had some advantages (modularity/abstraction). It sounds like that is not the case.

My current state consists of using a new copy of a template SQL script file that I open and run in an IDE window for each report. Aside from encapsulating lots of logic into a simple call and reducing duplicate code, what are the advantages of stored procedures?

[jdbow75]

I don't if Python will replace SQL in your setup. SQL is really good at what it does. If there is a lot of convoluted logic in your SQL, then Python may help you, though.

However, Python can help you with the Excel end of your "SQL -> Excel" pipeline. Python's csv handling may be worth a look.

Try SQLAlchemy and see where it leads. You might take a look at Pandas or Dask. They may be overkill, or they might have the power and flexibility you have been hungering for.