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)DataJoint connection (connected) root@localhost: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.
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
%config SqlMagic.style = '_DEPRECATED_DEFAULT' # addresses a bug in the SQL magic extension
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 * mysql://root:***@localhost
5 rows affected.
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
Cell In[4], line 1
----> 1 get_ipython().run_cell_magic('sql', '', '-- show all users\nSELECT User FROM mysql.user\n')
File /opt/conda/lib/python3.11/site-packages/IPython/core/interactiveshell.py:2565, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
2563 with self.builtin_trap:
2564 args = (magic_arg_s, cell)
-> 2565 result = fn(*args, **kwargs)
2567 # The code below prevents the output from being displayed
2568 # when using magics with decorator @output_can_be_silenced
2569 # when the last Python token in the expression is a ';'.
2570 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):
File /opt/conda/lib/python3.11/site-packages/sql/magic.py:219, in SqlMagic.execute(self, line, cell, local_ns)
216 return
218 try:
--> 219 result = sql.run.run(conn, parsed["sql"], self, user_ns)
221 if (
222 result is not None
223 and not isinstance(result, str)
(...) 226 # Instead of returning values, set variables directly in the
227 # user's namespace. Variable names given by column names
229 if self.autopandas:
File /opt/conda/lib/python3.11/site-packages/sql/run.py:374, in run(conn, sql, config, user_namespace)
372 if result and config.feedback:
373 print(interpret_rowcount(result.rowcount))
--> 374 resultset = ResultSet(result, config)
375 if config.autopandas:
376 return resultset.DataFrame()
File /opt/conda/lib/python3.11/site-packages/sql/run.py:116, in ResultSet.__init__(self, sqlaproxy, config)
114 list.__init__(self, sqlaproxy.fetchall())
115 self.field_names = unduplicate_field_names(self.keys)
--> 116 self.pretty = PrettyTable(self.field_names, style=prettytable.__dict__[config.style.upper()])
117 else:
118 list.__init__(self, [])
KeyError: 'DEFAULT'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!