Using SQLAlchemy and Flask-admin
In my project, we have a data driven application, with a heavy focus on the database portion. The application is such that the data required comes from the government, so the database is populated with information from the government and needs to be routinely updated. Apart from just cron jobs to populate the database, there is also functionality to insert into certain tables from a Flask application. The architecture of the application consists of a PostgreSQL database and a Python program consisted of parsing scripts that handle the ingestion of the data from the government. This forms the backend, which has python workflows that create scheduled reports from the database.
Flask admin comes into play here for certain tables because we have customer information also within the same database, which sometimes needs to be updated. Using the SQLAlchemy automap feature, we are able to create models of certain tables from the database, and then we can use Flask-admin to create a web interface that allows the admin to update customers. Automap base handles the creation of a Python object that properly does the foreign key relationships of every table. This means that I can create an object that has a foreign key linkage, and when I commit that object into the PostgreSQL database, the ORM inserts into all the required tables for you.
from sqlalchemy import create_engine, MetaData from sqlalchemy.ext.automap import automap_base from app import app DBNAME_engine = create_engine(app.config['DB_URI'], echo=True) metadata = MetaData() CHOSEN_TABLES = [ 'customer', 'customer_meter_detail', 'registry', 'address', 'contact', 'customer_bank_detail', ] metadata.reflect(DBNAME_engine, only=CHOSEN_TABLES) Base = automap_base(metadata=metadata) Base.prepare() Customer = Base.classes.customer Registry = Base.classes.registry CustomerMeter = Base.classes.customer_meter_detail Address = Base.classes.address Contact = Base.classes.contact CustomerBankDetail = Base.classes.customer_bank_detail
This differs from the usual usage of a web application where the database is only ever handled as a single entity, so there is no need to hide some of the tables from the rest of the domain. Since SQLAlchemy already has an object created for each of the required tables, we can use these objects in Flask-admin by extending the
from flask.ext.admin.contrib.sqla import ModelView from flask.ext.admin import Admin admin = Admin(app, name='Editing Customer Info') admin.add_view(CustomerAdminView(Customer, session)) admin.add_view(ContactAdminView(Contact, session)) admin.add_view(AddressAdminView(Address, session))
Doing so, you now have an easy admin access for those selected tables.