SQL browsing and/or DB/Table creation

Special Topics Education
#1

I teach an introduction to SQL class, using Jupyter. I’m looking for an approach for two things:

  1. Browsing SQL Database and Tables
  2. Creating Databases/Tables/Columns.

Currently I teach SELECT before I teach CREATE and INSERT, my intention is to minimize cognitive distraction and give some payoff before setup. In the past I’ve used phpmyadmin (together with MariaDB) but I’m switching over to postgres (for various reasons). pgadmin4 is even more complex visually and with options than phpmyadmin is (not to mention my lack of desire to discuss Servers/Databases/Schemas/Tablespaces).

I’ve checked out which was promising for browsing databases/tables. But it seems to be out of date and not active in responding to issues: https://github.com/pbugnion/jupyterlab-sql/issues/147

We’ve also explored http://www.adminer.org but had some difficulties there with installation along with JupyterHub. Anyone got those working together?

Reason I’d like browsing is two-fold: First, I think it helps students orient themselves when they can see what databases/tables/columns exist (and SHOW TABLES or \dt are pretty unfriendly; also \dt and \d don’t appear to work in SQL magic or xeus-sql modes). Second, I think being able to see the data in the tables helps scaffold from their understanding of Excel.

But perhaps these aren’t needed. Any ideas on ordering?

1 Like
#2

Relatively soon, it will be possible to host a xeus-sqlite kernel in the browser with preloaded data, which might offer some low-IT ways to get to the basics from a static HTML host. Of course, sqlite is a different beast than the RDBMS mentioned, but perhaps for learning the basics of SELECT, etc. it can be sufficient. I don’t know if there are plans to further expand what it can do visually, though it does ship vega… so it might be possible to add such a beast.

As for browsing: I’d started some work on a grid-based browser and dumb query tool, also based on in-browser sqlite. Reframed for jupyterlite, this could be provide a kernel API and/or ERD-style display with e.g. graphviz, ELK, or drawio.

1 Like