Nations - The DataJoint Book

Background¶

The Nations database is reproduced here from the MariaDB tutorial under fair use principles for instructional purposes.

The original is found at https://www.mariadbtutorial.com/getting-started/mariadb-sample-database/

This example demonstrates how DataJoint can work with existing SQL schemas. We:

Load the canonical schema exactly as published by the MariaDB team so you can compare the DataJoint rendition with the original SQL definitions.
Use spawn_missing_classes() to automatically create DataJoint table classes from an existing database schema.
Visualize the structure using DataJoint’s diagram tool to understand the relationships between tables.

The Nations database contains geographical data including:

Continents: Major continental regions
Regions: Sub-regions within continents
Countries: Individual nations with their regional assignments
Country Statistics: Population and GDP data over time
Languages: Languages spoken in each country

Loading the Dataset¶

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

%sql --file ./nation.sql

 * mysql://root:***@localhost
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
1 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
7 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
239 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
984 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
9514 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
5 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
457 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
25 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
25 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
5 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.

[]

%%sql

SHOW TABLES in nation;

 * mysql://root:***@localhost
9 rows affected.

DataJoint Schema Diagram¶

Now we use DataJoint to introspect the existing schema and generate Python table classes. The spawn_missing_classes() method automatically creates DataJoint table classes that mirror the existing database tables.

import datajoint as dj
schema = dj.Schema('nation')
schema.spawn_missing_classes()

dj.Diagram(schema)

Sample Query¶

Here we display the CountryStats table showing population and GDP data over time:

CountryStats()