Hardware and software Environment

  • windows 10 64bit
  • Anaconda3 with python 3.7
  • Pycharm 2020.1.2
  • Flask 1.1.2
  • sqlite3

The database operation Flask_SQLalchemy

preface

In Web development, database operation is indispensable. SQLAlchemy is an ORM that allows python to use SQLAlchemy to manipulate a mysql database. Flask-sqlalchemy is a Flask extension that brings SQLAlchemy support to Flask applications

The installation

Install using the old method

pip install flask_sqlalchemy
Copy the code

Install sqlite

An example of SQLAlchemy manipulating mysql has been shown before, this time using sqLite database. Sqlite is a lightweight database, which can be easily used on embedded devices or mobile devices. It supports mainstream operating systems such as Win, Linux, macOS, Android, etc. The current version has been developed to SQlitE3

Came to the official site www.sqlite.org/download.ht…

Decompress the downloaded two packages respectively, and then put all the documents inside in a folder for easy use, and then add the path of this folder to the system environment variables

Then, we can use SQLite.exe directly from any location by opening the terminal

If you are not familiar with SQL commands, you can use the graphical client. Here is a recommended tool, which is an open source project and can be downloaded at the following address

sqlitebrowser.org/dl/

Code sample

Flask-wtf is an example of a user’s registration. When a user submits his or her registration information, Flask queries the database and writes it to SQLite if the user name does not exist. Otherwise, an error message is displayed indicating that the user name is invalid

from flask import Flask, render_template, request
from flask_wtf import FlaskForm
from wtforms import SubmitField, StringField, PasswordField
from wtforms.validators import DataRequired, EqualTo, Length, Email
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.secret_key = "xxx"
Set the database URI
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///member.sqlite3'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True


Instantiate an object of class SQLAlchemy using app as a parameter
db = SQLAlchemy(app)

# Create membership model
class Member(db.Model) :
    id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(45), unique=True)
    email = db.Column(db.String(45))
    password = db.Column(db.String(128))

    def __init__(self, username, email, password) :
        self.username = username
        self.email = email
        self.password = password

Create table
db.create_all()

Use WTF to implement forms, customize a form class
class RegisterForm(FlaskForm) :
    username = StringField(label='Username:', validators=[DataRequired()])
    email = StringField(label='Email:', validators=[DataRequired(), Email(message='Email format error')])
    password = PasswordField(label='Password:', validators=[DataRequired(), Length(6.16, message='Wrong password format')])
    password2 = PasswordField(label='Confirm password:', validators=[DataRequired(), Length(6.16, message='Wrong password format'), EqualTo('password', message='Inconsistent passwords')])
    submit = SubmitField(label='registered')


@app.route('/', methods=['GEt'.'POST'])
def register() :
    register_form = RegisterForm()

    if request.method == 'POST':
        if register_form.validate_on_submit():
            username = request.form.get('username')
            email = request.form.get('email')
            password = request.form.get('password')

            Check whether the same user name already exists in the database
            if Member.query.filter_by(username=username).all() :return 'Invalid username'

            Build database records and write to the database
            member = Member(username=username, email=email, password=password)
            db.session.add(member)
            db.session.commit()
            return 'Register success.'

        else:
            return 'Invalid'

    Pass the instantiated register_form to register.html
    return render_template('register.html', form=register_form)


if __name__ == '__main__':
    app.run(host="127.0.0.1",port=5000,debug=True)
Copy the code

The template file is the same as the original, with only one form and no changes

<! DOCTYPEhtml>
<html>
<head>
    <meta charset="UTF-8">
    <title>Register</title>
</head>
<body>

    <form method="post">
        {{ form.csrf_token() }}
        {{ form.username.label }} {{ form.username }} <br>
        {{ form.email.label }} {{ form.email  }} <br>
        {{ form.password.label }} {{ form.password }} <br>
        {{ form.password2.label }} {{ form.password2 }} <br>
        {{ form.submit }}
    </form>

</body>
</html>
Copy the code

After the program is running, visit http://127.0.0.1:5000 and enter the registration information that meets the criteria

The database file member.sqlite3 is generated in the project root directory

Sqlite3 can see the user information registered successfully just now. Using the tool, you can easily add, delete, change and check the data

If the user name is already registered, an error message will be displayed and the information will not be written to the database

note

If there is a warning

UserWarning SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead
Copy the code

You can cancel the warning above by setting app.config[‘SQLALCHEMY_TRACK_MODIFICATIONS’] = True