Skip to content

Commit v0.12

Jump to bottom
kwmccabe edited this page Apr 17, 2018 · 8 revisions

v0.12 - Hello DB: MySQL service, Flask-SQLAlchemy, MySQL-Connector-Python

Files changed (15)

File .gitignore MODIFIED

  • Exclude instance data from repo.
 *.py[cod]
 *.log
 .DS_Store
+mysql/data

File docker-compose.yml MODIFIED

  • Create new database service db.
  • The build: step uses the file ./mysql/Dockerfile.
  • container_name: flaskapp_db implies there can be only one of these services running.
  • The environment: values are used by MySQL during setup.
  • The volumes: setting links the local directory ./mysql/data to the container's /var/lib/mysql, so data is preserved between restarts.
+    db:
+        build: ./mysql
+        container_name: flaskapp_db
+        environment:
+            MYSQL_ROOT_PASSWORD: root-password
+            MYSQL_DATABASE: flaskapp
+            MYSQL_USER: python
+            MYSQL_PASSWORD: python-pass
+        ports:
+            - "3306:3306"
+        restart: always
+        volumes:
+            - ./mysql/data:/var/lib/mysql

File mysql/Dockerfile ADDED

  • Build container for MySQL 5.7.
  • Copy initialization scripts to container directory /docker-entrypoint-initdb.d.
  • Scripts are executed in alphabetical order.
  • Scripts are executed ONLY when the database is first created.
+# base container image
+FROM mysql:5.7
+
+# Add SQL files to run on the container creation
+ADD scripts/tables.sql   /docker-entrypoint-initdb.d/1-tables.sql
+ADD scripts/seeddata.sql /docker-entrypoint-initdb.d/2-seeddata.sql

File mysql/bin/db_connect.sh ADDED

  • Utility script connects to the flaskapp_db container and runs mysql to connect to the database.
+#/bin/sh
+
+# root-password
+#docker exec -it flaskapp_db mysql -p -u root
+
+# python-pass
+docker exec -it flaskapp_db mysql -p -u python -D flaskapp

File mysql/scripts/seeddata.sql ADDED

  • Insert some data into the item table.
  • File is copied to /docker-entrypoint-initdb.d/. during container setup.
  • File is run ONLY when database is first initialized.
+-- -------------------------------------------- --
+-- create FlaskApp DB objects
+-- ./bin/mysql_connect.sh < ./scripts/seeddata.sql
+--
+-- INSERT handled automatically when first lauching MySQL/Docker container
+--   using this file, copied to /docker-entrypoint-initdb.d/.
+-- -------------------------------------------- --
+
+USE `flaskapp`;
+
+DELETE FROM `item`;
+INSERT INTO `item` (keyname) VALUES ("one");
+INSERT INTO `item` (keyname) VALUES ("two");
+INSERT INTO `item` (keyname) VALUES ("three");
+OPTIMIZE TABLE `item`;

File mysql/scripts/tables.sql ADDED

  • Create minimal item table.
  • File is copied to /docker-entrypoint-initdb.d/. during container setup.
  • File is run ONLY when database is first initialized.
+-- -------------------------------------------- --
+-- create FlaskApp DB objects
+-- ./bin/mysql_connect.sh < ./scripts/tables.sql
+--
+-- CREATE handled automatically when first lauching MySQL/Docker container
+--   using this file, copied to /docker-entrypoint-initdb.d/.
+-- -------------------------------------------- --
+
+USE `flaskapp`;
+
+-- -------------------------------------------- --
+-- item
+-- -------------------------------------------- --
+DROP TABLE IF EXISTS `item`;
+CREATE TABLE `item` (
+    `id`            bigint(20)      NOT NULL AUTO_INCREMENT,
+    `keyname`       varchar(63)     NOT NULL,
+    `mod_create`    datetime        DEFAULT CURRENT_TIMESTAMP,
+
+    PRIMARY KEY (`id`),
+    UNIQUE KEY `item_keyname` (`keyname`)
+) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;
+DESCRIBE `item`;
+SELECT "table `item` created" AS MSG;

File web/app/init.py MODIFIED

  • Add db = SQLAlchemy() to application.
 from flask import Flask
 from flask_bootstrap import Bootstrap
+from flask_sqlalchemy import SQLAlchemy
 from config import config
 
 bootstrap = Bootstrap()
+db = SQLAlchemy()
 
...
 
     bootstrap.init_app(app)
+    db.init_app(app)

File web/app/main/templates/base.html MODIFIED

  • Install site logo.
  • Add a homepage link to the site logo.
         <div class="row">
             <div id="header_left" class="col-md-4">
                 {% block header_left %}
-<img id="site_logo" src="/static/media/dot_blank.gif" width="100" />
+<a href="{{ url_for('main.main_home') }}">
+<img id="site_logo" src="/static/media/pdxcodeguild.png" width="80" />
+</a>
 <span id="site_title">FlaskApp</span>
                 {% endblock %}
             </div>

File web/app/main/views.py MODIFIED

  • Add a minimal index page for routes under /info/.
