Author: hans

1. create_db.py

import sqlalchemy
from urllib import parse
engine = sqlalchemy.create_engine('mysql://zhuby:%s@192.168.0.43' % parse.unquote_plus('somIUpass#98'))
engine.execute("CREATE DATABASE TESTDB") #create db
engine.execute("USE TESTDB") # select new db

2. create_db2.py

import sqlalchemy
from urllib import parse
from sqlalchemy_utils import database_exists, create_database

engine = sqlalchemy.create_engine('mysql://zhuby:%s@192.168.0.43/EmployeeDB3' % parse.unquote_plus('somIUpass#98'))

if not database_exists(engine.url):
    create_database(engine.url)

print(database_exists(engine.url))

3. create_db3.py

from sqlalchemy import create_engine
user = 'zhuby'
password = 'somIUpass#98'
host = '192.168.0.43'
port = '3306'
db = 'NEWDB2'
# This engine just used to query for list of databases
mysql_engine = create_engine('mysql://{0}:{1}@{2}:{3}'.format(user, password, host, port))
# Query for existing databases
mysql_engine.execute("CREATE DATABASE IF NOT EXISTS {0} ".format(db))
# Go ahead and use this engine
db_engine = create_engine('mysql://{0}:{1}@{2}:{3}/{4}'.format(user, password, host, port, db))

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from urllib import parse
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://zhuby:%s@192.168.0.43/EmployeeDB' % parse.unquote_plus('somIUpass#98')

db = SQLAlchemy(app)
migrate = Migrate(app, db)

manager = Manager(app)
manager.add_command('db', MigrateCommand)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128))

if __name__ == '__main__':
    manager.run()

Assuming the above script is stored in a file named manage.py, all the database migration commands can be accessed by running the script:

$ python manage.py db init
$ python manage.py db migrate
$ python manage.py db upgrade
$ python manage.py db --help

When DB user password contains special characters, we can make the connection string as:
from urllib import parse
from sqlalchemy.engine import create_engine
engine = create_engine(‘postgres://user:%s@host/database’ % parse.unquote_plus(‘badpass’))
Here is an example hello.py:

import os
from flask import Flask, render_template, session, redirect, url_for, flash
from flask_bootstrap import Bootstrap
from flask_moment import Moment
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired
from flask_sqlalchemy import SQLAlchemy
from urllib import parse
basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://zhuby:%s@192.168.0.43/EmployeeDB' % parse.unquote_plus('somIUpass#98')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False


bootstrap = Bootstrap(app)
moment = Moment(app)
db = SQLAlchemy(app)


class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    users = db.relationship('User', backref='role')

    def __repr__(self):
        return '<Role %r>' % self.name


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, index=True)
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))

    def __repr__(self):
        return '<User %r>' % self.username


class NameForm(FlaskForm):
    name = StringField('What is your name?', validators=[DataRequired()])
    submit = SubmitField('Submit')


@app.errorhandler(404)
def page_not_found(e):
    return render_template('404.html'), 404


@app.errorhandler(500)
def internal_server_error(e):
    return render_template('500.html'), 500


@app.route('/', methods=['GET', 'POST'])
def index():
    form = NameForm()
    if form.validate_on_submit():
        old_name = session.get('name')
        if old_name is not None and old_name != form.name.data:
            flash('Looks like you have changed your name!')
        session['name'] = form.name.data
        return redirect(url_for('index'))
    return render_template('index.html', form=form, name=session.get('name'))

verify the code with python commands:
(venv) C:\Users\zhuby\flasky>python
Python 3.8.3 (tags/v3.8.3:6f8c832, May 13 2020, 22:37:02) [MSC v.1924 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from hello import db
>>> db.create_all()
C:\Users\zhuby\python\flask_db_api\venv\lib\site-packages\pymysql\cursors.py:170: Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")
  result = self._query(query)
>>> from hello import Role, User
>>> admin_role = Role(name='Admin')
>>> mod_role = Role(name='Moderator')
>>> user_role = Role(name='User')
>>> user_john = User(username='john', role=admin_role)
>>> user_susan = User(username='susan', role=user_role)
>>> db.session.add(admin_role)
>>> db.session.add(mod_role)
>>> db.session.add(user_role)
>>> db.session.add(user_john)
>>> db.session.add(user_susan)
>>> db.session.commit()
>>> print(admin_role.id)
1
>>> print(user_role.id)
3
>>> Role.query.all()
[<Role 'Admin'>, <Role 'Moderator'>, <Role 'User'>]
>>> User.query.all()
[<User 'john'>, <User 'susan'>]
>>> User.query.filter_by(role=user_role).all()
[<User 'susan'>]

1. SETTING UP ENVIRONMENT on Ubuntu20:
   (base) ubuntu@ubunu2004:~$ mkdir flaskdbexample
   (base) ubuntu@ubunu2004:~$ cd flaskdbexample
   (base) ubuntu@ubunu2004:~/flaskdbexample$ sudo apt install python3-virtualenv
   (base) ubuntu@ubunu2004:~/flaskdbexample$ virtualenv venv
   created virtual environment CPython3.8.2.final.0-64 in 805ms

(base) ubuntu@ubunu2004:~/flaskdbexample/venv/bin$ source ./activate
(venv) (base) ubuntu@ubunu2004:~$ cd flaskdbexample/
(venv) (base) ubuntu@ubunu2004:~/flaskdbexample$ pip install flask flask-sqlalchemy
(venv) (base) ubuntu@ubunu2004:~/flaskdbexample$ pip install pymysql
(venv) (base) ubuntu@ubunu2004:~/flaskdbexample$ pip install marshmallow_sqlalchemy

2. Complete Code
   (venv) (base) ubuntu@ubunu2004:~/flaskdbexample$ vi app.py

from flask import Flask, request, jsonify, make_response
from flask_sqlalchemy import SQLAlchemy
from marshmallow_sqlalchemy import ModelSchema
from marshmallow import fields
app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://monty:password@192.168.0.43/EmployeeDB'
db = SQLAlchemy(app)

###Models####
class Product(db.Model):
    __tablename__ = "products"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(20))
    productDescription = db.Column(db.String(100))
    productBrand = db.Column(db.String(20))
    price = db.Column(db.Integer)

    def create(self):
      db.session.add(self)
      db.session.commit()
      return self
    def __init__(self,title,productDescription,productBrand,price):
        self.title = title
        self.productDescription = productDescription
        self.productBrand = productBrand
        self.price = price
    def __repr__(self):
        return '' % self.id
