Using a connection file#

Important

When using a connection file, ensure the file has the appropriate permissions, so only you can read its contents.

Using a connection file is the recommended way to manage connections, it helps you to:

  • Avoid storing your credentials in your notebook

  • Manage multiple database connections

  • Define them in a single place to use it in all your notebooks

%load_ext sql

By default, connections are read/stored in a ~/.jupysql/connections.ini file:

%config SqlMagic.dsn_filename

'/home/docs/.jupysql/connections.ini'

However, you can change this:

%config SqlMagic.dsn_filename = "connections.ini"

Tip

For configuration settings other than connections, you can use a pyproject.toml or ~/.jupysql/config file.

The .ini format defines sections and you can define key-value pairs within each section. For example:

[section_name]
key = value

Add a section and set the key-value pairs to add a new connection. When JupySQL loads them, it’ll initialize a sqlalchemy.engine.URL object and then start the connection. Valid keys are:

  • drivername: the name of the database backend

  • username: the username

  • password: database password

  • host: name of the host

  • port: the port number

  • database: the database name

  • query: a dictionary of string keys to be passed to the connection upon connect (learn more here)

For example, to configure an in-memory DuckDB database:

[duck]
drivername = duckdb

Or, to connect to a PostgreSQL database:

[pg]
drivername = postgresql
username = person
password = mypass
host = localhost
port = 5432
database = db

from pathlib import Path

_ = Path("connections.ini").write_text(
    """
[duck]
drivername = duckdb
"""
)

To connect to a database defined in the connections file, use --section and pass the section name:

%sql --section duck

Changed in version 0.10.0: The connection alias is automatically set when using %sql --section

Note that the alias is set to the section name:

%sql --connections
Active connections:
current url alias
* duckdb:// duck

Changed in version 0.10.0: Loading connections from the .ini (%sql [section_name]) file has been deprecated. Use %sql --section section_name instead.

from urllib.request import urlretrieve
from pathlib import Path

url = "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv"

if not Path("penguins.csv").exists():
    urlretrieve(url, "penguins.csv")

%%sql
drop table if exists penguins;

create table penguins as
select * from penguins.csv
Running query in 'duck'
Count 
%%sql
select * from penguins
Running query in 'duck'
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex
Adelie Torgersen 39.1 18.7 181 3750 MALE
Adelie Torgersen 39.5 17.4 186 3800 FEMALE
Adelie Torgersen 40.3 18.0 195 3250 FEMALE
Adelie Torgersen None None None None None
Adelie Torgersen 36.7 19.3 193 3450 FEMALE
Adelie Torgersen 39.3 20.6 190 3650 MALE
Adelie Torgersen 38.9 17.8 181 3625 FEMALE
Adelie Torgersen 39.2 19.6 195 4675 MALE
Adelie Torgersen 34.1 18.1 193 3475 None
Adelie Torgersen 42.0 20.2 190 4250 None
Truncated to displaylimit of 10.

Managing multiple connections#

Let’s now define another connection so we can show how we can manage multiple ones:

_ = Path("connections.ini").write_text(
    """
[duck]
drivername = duckdb

[second_duck]
drivername = duckdb
"""
)

Start a new connection from the second_duck section name:

%sql --section second_duck
Connecting and switching to connection second_duck
%sql --connections
Active connections:
current url alias
duckdb:// duck
* duckdb:// second_duck

There are no tables since this is a new database:

%sqlcmd tables
Name

If we switch to the first connection (by passing the alias), we’ll see the table:

%sql duck
Switching to connection duck
%sqlcmd tables
Name
penguins

We can change back to the other connection:

%sql second_duck
Switching to connection second_duck
%sqlcmd tables
Name

Setting a default connection#

New in version 0.10.1.

If JupySQL finds a default section in your connections file, it’ll automatically connect to it when the extension is loaded. For example, to connect to an in-memory DuckDB database:

[default]
drivername = duckdb

Then, whenever you run: load_ext %sql, the connection will start.