nbdbsession 0.2.7
Config mgt to connect to databases from jupyter notebooks.
License: MIT

# nbdbsession

__This code helps you to manage connections to sql databases in jupyter notebooks.__

`nbdbsession` stands for "notebook database session".

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

[`catherinedevlin`](https://github.com/catherinedevlin) has created open source software that I love: [`ipython-sql`](https://pypi.org/project/ipython-sql/).  (Note: There was a fork of this project: [`jupy-sql`](https://jupysql.ploomber.io/en/latest/quick-start.html). The original version is not actively maintained, and does not work with sqlalchemy version 2 that was [released in January 2023](https://docs.sqlalchemy.org/en/20/changelog/changelog_20.html#change-2.0.0))

This code -- `nbdbsession` -- lets you connect to sql databases from your notebook, and run queries.

### How To Use `nbdbsession`

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:

You need to define your database credentials in a toml file. The toml file should look like this:

```toml
# either: .settings.toml on top level of your git repo
# or:     $HOME/.nbdbsession.creds.toml

[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"
```

nbdbsession will scan three places for a toml file and will choose the first hit in this order:

- env var `$SETTINGS_FILE_PATH`
- (root directory of a git project)/.settings.toml
- `$HOME/.nbdbsession.creds.toml`

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

```python
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.

__Run single line sql commands directly in your notebook:__

```sql
%sql SELECT * FROM table LIMIT 10;
```

__Run multi-line sql commands directly in your notebook:__

```sql
%%sql
SELECT
    *
FROM
    table
LIMIT
    10;
```

### How to publish a pkg to PyPI

```sh
# package code
python setup.py sdist

# upload code to PyPI
twine upload dist/*

# update pkg
pip install nbdbsession --upgrade
```

### Managing the conn without this repo

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

```python
%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:

```python
from nbdbsession.sqlconn import connect

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

%sql
```