I have referenced a lot on Mike Driscoll's blog https://www.blog.pythonlibrary.org/ ,

Recently, I had to install MySQL on Odroid XU4 SBC and connect it remotely. I really hate GUI programming. I love the cli interface. However, to provide a simple GUI interface for simple table lookup, modification,, I decided to create a web page using Flask.

I will skip the content of installing MySQL. You will find numerous explanations for this on the Internet.

I'm going to use SQLAlchemy, I thought it was nice that there is a ready-made extension for adding SQLAlchemy to Flask called Flask-SQLAlchemy. SQLAlchemy can link not only MySQL but also lightweight DB such as SQLite and RDBMS such as PostgreSQL.

All the work below was done by Ubuntu 18.04 on Odroid XU-4.

Prerequisites

Several blogs have explained how to implement Flask.

Installing SQLAlchemy

Before installing SQLAlchemy, you need to install MySQLClient first.

Installing mysqlclient

pip3 install flask
apt-get install python3-dev default-libmysqlclient-dev build-essential
pip3 install mysqlclient

Installing SQLAlchemy

pip3 install flask-sqlalchemy wtforms flask_table

You are now ready to start working with MySQL in Flask.

Create Table using SQLAlchemy

Let's create a table using SQLAlchemy. Creating a database with SQLAlchemy is actually pretty easy. SQLAlchemy supports a couple of different ways of working with a database. I'm going to use its declarative syntax that allows you to create classes that model the database itself. So I will use that for this example.

First I created "test "MySQL schema for testing. Therefore, all tests will be done in the "test" schema.

# create_tables.py
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('mysql://ivr:ROOT@mysql123@192.168.11.206/test')
Base = declarative_base()


class Holiday(Base):
    __tablename__ = 't_sip_holiday'
    c_acdcode = Column(String(8), primary_key=True)
    c_date = Column(String(8), primary_key=True)
    c_datetype = Column(String(1))
    c_desc = Column(String(255))


class TimeTable(Base):
    __tablename__ = 't_sip_timetable'
    c_acdcode = Column(String(4), primary_key=True)
    c_datetype = Column(String(1), primary_key=True)  #0 : Sunday, ~ 6:Saturday
    c_timetype = Column(String(1), primary_key=True)  #1 : WorkTime  2: BreakTime
    c_starttime = Column(String(4))
    c_endtime = Column(String(4))
    c_desc = Column(String(255))


Base.metadata.create_all(engine)

<create_tables.py>

This program will create the t_holiday table in the test schema of the MySQL database located at 192.168.11.206.

If you run the above python code, perhaps you can see that the table is created like this:

root@ppirgib01:/usr/local/src/flask_web# python3 create_tables.py
root@ppirgib01:/usr/local/src/flask_web# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.31-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| t_sip_holiday    |

| t_sip_timetables |
+------------------+
1 row in set (0.00 sec)

I tried creating a simple table using SQLAlchemy. This time, let's see how to use SQLAlchemy combined with flask.

Be Careful : The primary key must exist in the table to use SQLAlchemy.

Using Flask-SQLAlchemy

The first thing we need to do when we go to use Flask-SQLAlchemy is to create a simple application script. We will call it app.py.

# app.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
my_ip = '192.168.11.206'
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://ivr:ROOT@mysql123@%s/pbx'%(my_ip)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = "flask rocks!"
app.config['MYSQL_CHARSET'] = 'utf8'

db = SQLAlchemy(app)

<app.py>

Next I created model.py that describes 2 tables(t_sip_holiday, t_sip_timetable).

# models.py 

from app import db

class Holiday(db.Model):
    __tablename__ = 't_sip_holiday'
    c_acdcode = db.Column(db.String(8), primary_key=True)
    c_date = db.Column(db.String(8), primary_key=True)
    c_datetype = db.Column(db.String(1))
    c_desc = db.Column(db.String(255))

    def __init__(self, c_acdcode, c_date, c_datetype, c_desc):
        self.c_acdcode = c_acdcode
        self.c_date = c_date
        self.c_datetype = c_datetype
        self.c_desc = c_desc

    def __repr__(self):
        return "<holiday('%s', '%s', '%s', '%s')>" % (self.c_acd, self.c_date, self.c_datetype, self.c_desc)
		
