Skip to main content

Write back to your database with multi-statement SQL queries

While we don’t (yet!) support the ability to write a dataframe directly to a database table, you can leverage our SQL cells which can execute multi-statement queries along with some Jinja magic to unlock this workflow.

If this method isn’t your cup of tea, you can always use Python to establish a connection to your database and writeout data a la sqlalchemy and the like. See this tutorial for an example of that approach!

tip

Checkout the campanion Hex project for this tutorial here.

Insert data row by row into a table in your database#

For quick illustration, we're using the well-known "iris" dataset.

import seaborn as snsdf_iris = sns.load_dataset('iris')

Next up, we’ll assemble a SQL query to create a table and write to it.

But first a short detour.#

With workflows that require writing back to a database, you’ll often want to be extra cautious about when that writeback actually happens. You don’t want an accidental run of a cell to always add new tables or rows. You can add protection against this inadvertent cell execution by using a Run Button and incorporating that into your query!

The tl;dr on Run buttons — the output of a Run button input is a boolean which evaluates True if you’ve pressed the button and False if you haven't. For example, if you refresh an app or re-run your project the button parameter has not explicitly been pressed and will evaluate False. When you press a Run button, a full project run is triggered. During that specific run, the button parameter will be evaluated True.

So, if you only wanted to execute a query if a Run button has been pressed, you can incorporate an if block with Jinja into your query to conditionally fire it off!

The full query#

Ok, now onto assembling the final query which will:

  1. Delete the table we want to create if it already exists
  2. Create a table with the desired columns and data types
  3. Loop through our dataframe and insert values into that table row by row

First, we wrap everything in a Jinja if block to test if the Run button has been pressed. A generic Jinja if block has the following structure:

{% if condition %}    Write conditional query content here{% else %}    Write some alternative query content here{% endif %}

Next, we drop any existing table of the same name and create a new table with the desired column using two SQL statmentens, DROP TABLE IF EXISTS and CREATE TABLE. This example is in a PostgreSQL database, so your syntax may vary!

Finally, using Jinja again, we loop through our datafame row by row and write those values to the newly created table. Here’s a dummy example of a Jinja for loop:

{% for iterable in variable %}    {{ interable }}{% endfor %}

All together now!

{% if write_to_db %}
DROP TABLE IF EXISTS public.iris;
CREATE TABLE iris (    id SERIAL PRIMARY KEY,    sepal_length FLOAT,    sepal_width FLOAT,    petal_length FLOAT,    petal_width FLOAT,    species VARCHAR(255));
{% for i,row in df_iris.iterrows() %}    INSERT INTO iris (sepal_length, sepal_width, petal_length, petal_width, species)    VALUES ({{row | array}});{% endfor %}
SELECT *from iris
{% endif %}

To learn more about writting data to your database, checkout the docs as well a companion Hex project for this tutorial.

On this page