Python Application for Chinook Database

We’re going to write it in Python to make it easy to browse the code using the anosql Python library. It’s very easy to embed SQL code in Python and keep the SQL clean and tidy in .sql files.

Steps to implement the idea in the application code

To implement the same thing in application code, we have to do the following:

1. Fetch the list of genres (that’s one select name from genre query).

2. Then, for each genre, fetch the top-N list of tracks, which is the ss subquery, before running as many times as genres from the application.

3. Then, for each track selected in this way (that’s n times how many genres we have), we can fetch the artist’s name.

That’s a lot of data to go back and forth between our application and our database server. It’s a lot of unnecessary processing too. So we avoid all this extra work by having the database compute exactly the result set we’re interested in, and then we have a very simple Python code that only looks at the user interface, here parsing command-line options and printing out the result of our queries.

Another common argument against the seemingly complex SQL query is that we know another way to obtain the same result in SQL that doesn’t involve a lateral subquery. Sure, it’s possible to solve this top-N problem in other ways in SQL, but they’re all less efficient than the lateral method. We can learn to figure out the most efficient way to write a query by reading explain plan.

Application for the Chinook database

For now, let’s suppose this is the best way to write the query. So, of course, that’s the one we’re going to include in the application’s code, and we need an easy way to then maintain the query.

Here’s the whole of our application code: