Database Connection - The DataJoint Book

Connect with DataJoint¶

The Devcontainer that comes with this textbook contains a running MySQL server.

The root credentials are set in environment variables DJ_HOST, DJ_USER, and DJ_PASS. These credentials are not secret since this database is not exposed to the external world.

The DataJoint client library uses these environment variables to connect to the database. It sufficient to simply import the datajoint client library. It will connect to the database automatically as soon as necessary. However, you can explicity verify the connection by calling dj.conn():

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

[2024-08-14 03:55:54,328][INFO]: Connecting root@localhost:3306
[2024-08-14 03:55:54,347][INFO]: Connected root@localhost:3306

DataJoint connection (connected) root@localhost:3306

If you are only learning DataJoint, you are done. If you are reading this text to also learn SQL, you can use two ways to issue queries: with IPython magic commands or a client library.

Connect with IPython “Magic”¶

You can execute SQL statements directly from Jupyter with the help of “magic commdands”.

The following cell sets up the connection to the database for the Jupyter SQL Magic.

import pymysql
import os
pymysql.install_as_MySQLdb()

connection_string = "mysql://{user}:{password}@{host}".format(
    user=os.environ['DJ_USER'],
    host=os.environ['DJ_HOST'],
    password=os.environ['DJ_PASS']
)

%load_ext sql
%sql $connection_string

Then 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

We will use SQL magic only for fast interactive SQL queries. We will not use SQL magic as part of Python code.

Connect with Python client¶

To issue SQL queries from Python code, we will use a conventional SQL client, in this case pymysql.

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

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

(('debian-sys-maint',),
 ('mysql.infoschema',),
 ('mysql.session',),
 ('mysql.sys',),
 ('root',))

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

Setup

Development Environment

Concepts

Data Models