db.create_all()
class ProductSchema(ModelSchema):
    class Meta(ModelSchema.Meta):
        model = Product
        sqla_session = db.session
    id = fields.Number(dump_only=True)
    title = fields.String(required=True)
    productDescription = fields.String(required=True)
    productBrand = fields.String(required=True)
    price = fields.Number(required=True)

@app.route('/products', methods = ['GET'])
def index():
    get_products = Product.query.all()
    product_schema = ProductSchema(many=True)
    products = product_schema.dump(get_products)
    return make_response(jsonify({"product": products}))
@app.route('/products/<id>', methods = ['GET'])
def get_product_by_id(id):
    get_product = Product.query.get(id)
    product_schema = ProductSchema()
    product = product_schema.dump(get_product)
    return make_response(jsonify({"product": product}))
@app.route('/products/<id>', methods = ['PUT'])
def update_product_by_id(id):
    data = request.get_json()
    get_product = Product.query.get(id)
    if data.get('title'):
        get_product.title = data['title']
    if data.get('productDescription'):
        get_product.productDescription = data['productDescription']
    if data.get('productBrand'):
        get_product.productBrand = data['productBrand']
    if data.get('price'):
        get_product.price= data['price']    
    db.session.add(get_product)
    db.session.commit()
    product_schema = ProductSchema(only=['id', 'title', 'productDescription','productBrand','price'])
    product = product_schema.dump(get_product)
    return make_response(jsonify({"product": product}))
@app.route('/products/<id>', methods = ['DELETE'])
def delete_product_by_id(id):
    get_product = Product.query.get(id)
    db.session.delete(get_product)
    db.session.commit()
    return make_response("",204)
@app.route('/products', methods = ['POST'])
def create_product():
    data = request.get_json()
    product_schema = ProductSchema()
    product = product_schema.load(data)
    result = product_schema.dump(product.create())
    return make_response(jsonify({"product": result}),200)
if __name__ == "__main__":
    app.run(debug=True)

3. testing the API
(venv) (base) ubuntu@ubunu2004:~/flaskdbexample$ flask run –host=0.0.0.0
A. POST data to http://ubunu2004:5000/products
{
“title” : “Python coding”,
“productDescription” : “ebook”,
“productBrand” : “SUN”,
“price” : “5000”
}

B. GET data

1. install flask and flask-mysqldb
pip install flask
pip install flask_mysqldb
2. mkdir flask_mysql and subfolder templates
we only need two files:
app.py and templates/index.html
3. make app works without DB:
index.html

<HTML>
<BODY bgcolor="cyan">
<form method="POST" action="">
    <center>
    <H1>Enter your details </H1> <br>
    First Name <input type = "text" name= "fname" /> <br>
    Last Name <input type = "text" name = "lname" /> <br>
    <input type = "submit">
    </center>
</form>
</BODY>
</HTML>

app.py

from flask import Flask, render_template
app = Flask(__name__)
@app.route('/', methods=['GET', 'POST'])
def index():
    return render_template('index.html')
if __name__ == '__main__':
    app.run()

4. test app.py, you will get the web page as below:

5. then we can create table in MySQL:
CREATE TABLE MyUsers ( firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL);
update app.py:

from flask import Flask, render_template, request
from flask_mysqldb import MySQL
app = Flask(__name__)
app.config['MYSQL_HOST'] = '192.168.0.43'
app.config['MYSQL_USER'] = 'monty'
app.config['MYSQL_PASSWORD'] = 'somIUpass#98'
app.config['MYSQL_DB'] = 'EmployeeDB'
mysql = MySQL(app)
@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == "POST":
        details = request.form
        firstName = details['fname']
        lastName = details['lname']
        cur = mysql.connection.cursor()
        cur.execute("INSERT INTO MyUsers(firstName, lastName) VALUES (%s, %s)", (firstName, lastName))
        mysql.connection.commit()
        cur.close()
        return 'success'
    return render_template('index.html')
if __name__ == '__main__':
    app.run()

6. run it again, you can submit info into DB on http://127.0.0.1:5000/

1. python pdb debug
   we have sample code read_redis.py:
   import json
   import redis
   client = redis.Redis()
   data = client.lpop(‘info’)
   run it with command: python -i read_redis.py.
   once you got "exception occurred", start pdb debuggin as below:

>>> import pdb
>>> pdb.pm()
> c:\python\lib\site-packages\redis\connection.py(563)connect()
-> raise ConnectionError(self._error_message(e))
(Pdb) data
*** NameError: name 'data' is not defined

2. VS CODE debug
   you can press F5 to start debugging python in VS CODE as below: