DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

Trending

  • From On-Prem to SaaS
  • Extending Java APIs: Add Missing Features Without the Hassle
  • Health Check Response Format for HTTP APIs
  • The SPACE Framework for Developer Productivity

Trending

  • From On-Prem to SaaS
  • Extending Java APIs: Add Missing Features Without the Hassle
  • Health Check Response Format for HTTP APIs
  • The SPACE Framework for Developer Productivity
  1. DZone
  2. Data Engineering
  3. Databases
  4. Instant DB Web Apps

Instant DB Web Apps

Create a multi-page, multi-table web app in 10 minutes.

Val Huber user avatar by
Val Huber
·
Aug. 25, 20 · Tutorial
Like (2)
Save
Tweet
Share
8.00K Views

Join the DZone community and get the full member experience.

Join For Free

Here's how to create a basic web app for your database in 10 minutes.  Let's explore the created app, and then the creation process.

Created Web App

As shown below, the apps are

  1. Multi-page: apps include 1 page per table.  The screen show below shows a List Customer screen (with query), a Show (one) Customer page, and a Show Order page.  Navigation controls enable you to transition between these pages.
  2. Multi-table: pages include related_views for each related child table, and join in parent data
  3. Favorite field first: first-displayed field is "name", or contains "name" (configurable)
  4. Predictive joins: favorite field of each parent is shown (product name - not the foreign key product_id_)
  5. Ids last: such boring fields are not shown on lists, and at the end on other pages

The Challenge: Conventional Approaches Not Agile

Conventional approaches where you create pages a field at a time with a screen painter, or use Wizards to create pages, will not create such an app in 10 minutes.  These also often require not only complicated coding, but also installing and configuring an IDE, frameworks, Web Servers, etc.  Not agile.

Instead, we need an agile approach that introspects the data model, and creates not only all the pages, but also the transitions.

Creation Process

Let's see how to create this app.

Background

As shown below, Flask AppBuilder (FAB) is a Python-based open source tool that uses sqlalchemy (an ORM for database access), and Flask (a micro web framework):

To drive FAB, you need to provide 2 model files:

  1. models.py: describes your database tables
  2. views.py: describes your pages

Each of these consists of a snippet of code you repeat for each table and page:

models.py

Java
 




xxxxxxxxxx
1
11


 
1
class OrderDetail(BaseMixin, Model):
2
    __tablename__ = 'OrderDetail'
3

          
4
    Id = Column(String(8000), primary_key=True)
5
    OrderId = Column(Integer, ForeignKey("Order.Id"), nullable=False)
6
    Order = relationship("Order")
7
    ProductId = Column(Integer, ForeignKey("Product.Id"), nullable=False)
8
    Product = relationship("Product")
9
    UnitPrice = Column(DECIMAL, nullable=False)
10
    Quantity = Column(Integer, nullable=False)
11
    Discount = Column(Float, nullable=False)



views.py 

Java
 




xxxxxxxxxx
1
10


 
1
class OrderModelView(ModelView):
2
   datamodel = SQLAInterface(Order)
3
   list_columns = ["ShipName", "Customer.CompanyName", ... "EmployeeId", "CustomerId"]
4
   show_columns = ["ShipName", "Customer.CompanyName", "OrderDate", ... "ShipCountry", "Id", "EmployeeId", "CustomerId"]
5
   edit_columns = ["ShipName", "OrderDate",... "ShipCountry", "Id", "EmployeeId", "CustomerId"]
6
   add_columns = ["ShipName", "OrderDate", ... "ShipCountry", "Id", "EmployeeId", "CustomerId"]
7
   related_views = [OrderDetailModelView]
8

          
9
appbuilder.add_view(
10
      OrderModelView, "Order List", icon="fa-folder-open-o", category="Menu")



While straightforward, these take time to learn, and are tedious to construct.  Happily, the 2 generators shown build these with a single command line.  Let's see how.

Process

So, the process is as follows.  First, you need Python3, along with virtualenv (a Best Practice for managing and separating project dependencies), and pip (Python installation utility).  You can install these in a minute or two as described here here.

We'll be using a sqllite version of Northwind (Customers, Orders, OrderDetails, Products, etc).

1. Create an environment

This creates a folder for our project, and an environment to manage dependencies:

Java
 




xxxxxxxxxx
1


 
1
mkdir nw
2
cd nw
3
virtualenv venv
4
# windows .env\Scripts\activate
5
source venv/bin/activate