+@main.route('/info/')
+def main_info():
+    result = ''
+    result += '<br/><a href="'+url_for('.info_date')+'">show datetime</a>'
+    result += '<br/><a href="'+url_for('.info_config')+'">show app.config</a>'
+    result += '<br/><a href="'+url_for('.info_url_map')+'">show url_map</a>'
+    return result

File web/app/static/css/flaskapp.css MODIFIED

  • Shrink margins in the header section of each page.
  • Match site_title font color with new logo.
 #main   { min-height: 320px; }
 #footer { min-height: 80px; }
 
+#navbar_main {
+    margin-bottom: 8px;
+}
 #site_logo {
-    margin: 15px;
-    border: 1px dotted black;
+    margin: 8px 16px 8px 0;
 }
 #site_title {
     font-weight: 500;
     font-size: 36px;
-    color: mediumblue;
+    color: rgb(236, 100, 0);
 }
-
-div#breadcrumb > ol {
+#breadcrumb > ol {
     margin-bottom: 0;
     background-color: #ffffff;
 }
-
+#header_hr {
+    margin-top: 0;
+}

File web/app/static/media/pdxcodeguild.png ADDED

File web/app/templates/hello.html MODIFIED

  • Sample update of the template's breadcrumb block.
  • Note the call to {{super()}} prior to adding the current page link.
 {% block title %}{{super()}} - Hello{% endblock %}
 
+{% block breadcrumb %}{{super()}}<li><a href="{{ url_for('main.main_page', page='hello') }}">Hello</a></li>{% endblock %}
+
 {% block content %}
 <h1>{{ page_title }}</h1>
 <p>template = /web/app/templates/hello.html</p>

File web/config.py MODIFIED

  • Add MySQL and SQLAlchemy configuration values.
     LOG_LEVEL = logging.ERROR  # CRITICAL,ERROR,WARNING,INFO,DEBUG,NOTSET
     BOOTSTRAP_USE_MINIFIED = True
     BOOTSTRAP_SERVE_LOCAL = True
+    MYSQL_USER = 'python'
+    MYSQL_PASSWORD = 'python-pass'
+    MYSQL_DB = 'flaskapp'
+    #MYSQL_HOST = '192.168.1.176'  # public ip
+    MYSQL_HOST = 'db'             # container network
+    SQLALCHEMY_DATABASE_URI = 'mysql+mysqlconnector://'+MYSQL_USER+':'+MYSQL_PASSWORD+'@'+MYSQL_HOST+'/'+MYSQL_DB
+    SQLALCHEMY_TRACK_MODIFICATIONS = False
+    SQLALCHEMY_POOL_TIMEOUT = 30
+    SQLALCHEMY_POOL_RECYCLE = 30
+    SQLALCHEMY_MAX_OVERFLOW = 2
 
...

 class DevelopmentConfig(AppConfig):
     DEBUG = True
     LOG_LEVEL = logging.DEBUG
+    SQLALCHEMY_RECORD_QUERIES = True

File web/flaskapp.py MODIFIED

-from flask import flash, render_template
+from flask import current_app, flash, render_template
 from markupsafe import Markup
+from app import db

...

+@app.route('/hello_db')
+def hello_db():
+    stmt = "SELECT VERSION()"        # Return a string that indicates the MySQL server version
+    #stmt = "SELECT CONNECTION_ID()"  # Return the connection ID (thread ID) for the connection
+    #stmt = "SELECT CURRENT_USER()"   # The authenticated user name and host name
+    #stmt = "SELECT DATABASE()"       # Return the default (current) database name
+    #stmt = "SHOW TABLES"             # Return list of non-temporary tables in current database
+
+    #stmt = "show create database %s;" % current_app.config['MYSQL_DB']
+    #stmt = "show grants for %s;" % current_app.config['MYSQL_USER']
+
+    #stmt = "select * from item"             # Return list of non-temporary tables in current database
+
+    #import pdb; pdb.set_trace()
+    eng = db.create_engine(current_app.config['SQLALCHEMY_DATABASE_URI'])
+    con = eng.connect()
+    rs = con.execute(db.text(stmt))
+    con.close()
+
+    cols = rs.keys()
+    rows = rs.fetchall()
+
+    result = '<b>'+stmt+'</b>'
+    result += '<br/>| '
+    for col in cols:
+        result += '<b>'+str(col)+'</b> | '
+    for row in rows:
+        result += '<br/>| '
+        for i,col in enumerate(row):
+            result += '%s | ' % col
+    return result

File web/requirements.txt MODIFIED

  • MySQL-Connector-Python is a databases driver, used by SQLAlchemy to communicate with MySQL servers.
  • Flask-SQLAlchemy consists of two components: the Core and ORM.
  • The Core provides a minimal SQL abstraction and allows direct access to the database through commands and queries.
  • The Object Relational Mapper (ORM) provides an object-oriented, high-level data abstraction.
 Flask==0.12.2
 Flask-Bootstrap==3.3.7.1
+Flask-SQLAlchemy==2.2.0
 gunicorn==19.7.1
 Jinja2==2.9.6
+MySQL-Connector-Python==8.0.6

Commit-v0.11 | Commit-v0.12 | Commit-v0.13

Clone this wiki locally