nbdbsession 0.2.2

Config mgt to connect to databases from jupyter notebooks.

nb_db_session

I use notebooks all the time to connect to databases like postgres. With the notebook sessions, I often work on PoCs (proof of concept), on presentations or on debugging.

catherinedevlin has created open source software that I love: ipython-sql. This code lets you connect to sql databases from your notebook, and run queries.

How To Use nb_db_session

First, install it:

pip install nbdbsession

Then, in your git repository where you start your notebook, create a .settings.toml file with your database login credentials:

# .settings.toml on top level of your git repo
[davidkuda]
db_driver = "postgresql"
database = "dev"
user = "davidkuda"
password = "${DB_PASSWORD}" # you can use environment variables
db_url = "localhost"
port = 5439
# the ssh command is optional:
ssh_cmd = "ssh -fL 5432:db.kuda.ai:5432"

Finally, you can connect to your database in your notebook by running the following code in a cell:

from nbdbsession.sqlconn import connect

connect("davidkuda") # note: this is the name as defined in .settings.toml

Once you have done that, you can run sql commands by prepending %sql (one line) %%sql (multi-line) in the notebook.

%sql SELECT * FROM table LIMIT 10;

%%sql
SELECT
    *
FROM
    table
LIMIT
    10;

Managing the conn without this repo

In the usual way, you would create a connection string, something like this:

%load_ext sql 

import parse

# make a connection string for your database connection
config = {
    "user": "postgres",
    "password": parse.quote("postgres"),
    "url": "127.0.0.1",
    "port": 5432,
    "database": "postgres",
}

conn_string = f'postgresql://{c["user"]}:{c["password"]}@{c["url"]}:{c["port"]}/{c["database"]}'
# results in: 'postgresql://postgres:postgres@127.0.0.1:5432/postgres'

%sql $conn_string

With the code in this repo, you reduce all that to one line:

from nbdbsession.sqlconn import connect

# this will enable ipython sql and use the conn str that you choose:
connect("staging")

%sql