Connect to SQLite or Postgres

Connect to SQLite

Step 1: upload your database

Upload your database to Nextjournal by dragging and dropping the file into your notebook, or by clicking the “+” button below a block and choosing the File option.

Step 2: mount the database

If you haven't already, add a code cell in the language that you want to use your database with. To mount the database, click the ⚙️ next to the runtime's name in the sidebar to reveal the runtime settings, and add it to the Mounts section:

Done! The database will now be available to the runtime's file system.

Step 3: connect

In the example below, we use 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

Step 1: configure your Google Cloud SQL instance

Make sure your Google Cloud SQL instance accepts connections on public IP, and note the public IP as shown in https://console.cloud.google.com/sql/instances. Use this public ip as HOSTNAME later in this example.

Then, add the following two IPs to the "Public IP - Authorized networks" setting of your Google Cloud SQL instance.

• 34.77.83.158/32

• 35.240.22.225/32

Step 2: install a database adapter

If you use Python, install Psycopg to connect to PostgreSQL. Similar packages exist for most languages, including Julia, R, and Clojure.

pip install psycopg2-binary

Step 3: add the database credentials

Add the database credentials by first opening the Python runtime settings (using the ⚙️ button next to the runtime's name), and inserting them as variables in the Environment Variables Section.

In particular, sensitive authentication credentials should be managed here; find more details about that in our Using Secrets guide.

Step 4: connect to the database

Great! Now, you can connect to the remote database by using the credentials right away, and then run/fetch the query, as we do below:

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()