<jupyter_start><jupyter_text>How to use Flask-sqlalchemy db.Model derived classes in JupyterLab 20201111 : Web applications developed using Flask and Flask-sqlalchemy use a declarative base class db.Model to implement SQLAlchemy. I often want to develop and test classes derived from db.Model in JupyterLab.  This notebook gives a pattern showing how to import the classes into JupyterLab, create and push the Flask app, and connect to multiple databases using the Flask db = SQLAlchemy() instance.This example uses sqlite but the pattern is applicable to other databases.  This notebook connects to three simple and arbitrary databases.  Import Flask app, SqlAlchemy instance, and database table classes<jupyter_code># Input app path and directory
run_config = 'development' # 'development' or 'production'. Both are same in this example.
app_directory = 'jupyterlab_flask_sqlalchemy'
app_path = '/Users/Phil/Documents/webapps/' + app_directory
# Imports
import sys

from platform import python_version
print(f"Python version : {python_version()}")

if app_path not in sys.path:
    sys.path.append(app_path)
    
from app import db, create_app
from app.models_chinook import Album, Artist
from app.models_db2 import Company, Country, Employee
from app.models_db3 import Pet, User, Post
# create flask app which creates sqlalchemy object 'db'
current_app = create_app(run_config)
current_app.app_context().push()<jupyter_output>PAB> Python version 3.9.0 (v3.9.0:9cf6752276, Oct  5 2020, 11:29:23) 
[Clang 6.0 (clang-600.0.57)]
PAB> Run configuration = development<jupyter_text>Examples using Flask-sqlalchemy db object<jupyter_code>print(f"db.session is : {db.session}")
print(f"\nDefault database is : {db.get_engine(bind=None)}")
print(f"\nDB2 is : {db.get_engine(bind='db2')}")
print(f"\nDB3 is : {db.get_engine(bind='db3')}")
db.get_engine(bind=None).table_names()
db.get_engine(bind='db2').table_names()
db.get_engine(bind='db3').table_names()<jupyter_output><empty_output><jupyter_text>Sample sqlalchemy queries  Query the default database (Chinook)<jupyter_code>q = db.session.query(Album).first()
q.title
q = db.session.query(Album).filter(Album.id==1).first()
q.title
# Get all records for artistid
q = db.session.query(Album).filter(Album.artistid==1).all()
for item in q:
    print(item.id, '\t', item.artistid, '\t', item.title)
q = db.session.query(Album, Artist).filter(Album.artistid==1).filter(Artist.id==1)
for item in q:
    print(item.Album.id, '\t', item.Album.artistid, '\t', item.Artist.name, '\t', item.Album.title)
q = db.session.query(Album, Artist).filter(Artist.name=='AC/DC').filter(Album.artistid==Artist.id)
for item in q:
    print(item.Album.id, '\t', item.Album.artistid, '\t', item.Artist.name, '\t', item.Album.title)<jupyter_output>1 	 1 	 AC/DC 	 For Those About To Rock We Salute You
4 	 1 	 AC/DC 	 Let There Be Rock<jupyter_text>Query database 2 (DB2)<jupyter_code>q = db.session.query(Employee).all()
for item in q:
    print(item.id, '\t', item.last_name, '\t', item.company_id)
q = db.session.query(Company).all()
for item in q:
    print(item.id, '\t', item.name)
q = db.session.query(Company, Employee).filter(Company.name=='Samsung Electronics').filter(Company.id==Employee.company_id)
for item in q:
    print(item.Company.id, '\t', item.Company.name, '\t', item.Employee.last_name)<jupyter_output>1 	 Samsung Electronics 	 Adams
1 	 Samsung Electronics 	 Park
1 	 Samsung Electronics 	 Johnson<jupyter_text>Query database 3 (DB3)<jupyter_code>q = db.session.query(Pet)
for item in q:
    print(item.name, '\t', item.animal, '\t', item.owner_id)
q = db.session.query(Pet, User).filter(Pet.name=='Baron').filter(User.id==Pet.owner_id)
for item in q:
    print(item.Pet.name, '\t', item.Pet.animal, '\t', item.Pet.owner_id, '\t', item.User.name)<jupyter_output>Baron 	 dog 	 3 	 <NAME><jupyter_text>Close sqlalchemy session<jupyter_code>db.session.close_all()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13418043202033036, 'fraction_numerical': 0.016518424396442185, 'mean_word_length': 5.338164251207729, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 24, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '564628', 'license': 'MIT', 'n_tokens_mistral': 1457, 'n_tokens_neox': 1353, 'n_words': 353, 'path': 'jupyterlab_flask_sqlalchemy.ipynb', 'repo_name': 'philblower/jupyterlab_flask_sqlalchemy_pattern', 'size': '3943'}