Desktop database frontend application in under 100 lines using Python and Flask

README.md

A simple, standalone frontend for database editing using Python. It is based on the following libraries:

• sqlalchemy: database connections and data models
• Flask: web application framework
• Flask-Admin: provides ModelView class for data model editing
• pywebview: uses PyQT to provide webview GUI window containing application

Features

• Data models defined in Python using SQL Alchemy are database agnostic
• View and sort database table contents
• Form for creating and editing items
• Widgets such as foreign-key dropdowns and date pickers

To Run

Set up a Python virtual environment, then:

pip install -r requirements.txt
python app.py

A window will pop up containing the running application. Close the window to end.

Working with existing databases

This example uses a SQLite database, but SQLAlchemy can connect to many other types e.g. PostgreSQL or Oracle (via psycopg2 and cxOracle respectively). Just update the SQLALCHEMY_DATABASE_URI.

Use sqlacodegen to automatically generate model definitions from existing database tables.

app.py

"""Simple, single-file database frontend application."""
import datetime as dt
from http.client import HTTPConnection
from threading import Thread
from time import sleep

from flask import Flask
from flask_admin import Admin
from flask_admin.contrib.sqla import ModelView
from flask_sqlalchemy import SQLAlchemy
import webview

app = Flask(__name__)

# App config
app.config['SECRET_KEY'] = 'e9a9f6fa5fac'  # required for sessions
app.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:///db-frontend.sqlite"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Database setup
db = SQLAlchemy(app)


class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String, unique=True, nullable=False)
    email = db.Column(db.String, unique=True)
    role_id = db.Column(db.Integer, db.ForeignKey('role.id'))
    role = db.relationship('Role')
    update_date = db.Column(db.DateTime, default=dt.datetime.now)


class Role(db.Model):
    __tablename__ = 'role'
    id = db.Column(db.Integer, primary_key=True)
    role = db.Column(db.String, unique=True, nullable=False)
    update_date = db.Column(db.DateTime, default=dt.datetime.now)

    def __repr__(self):
        return str(self.role)


db.create_all()

# Add administrative views here
admin = Admin(app, name='db-frontend', template_mode='bootstrap3')
admin.add_view(ModelView(User, db.session))
admin.add_view(ModelView(Role, db.session))


# Configure webview
def url_ok(url, port):
    try:
        conn = HTTPConnection(url, port)
        conn.request('GET', '/admin/')
        response = conn.getresponse()
        return response.status == 200
    except Exception:
        return False


if __name__ == '__main__':
    # Start webapp
    t = Thread(target=app.run)
    t.daemon = True
    t.start()

    # Wait until started
    while not url_ok('127.0.0.1', 5000):
        sleep(1)

    # Open GUI window
    window = webview.create_window('DB Frontend Demo',
                                   'http://127.0.0.1:5000/admin')
    webview.start(debug=True)

requirements.txt

Flask
Flask-Admin
Flask-SQLAlchemy
pywebview[qt]