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'>]