# ipython_magic_sqlalchemy_schemadisplay
Magic for [sqlalchemy_schemadisplay](https://github.com/fschulze/sqlalchemy_schemadisplay)
[](https://mybinder.org/v2/gh/innovationOUtside/ipython_magic_sqlalchemy_schemadisplay/master?filepath=notebooks/SchemaDemo.ipynb)
Magic originally created for the Open University module [TM351 Data Management and Analysis](http://www.open.ac.uk/courses/modules/tm351). This magic complements the [`ipython-sql`](https://github.com/catherinedevlin/ipython-sql), which provides magics for connecting to a SQL database, with a magic that uses [sqlalchemy_schemadisplay](https://github.com/fschulze/sqlalchemy_schemadisplay) to generate ERDs over a SQL database. *The code for generating the ER diagram can be found in the third party `sqlalchemy_schemadisplay` package.*
At the moment, the schema display and `ipython-sql` magics and independent, but they really should be combined into a single package.
## Usage
Install from PyPi: `pip install schemadisplay-magic`
Install from this repo:
`pip install git+https://github.com/innovationOUtside/ipython_magic_sqlalchemy_schemadisplay.git`
Note there are several other dependencies:
- Python: see *requirements.txt*
- O/S: see *apt.txt*
Set up a database. For example, load the SQL magic:
```python
%load_ext sql
```
Create a database connection string — we can use a SQLite database for demo purposes — and connect the SQL magic to the database:
```
DB_CONNECTION = 'sqlite:///./test.db'
%sql $DB_CONNECTION
```
Populate the database with a couple of foreign key related tables:
```
%%sql
DROP TABLE IF EXISTS doctor;
CREATE TABLE doctor (
doctor_id CHAR(4),
doctor_name VARCHAR(20),
PRIMARY KEY (doctor_id)
);
DROP TABLE IF EXISTS patient;
CREATE TABLE patient (
patient_id CHAR(4),
patient_name VARCHAR(20),
date_of_birth DATE,
gender CHAR(6),
height_cm DECIMAL(4,1),
weight_kg DECIMAL(4,1),
doctor_id CHAR(4),
PRIMARY KEY (patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor
);
```
Load the schema display magic, and render the schema from the connected database:
```python
%load_ext schemadisplay_magic
%schema --connection_string $DB_CONNECTION
```
<img src='example_erd.png' width=500/>
At the moment, the database connection string needs to be provided to the schem magic for each diagram. [TO DO - fix this to use a single persistemt connection for the life of the notebook session, once connected.]
## Using the Magic in a Teaching and Learning Context
The magic was developed to support teaching and learning around the topic of *relational databases*. Students were working with a PostgreSQL database, creating, editing and deleting tables, and creating foreign key relationships between tables. The magic provided an easy way to visualise the current state of the tables available in the database, and any foreign key relationships between them.
In this way, students could run database and table modifying statements in a notebook. A single line magic invocation could then be used to generate a visual representation of the current state of the database to check that their intended changes had worked correctly.
Raw data
{
"_id": null,
"home_page": "https://github.com/innovationOUtside/ipython_magic_sqlalchemy_schemadisplay",
"name": "schemadisplay-magic",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "Tony Hirst",
"author_email": "tony.hirst@open.ac.uk",
"download_url": "https://files.pythonhosted.org/packages/66/d1/a0131f8b6f3495fc2dc2a727b8d9d2c45f63552c7098176c83f2f3d8b177/schemadisplay-magic-0.0.7.tar.gz",
"platform": null,
"description": "# ipython_magic_sqlalchemy_schemadisplay\nMagic for [sqlalchemy_schemadisplay](https://github.com/fschulze/sqlalchemy_schemadisplay)\n\n[](https://mybinder.org/v2/gh/innovationOUtside/ipython_magic_sqlalchemy_schemadisplay/master?filepath=notebooks/SchemaDemo.ipynb)\n\nMagic originally created for the Open University module [TM351 Data Management and Analysis](http://www.open.ac.uk/courses/modules/tm351). This magic complements the [`ipython-sql`](https://github.com/catherinedevlin/ipython-sql), which provides magics for connecting to a SQL database, with a magic that uses [sqlalchemy_schemadisplay](https://github.com/fschulze/sqlalchemy_schemadisplay) to generate ERDs over a SQL database. *The code for generating the ER diagram can be found in the third party `sqlalchemy_schemadisplay` package.*\n\nAt the moment, the schema display and `ipython-sql` magics and independent, but they really should be combined into a single package.\n\n## Usage\n\nInstall from PyPi: `pip install schemadisplay-magic`\n\nInstall from this repo:\n\n`pip install git+https://github.com/innovationOUtside/ipython_magic_sqlalchemy_schemadisplay.git`\n\nNote there are several other dependencies:\n\n- Python: see *requirements.txt*\n- O/S: see *apt.txt*\n\nSet up a database. For example, load the SQL magic:\n\n```python\n%load_ext sql\n```\n\nCreate a database connection string \u2014 we can use a SQLite database for demo purposes \u2014 and connect the SQL magic to the database:\n\n```\nDB_CONNECTION = 'sqlite:///./test.db'\n%sql $DB_CONNECTION\n```\n\nPopulate the database with a couple of foreign key related tables:\n\n```\n%%sql\n\nDROP TABLE IF EXISTS doctor;\n\nCREATE TABLE doctor (\n \n doctor_id CHAR(4),\n doctor_name VARCHAR(20),\n \n PRIMARY KEY (doctor_id)\n );\n\nDROP TABLE IF EXISTS patient;\n\nCREATE TABLE patient (\n \n patient_id CHAR(4),\n patient_name VARCHAR(20),\n date_of_birth DATE,\n gender CHAR(6),\n height_cm DECIMAL(4,1),\n weight_kg DECIMAL(4,1),\n doctor_id CHAR(4),\n \n PRIMARY KEY (patient_id),\n \n FOREIGN KEY (doctor_id) REFERENCES doctor\n );\n \n```\n\nLoad the schema display magic, and render the schema from the connected database:\n```python\n%load_ext schemadisplay_magic\n%schema --connection_string $DB_CONNECTION\n```\n\n<img src='example_erd.png' width=500/>\n\nAt the moment, the database connection string needs to be provided to the schem magic for each diagram. [TO DO - fix this to use a single persistemt connection for the life of the notebook session, once connected.]\n\n\n## Using the Magic in a Teaching and Learning Context\n\nThe magic was developed to support teaching and learning around the topic of *relational databases*. Students were working with a PostgreSQL database, creating, editing and deleting tables, and creating foreign key relationships between tables. The magic provided an easy way to visualise the current state of the tables available in the database, and any foreign key relationships between them.\n\nIn this way, students could run database and table modifying statements in a notebook. A single line magic invocation could then be used to generate a visual representation of the current state of the database to check that their intended changes had worked correctly.\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Python package installation for ipython_magic_sqlalchemy_schemadisplay",
"version": "0.0.7",
"project_urls": {
"Homepage": "https://github.com/innovationOUtside/ipython_magic_sqlalchemy_schemadisplay"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "7c7b294fdb6c9d2a5a05a978c72e98bee486a985c806c21ca9da9569290b7b95",
"md5": "cf009464953efb34fc4c5cb8d84ad6ab",
"sha256": "7e7a015fe6a33bbb253cffc4bbf7ab6c01d3922357621e282a4a475087f4f3d4"
},
"downloads": -1,
"filename": "schemadisplay_magic-0.0.7-py3-none-any.whl",
"has_sig": false,
"md5_digest": "cf009464953efb34fc4c5cb8d84ad6ab",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 9580,
"upload_time": "2023-09-03T19:57:21",
"upload_time_iso_8601": "2023-09-03T19:57:21.853954Z",
"url": "https://files.pythonhosted.org/packages/7c/7b/294fdb6c9d2a5a05a978c72e98bee486a985c806c21ca9da9569290b7b95/schemadisplay_magic-0.0.7-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "66d1a0131f8b6f3495fc2dc2a727b8d9d2c45f63552c7098176c83f2f3d8b177",
"md5": "81290aba4266d3ac6238cb22b90a22a8",
"sha256": "cf5bccd458eb72321dbd60db2190a8f512e4de67e913fe93d62cbc69c81b73f8"
},
"downloads": -1,
"filename": "schemadisplay-magic-0.0.7.tar.gz",
"has_sig": false,
"md5_digest": "81290aba4266d3ac6238cb22b90a22a8",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 9087,
"upload_time": "2023-09-03T19:57:23",
"upload_time_iso_8601": "2023-09-03T19:57:23.331448Z",
"url": "https://files.pythonhosted.org/packages/66/d1/a0131f8b6f3495fc2dc2a727b8d9d2c45f63552c7098176c83f2f3d8b177/schemadisplay-magic-0.0.7.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-09-03 19:57:23",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "innovationOUtside",
"github_project": "ipython_magic_sqlalchemy_schemadisplay",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"requirements": [],
"lcname": "schemadisplay-magic"
}
JSON
