Database Connection - The DataJoint Book

This textbook comes with a Devcontainer that runs a MySQL database server with the following credentials:

database server address: db
username: dev
password: devpass

These credentials are not secret since this database is not exposed to the external world.

In this container environment, the user credentials are set in as the environment variables DJ_HOST, DJ_USER, and DJ_PASS and that’s what the DataJoint client library will use to connect to the database.

Connect with DataJoint¶

DataJoint is the primary way to connect to the database in this book. The DataJoint client library reads the database credentials from the environment variables DJ_HOST, DJ_USER, and DJ_PASS.

Simply importing the DataJoint library is sufficient—it will connect to the database automatically when needed. Here we call dj.conn() only to verify the connection, but this step is not required in normal use.

import datajoint as dj
dj.conn()  # test the connection (optional)

Connect with SQL Magic¶

SQL “Jupyter magic” allows executing SQL statements directly in Jupyter notebooks, implemented by the jupysql library. This is useful for quick interactive SQL queries and for learning SQL syntax. We will use SQL magic in this book for demonstrating SQL concepts, but it is not used as part of Python application code.

The following cell sets up the SQL magic connection to the database.

%load_ext sql
%sql mysql+pymysql://dev:devpass@db

You can issue SQL commands from a Jupyter cell by starting it with %%sql. Change the cell type to SQL for appropriate syntax highlighting.

%%sql
-- show all users
SELECT User FROM mysql.user

Connect with a Python MySQL Client¶

To issue SQL queries directly from Python code (outside of Jupyter magic), you can use a conventional SQL client library such as pymysql. This approach gives you full programmatic control over database interactions and is useful when you need to execute raw SQL within Python scripts.

import os
import pymysql

# create a database connection
conn = pymysql.connect(
    host=os.environ['DJ_HOST'], 
    user=os.environ['DJ_USER'], 
    password=os.environ['DJ_PASS']
)

# create a query cursor and issue an SQL query
cur = conn.cursor()
cur.execute('SELECT User FROM mysql.user')
cur.fetchall()

We are all set for executing all the database queries in this book!