2. Create an empty FAB Project

Install FAB (and dependencies, such as Flask/SqlAlchemy), and create a default empty FAB app:

Java
 




xxxxxxxxxx
1


 
1
pip install flask-appbuilder
2
flask fab create-app


You will then be prompted for the project name and your db engine type. When prompted:

  • Use the default engine
  • Name the project nw-app

You should see a structure as shown in the screen shot in the next section.

We now have a well-formed empty project. We now need to acquire and configure a database, set up SQLAlchemy ORM models.py, and describe our pages with views.py.

3, Configure Database

To get the database:

  1. Download this file, a sqlite version of Northwind
  2. Copy it to your nw-app folder
  3. Update your nw-app/config.py file to denote this database name (illustrated below): Northwind_small.sqlite

Your project will look something like this (from VSCode, an IDE - optional, install it like this):

4. Create models.py

You must next provide model classes for SQLAlchemy. That's a bit of work (13 classes in this small example), but we can automate this with sqlacodegen:

Java
 




xxxxxxxxxx
1


 
1
cd nw-app
2
pip install sqlacodegen
3
sqlacodegen sqlite:///Northwind_small.sqlite --noviews > app/models.py


This overwrites your nw/nw-app/app/models.py module. For more information, see the sqlacodegen docs.

5. Define views.py

Finally, we need to define some pages. That's also a bit of work to do that by hand, so let's use fab-quick-start to create the views.py file:

Java
 




xxxxxxxxxx
1


 
1
pip install fab-quick-start
2
fab-quick-start run --favorites="name description" --non_favorites="id" > app/views.py



This overwrites your nw/nw-app/app/views.py file. For more information, see the FAB Quick Start Utility docs.

6. Create Admin

The FAB system can create tables in your database for authenticating and authorizing users (tables such as ab_user, ab_user_role, etc). You create these as follows (Username: admin, Password: p):

Java
 




xxxxxxxxxx
1


 
1
(venv)$ export FLASK_APP=app
2
(venv)$ flask fab create-admin
3
Username [admin]:
4
User first name [admin]:
5
User last name [user]:
6
Email [admin@fab.org]:
7
Password:
8
Repeat for confirmation:



Ignore the error "user already exists", since the admin data was pre-loaded in the sample database.

7. Run `nw' App

You've now created a app with a dozen pages or so; run it like this:

Java
 




xxxxxxxxxx
1


 
1
(venv)$ # still cd'd to nw-app
2
(venv)$ export FLASK_APP=app
3
(venv)$ flask run



Start your browser here.

Business Value

So, 10 minutes for a 13 table app.  It is a testament to the Python infrastructure that the process was not only fast, but also that it required no programming knowledge, nor often-formidable install / configuration skills.

There are several ways such automatic web apps can contribute to your project.

Agile: Collaboration With Working Software NOW

Agile is based on Collaboration based on Working Software, and there's no surer way to communicate than with running screens.

Approaches like FAB provide Working Software NOW, to kickstart your agile process.  Even if the ultimate User Interface is much different, collaboration over such early running prototypes can provide early clarification of the data model, and provoke discussions about the underlying business logic and process flow.

Data Exploration and Maintenance

In encountering a new database, developers typically require a database diagram.  But in my own experience, I need to explore the data - and their relationships - to really understand the database.   With automated navigation and joins, this is infinitely simpler than writing dozens of sql scripts.

Back Office Production Use

Most systems have a number of admin aka maintenance screens that are not strategic, yet are expensive.  FAB can make these basic apps free, leaving you more time to focus on the custom apps.  In fact, approaches analogous to FAB are used in many large-scale CRM/ERP systems.

Acknowledgments

Key software illustrated here include the following open source projects and authors:

  • Flask App Builder, by Daniel Vaz Gaspar
  • Flask, by Armin Ronacher
  • SqlAlchemy, by Mike Bayer
  • sqlacodegen, by Alex Grönholm
  • fab-quick-start, the latest step in pursuing this vision

Next Steps

Follow this link for a Quick Start to Python, FAB, SqlAlchemy, and fab-quick-start.

app Web apps Database Open source Java (programming language)

Published at DZone with permission of , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • From On-Prem to SaaS
  • Extending Java APIs: Add Missing Features Without the Hassle
  • Health Check Response Format for HTTP APIs
  • The SPACE Framework for Developer Productivity

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: