SQL Basics

Connect to SQLite

Upload your database to Nextjournal. For more information on this step, see Working With Data.

opennex-chicago-climate.db

Mount the database in the runtime settings. Mounts are configured in a runtime's settings under Mounts. Click the gear button next to the runtime name in the sidebar to reveal this menu:

See Runtime Settings for documentation on all runner settings.

This example uses sqlite3 in Python to connect to SQLite but similar packages exist for most languages, including Julia, R, and Clojure.

import sqlite3 as sql
import pandas as pd

database = "/opennex-chicago-climate.db"
connection = sql.connect(database)
query = '''select * from opennex_chicago_climate where Scenario="historical"'''

# Create cursor to execute SQL queries against the database
cur = connection.cursor()
# Execute the query and then fetch the results
cur.execute(query) 
cur.fetchall()

The fetch returns a list of tuples. Queries can be turned into a Pandas DataFrame, which is easier to manipulate and plot.

pd.read_sql(query,connection)

Connect to Postgres

Install Psycopg to connect to PostgreSQL using Python. Similar packages exist for most languages, including Julia, R, and Clojure.

pip install psycopg2-binary

Add the database credentials in the runtime settings under Environment Variables & Secrets. Click the gear button next to the runtime name in the sidebar to open the menu:

In particular, sensitive authentication credentials should be managed here. This feature requires a paid plan. Details can be found in Using Secrets.

Connect to the remote database using the credentials from the runner settings and then run/fetch the query.

import psycopg2
import os

# Grab the database credentials
user_name = os.environ['USER']
password = os.environ['PASSWORD']
hostname = os.environ['HOSTNAME']
port = os.environ['PORT']
db_name = os.environ['DATABASE']

conn = psycopg2.connect(dbname=db_name, user=user_name, password=password, host=hostname, port=port, sslmode='require')

print(conn)

cur = conn.cursor()
cur.execute("SELECT * FROM pg_user")
cur.fetchall()