class TimeTable(db.Model):
    __tablename__ = 't_sip_timetable'
    c_acdcode = db.Column(db.String(4), primary_key=True)
    c_datetype = db.Column(db.String(1), primary_key=True)  #0 : Sunday, ~ 6:Saturday
    c_timetype = db.Column(db.String(1), primary_key=True)  #1 : WorkTime  2: BreakTime
    c_starttime = db.Column(db.String(4))
    c_endtime = db.Column(db.String(4))
    c_desc = db.Column(db.String(255))

    def __init__(self, c_acdcode, c_datetype, c_timetype, c_starttime, c_endtime, c_desc):
        self.c_acdcode = c_acdcode
        self.c_datetype = c_datetype
        self.c_timetype = c_timetype
        self.c_starttime = c_starttime
        self.c_endtime = c_endtime
        self.c_desc = c_desc

    def __repr__(self):
        return "<time table('%s', '%s', '%s', '%s', '%s', '%s')>" % (self.c_acdcode, self.c_datetype, self.c_timetype, self.c_starttime, self.c_endtime, self.c_desc)

<model.py>

The model.py file will be used to display screens on the Flask Web shortly.

from flask_table import Table, Col, LinkCol

class HolidayResults(Table):
    c_acdcode = Col('c_acdcode')
    c_date = Col('c_date')
    c_datetype = Col('c_datetype')
    c_desc = Col('c_desc')
    edit = LinkCol('  Modify  ', 'holiday_edit', url_kwargs=dict(edit_date='c_date'))
    delete = LinkCol('  Delete  ', 'holiday_del', url_kwargs=dict(del_date='c_date'))


class TimeTableResults(Table):
    c_acdcode = Col('c_acdcode')
    c_datetype = Col('c_datetype')
    c_timetype = Col('c_timetype')
    c_starttime = Col('c_starttime')
    c_endtime = Col('c_endtime')
    c_desc = Col('c_desc')
    edit = LinkCol('  Modify  ', 'timetable_edit', url_kwargs=dict(edit_datetype='c_datetype', edit_timetype = 'c_timetype'))
<model.py>

<table.py>

Table data lookup

First, create a flask code that inquires the data of the two tables you created earlier.

The directory structure is as follows. The templates directory contains the html files required for screen configuration.

root@ppirgib01:/usr/local/src/flask_web# tree
.
|-- app.py
|-- check_flask.sh
|-- create_tables.py
|-- db_setup.py
|-- flask_main.py
|-- flask_test.py
|-- flask_test2.py
|-- flask_web.sh
|-- holidayform.py
|-- model.py
|-- table.py
`-- templates
    |-- _formhelpers.html
    |-- alert.html
    |-- edit_holiday.html
    |-- edit_timetable.html
    |-- holiday.html
    |-- holiday_results.html
    |-- index.html
    |-- new_holiday.html
    `-- worktime.html

I'll put some data in advance for the test. I will begin with the following flask file.

#!/usr/bin/env python3
# # -*- coding: utf-8 -*-
# main.py
from app import app, db
from db_setup import init_db, db_session
from holidayform import HolidayForm, TimeTableForm
from flask import flash, render_template, request, redirect
from model import Holiday, TimeTable
from table import HolidayResults, TimeTableResults
import pidfile
import os, sys
import signal


@app.route('/', methods=['GET', 'POST'])
def index():
    return render_template("index.html")

@app.route('/holiday', methods=['GET', 'POST'])
def holiday_main():
    results = []
    qry = db_session.query(Holiday).filter(Holiday.c_acdcode=='0202')
    results = qry.all()
    if results:
        table = HolidayResults(results)
        table.border = True
    return render_template("holiday.html", table=table)
   
@app.route('/worktime', methods=['GET', 'POST'])
def worktime_main():
    results = []
    qry = db_session.query(TimeTable).filter(TimeTable.c_acdcode=='0202')
    results = qry.all()
    if results:
        table = TimeTableResults(results)
        table.border = True
    return render_template("worktime.html", table=table)

@app.route('/new_holiday', methods=['GET', 'POST'])
def new_holiday():
    """
    Add a new holiday
    """
    form = HolidayForm(request.form)
    if request.method == 'POST' and form.validate():
        h_desc = form.c_desc.data.encode("utf-8", errors="ignore").decode("utf-8")
        h_date = form.c_date.data

        # print('return value:%s %s'%(h_date, h_desc))
        # print('return value:%s %s'%(type(form.t_date.data), len(form.t_desc.data)))
        hday = Holiday(c_acdcode = "0202", c_date = h_date, c_datetype = "7", c_desc = h_desc)
        # print('Object value : %s %s'%(hday.t_date, hday.t_desc))
        db_session.add(hday)
        db_session.commit()

        return redirect('/holiday')
    return render_template('new_holiday.html', form=form)

