Build a SQL Snippets web app in 244 lines of Python

Data analysts and scientists often need the same or similar versions of complex SQL queries to run against their database or data warehouse. These take a lot of time and effort to write, and writing them each time from scratch is inefficient. How can the data team share important SQL queries so everyone can leverage their collective prior work?

The SQL snippets web app allows data analysts, data scientists, and engineers to view, save and run SQL queries, and have a shared repository for the whole team.

Links: Live App | Github | Youtube | Pycob | Google Slides

<yt video>

App demo

When you land on SQL Snippets, you’ll see a list of all the snippets your team has contributed. You can view the name of the snippet, who created it, and when it was last run, among other fields.

If you click on the “View” button on a particular snippet, you can see the details, including the metadata about this query, the query itself, and the cached results of the last run.

Metadata about the query

The query itself

Results from the last run (cached)

To run the query, just click the “Run SQL” button and it will take you to a new page that runs the SQL, displays the results, and replaces the cache with the new results.

Finally, you can click “Create Snippet” button to add a new snippet. Name the query, enter the tables used (a future version could auto detect the tables), and write the query. Clicking “Create” will add this to the database.

Architecture

Let’s dive into how we build this app from scratch. First, we need to grab the data and turn it into something that Pycob can consume. We do this in this Jupyter Notebook.

SQL Snippets’ architecture — both the data model and pages

Code

Let’s highlight a few important parts of the code to get a sense of how this all works.

First, building the all_snippets page is 16 lines of code! There are two key functions — server_request.list_objects grabs the snippets, and page.add_pandastable that renders the table with the snippets in the UI.

def all_snippets(server_request: cob.Request) -> cob.Page:
    page = cob.Page("All Snippets")
    page.add_header("All Snippets")
    
    username = server_request.get_username()

    snippets = server_request.list_objects(table_id="snippet")

    df = pd.DataFrame(snippets)

    action_buttons = [
        cob.Rowaction(label="View", url="/view_snippet?id={id}", open_in_new_window=False),
    ]

    page.add_pandastable(df, hide_fields=["id", "Query"], action_buttons=action_buttons)

    return page           

The create_snippet page renders the form to add a new snippet. It uses Pycob’s built in user management functionality as well.

def create_snippet(server_request: cob.Request) -> cob.Page:
    page = cob.Page("Create Snippet")


    # Get the username of the user who is logged in.
    username = server_request.get_username()


    # Check if the user has submitted any form data.
    if server_request.params():
        # If the user has submitted form data, create a new row in the database.
        page.add_text("Creating new row...")


        # Get the form data from the server request.
        form_data = server_request.params()
        form_data["author"] = username
        form_data["created_at"] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        form_data["updated_at"] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        # A UUID is a unique identifier that can be used to identify a row in the database.        
        form_data["id"] = str(uuid.uuid4())
        form_data["last_run"] = ""
        form_data["rows_returned"] = 0

Finally, let’s look at the code that actually executes the SQL, run_snippet. Here, we simply utilize pandas’ pd.read_sql_query to run the query and then save it to our cloud storage using server_request.app.to_cloud_pickle so we can retrieve the cached version later.

def run_snippet(server_request: cob.Request) -> cob.Page:
    page = cob.Page("Snippet")

    code = server_request.params("code")
    id = server_request.params("id")

    page.add_codeeditor(code, language="sql")

    df = pd.read_sql_query(code, conn)

    server_request.app.to_cloud_pickle(df, f"{id}.pkl")

    page.add_datagrid(df)

    data = server_request.retrieve_dict(table_id="snippet", object_id=id)

    data["last_run"] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    data["rows_returned"] = len(df)

    server_request.store_dict(table_id="snippet", object_id=id, value=data)

    return page

Deployment

Now that we have the app running on our local machine, let’s deploy it to Pycob’s cloud hosting so other people in our organization can access it. Deploying is super simple and just takes one step once you have your API key. All you need to do is —

python3 -m pycob.deploy

And wait about 5 mins, and the app is live on the server and ready to go!

Using SQL Snippets in your organization

SQL Snippets is 100% free and open source. You can grab the code here, modify it to suit your needs, and run it locally or deploy it using Pycob’s cloud offering.