This textbook comes with a Devcontainer that runs a MySQL database server with the following credentials:
database server address:
dbusername:
devpassword:
devpass
These 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 SQL “Magic” for Jupyter Notebooks¶
You can execute SQL statements directly from Jupyter with the help of SQL “Jupyter magic” implemented by the sqljupy library.
The following cell sets up the connection to the database for the Jupyter SQL Magic.
%load_ext sql
%sql mysql+pymysql://dev:devpass@dbConnect with DataJoint¶
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)[2025-09-16 01:37:46,977][INFO]: DataJoint 0.14.6 connected to dev@db:3306
DataJoint connection (connected) dev@db:3306If 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.
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.
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.userWe 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!