[FIXED] Best practice for writing SQL queries in Jupyter

Issue

I am doing some analysis using Jupyter notebooks. I usually use pandas.read_sql() to write SQL queries in Jupyter. Recently I have written a relatively big query with multiple joins. Its about a 25 line query. What is the best practice when writing such queries in Jupyter? For example, writing a query like this is no biggie -

pd.read_sql('select cs1.CLIENT as ClientName from central cs1', db.connect_win())

It is easy to read and understand, but what about larger queries? I want them to have indention and such so they are easier to read and understand.

Solution

I would do something like this.

sql_query = """
  SELECT first_name, last_name
  FROM actor
  WHERE actor_id IN
  (
    SELECT actor_id
    FROM film_actor
    WHERE film_id IN
    (
      SELECT film_id
      FROM film
      WHERE title = 'ALTER VICTORY'
    )
  );
"""
actor = pd.read_sql(sql_query, db.connect_win())

There are some great VS Code Extensions, like SQL Server (mssql). It will do automatic formatting for you. Sometimes I like using VS Code first, as a linting tool, and pasting the resulting query back into my Jupyter notebook.

Answered By - David Rinck