Jupyter SQL Magic Persist

In Jupyter Lab, the %sql magic command persist creates a new table in the database to which we are connected. The table name will be the name as the name of the Python variable.

Example:

import pandas as pd
df = pd.read_csv('/tmp/data.csv')
df
column1 column2
0 1 a
1 2 b
2 3 b
# set environment variable $DATABASE_URL
%sql postgresql://datacomy:PASSWORD@localhost/datacomy
'Connected: datacomy@datacomy'
%sql persist df
 * postgresql://datacomy:***@localhost/datacomy





'Persisted df'
%%sql
SELECT tablename,tableowner  
FROM pg_catalog.pg_tables  
WHERE tableowner='datacomy';  
 * postgresql://datacomy:***@localhost/datacomy
6 rows affected.
tablename tableowner
my_table1 datacomy
my_table2 datacomy
df datacomy

As we can see, the table ‘df’ was created.

If we want to create a table using the same name, PostgreSQL with throw an error ValueError: Table 'df' already exists.

%sql persist df
 * postgresql://datacomy:***@localhost/datacomy



---------------------------------------------------------------------------

ValueError                                Traceback (most recent call last)

[...]

ValueError: Table 'df' already exists.

After the table is created, we can execute SQL commands using Jupyter or any other RDBMS frontend:

%sql select * from df
 * postgresql://datacomy:***@localhost/datacomy
3 rows affected.
index column1 column2
0 1 a
1 2 b
2 3 b

Or from the PostgreSQL terminal:

jupyter persist

Related