@app.route('/holiday_del/<string:del_date>', methods=['GET', 'POST'])
def holiday_del(del_date):
    print('holiday del')
    qry = db_session.query(Holiday).filter(Holiday.c_acdcode=='0202').filter(Holiday.c_date == del_date)
    hday = qry.first()
    db_session.delete(hday)
    db_session.commit()    
    print('Holiday delete successfully!')
    return redirect('/holiday')

@app.route('/holiday_edit/<string:edit_date>', methods=['GET', 'POST'])
def holiday_edit(edit_date):
    print('holiday edit [%s] '%edit_date)
    qry = db_session.query(Holiday).filter(Holiday.c_acdcode=='0202').filter(Holiday.c_date == edit_date)
    hday = qry.first()
    form = HolidayForm(formdata=request.form, obj=hday)

    if request.method == 'POST' and form.validate():
        hday.c_date = form.c_date.data      
        hday.c_desc = form.c_desc.data.encode("utf-8", errors="ignore").decode("utf-8") 
        db_session.commit()    
        print('Holiday updated successfully!')
        return redirect('/holiday')
    if hday:
        return render_template('edit_holiday.html', form=form)
    else:
        return 'Error loading #{id}'.format(id=id) 


@app.route('/timetable_edit', methods=['GET', 'POST'])
def timetable_edit():
    edit_datetype = request.args.get('edit_datetype')
    edit_timetype = request.args.get('edit_timetype')
    print('timetable_edit datetype [%s]  timetype[%s]'%(edit_datetype, edit_timetype))

    qry = db_session.query(TimeTable).filter(TimeTable.c_acdcode=='0202').filter(TimeTable.c_datetype == edit_datetype).filter(TimeTable.c_timetype == edit_timetype)
    t_table = qry.first()
    form = TimeTableForm(formdata=request.form, obj=t_table)
    if request.method == 'POST' and form.validate():
        t_table.c_starttime = form.c_starttime.data      
        t_table.c_endtime = form.c_endtime.data
        db_session.commit()    
        print('TimeTable updated successfully! start:%s end:%s'%(form.c_starttime.data, form.c_endtime.data) )
        return redirect('/worktime')
    if t_table:
        return render_template('edit_timetable.html', form=form)
    else:
        return 'Error loading #{id}'.format(id=id) 


init_db()

app.run(host="0.0.0.0", port="8800")

<flask_test.py>

Run this python.

root@ppirgib01:/usr/local/src/flask_web# python3 flask_test.py 
Create tables if not exists
 * Serving Flask app "app" (lazy loading)
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://0.0.0.0:8800/ (Press CTRL+C to quit)

Now connect the flask web using your browser.

This page shows index.html. This page is very simple and does not contain any special functions. Two buttons are route the page to /holiday, /worktime.

<index.html>

<holiday.html>

If you click on the “Add Holiday” link, then you should see something like this in your browser:

<new_holiday.html>

<worktime.html>

If you click on the “Modify” link, then you should see something like this in your browser:

<edit_timetable.html>

Wrapping Up

It's been a long time since I quit programming that provides a user interface. In the days when web services were not as popular as they are now, I developed a server/client structure. The screen is rough because I don't know the web interface at all. Rather than targeting end users, it is more for developers. But it was GUI programming that I challenged for the first time in a long time. Please look at the parts that use DB in Flask rather than Web UI.

You can download the source codes at https://github.com/raspberry-pi-maker/C-Python-Cooking .

이 블로그 검색

C/C++ Python Cooking

Python Flask - MySQL connection using SQLAlchemy

Prerequisites

Installing SQLAlchemy

Installing mysqlclient

Installing SQLAlchemy

Create Table using SQLAlchemy

Using Flask-SQLAlchemy

Table data lookup

Wrapping Up

댓글

댓글 쓰기

이 블로그의 인기 게시물

MQTT - C/C++ Client

RabbitMQ - C++ Client #1 : Installing C/C++ Libraries

C/C++ - Everything about time, date