ORM, SQL and all that jazz

14 Jul 2018

Let me try to work through a process:

1) Extract data from your database

You have a giant SQL query (read: >200 lines) which you format really really neatly for ease of reading and works like a charm. But if I change something in one part of the SQL, does it affect another part of the SQL? Yes? No?

How would you know?

This is the case for unit testing - if all your (well written) test pass, you can be assured that nothing will break. But how can you unit test SQL? Apparently, you can’t. (I have not been informed otherwise!!!)

ORM or SQL? Object-Relational Mapper (ORM) basically allows you to translate code into SQL statements. Meaning, you can now test your code! Ok ORM is a lot more powerful than that, but this tiny portion of its functionality is all we need for now. Read more here: https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/

SqlAlchemy is one of the ORMs you can use with Python: https://www.blog.pythonlibrary.org/2010/09/10/sqlalchemy-connecting-to-pre-existing-databases/

2) Transform your data

Yup do your Python pandas magic here!! Instead of doing all that in that giant SQL query.

3) Serve your data

So for example you used to pull all that data straight into Tableau for visualisation (yes using that giant SQL query!!) Now if you serve your manipulated data through an API which can also go direct into Tableau…


other random stuff in the meantime: For building a flask app: https://pythonspot.com/flask-web-app-with-python/ For coding on your phone (?? LOL): http://omz-software.com/pythonista/ For Django: https://docs.djangoproject.com/en/2.0/intro/tutorial01/ For debugging (i have NOT clicked into this link at all lol): https://www.digitalocean.com/community/tutorials/how-to-use-the-python-debugger For Python in Tableau (but i want it OUTSIDE of Tableau): https://github.com/tableau/TabPy For legit ORM (but so complicated to read): http://docs.sqlalchemy.org/en/latest/orm/tutorial.html Um: https://www.sitepoint.com/publishing-mysql-data-web/