Instant DB Web Apps
Create a multi-page, multi-table web app in 10 minutes.
Join the DZone community and get the full member experience.
Join For FreeHere'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
- 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.
- Multi-table: pages include
related_views
for each related child table, and join in parent data - Favorite field first: first-displayed field is "name", or contains "name" (configurable)
- Predictive joins: favorite field of each parent is shown (product name - not the foreign key
product_id_
) - 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:
models.py
: describes your database tablesviews.py
: describes your pages
Each of these consists of a snippet of code you repeat for each table and page:
models.py
xxxxxxxxxx
class OrderDetail(BaseMixin, Model):
__tablename__ = 'OrderDetail'
Id = Column(String(8000), primary_key=True)
OrderId = Column(Integer, ForeignKey("Order.Id"), nullable=False)
Order = relationship("Order")
ProductId = Column(Integer, ForeignKey("Product.Id"), nullable=False)
Product = relationship("Product")
UnitPrice = Column(DECIMAL, nullable=False)
Quantity = Column(Integer, nullable=False)
Discount = Column(Float, nullable=False)
views.py
xxxxxxxxxx
class OrderModelView(ModelView):
datamodel = SQLAInterface(Order)
list_columns = ["ShipName", "Customer.CompanyName", ... "EmployeeId", "CustomerId"]
show_columns = ["ShipName", "Customer.CompanyName", "OrderDate", ... "ShipCountry", "Id", "EmployeeId", "CustomerId"]
edit_columns = ["ShipName", "OrderDate",... "ShipCountry", "Id", "EmployeeId", "CustomerId"]
add_columns = ["ShipName", "OrderDate", ... "ShipCountry", "Id", "EmployeeId", "CustomerId"]
related_views = [OrderDetailModelView]
appbuilder.add_view(
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:
xxxxxxxxxx
mkdir nw
cd nw
virtualenv venv
# windows .env\Scripts\activate
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:
xxxxxxxxxx
pip install flask-appbuilder
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:
- Download this file, a sqlite version of Northwind
- Copy it to your
nw-app
folder - 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:
xxxxxxxxxx
cd nw-app
pip install sqlacodegen
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:
xxxxxxxxxx
pip install fab-quick-start
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
):
xxxxxxxxxx
(venv)$ export FLASK_APP=app
(venv)$ flask fab create-admin
Username [admin]:
User first name [admin]:
User last name [user]:
Email [admin .org]:
Password:
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:
xxxxxxxxxx
(venv)$ # still cd'd to nw-app
(venv)$ export FLASK_APP=app
(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.
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