ORM

• ORM full spell object relation mapping

• Object - relational mapping

• It mainly realizes the mapping from model object to relational database data

  • For example, map each record in the database table to a model object

advantage

• You only need object - oriented programming, not database - oriented coding
  • All operations on the database are transformed into operations on class properties and methods
  • There is no need to write sql statements of various databases
• The decoupling of data model and database is realized, and the differences of different database operations are shielded
  • No longer pay attention to mysql, oracle... Etc
  • Through simple configuration, you can easily change the database without modifying the code

shortcoming

• Compared with directly using SQL statements to operate the database, there is a performance loss
• Converting objects into SQL statements according to their operations and objects according to the query results will cause performance loss in the mapping process

Flash Sqlalchemy installation and setup

• Sqlalmy is actually an abstraction of the database, which allows developers to operate the database through Python objects instead of directly dealing with SQL statements. While discarding some performance overhead, it can greatly improve the development efficiency
• SQLAlchemy is a relational database framework that provides high-level ORM and low-level native database operations. Flash SQLAlchemy is a flash extension that simplifies SQL alchemy operations.
• Document address: http://docs.jinkan.org/docs/flask-sqlalchemy

Environment construction

• Install flash Sqlalchemy

pip install flask-sqlalchemy

• If you are connecting to a mysql database, you need to install mysql dB

pip install flask-mysqldb

Database connection settings

• In flame Sqlalchemy, the database is specified by URL, and the database used by the program must be saved to Sqlalchemy of the flame configuration object_ DATABASE_ Uri key

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/test'

• Other settings:

# Dynamic tracking changes settings. If not set, only a warning will be prompted
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
#The original SQL statement is displayed when querying
app.config['SQLALCHEMY_ECHO'] = True

Connect to other databases

For the complete list of connection URI s, please jump to the document under SQLAlchemy( Supported Databases ) . Here are some common connection strings.

• Postgres:

postgresql://scott:tiger@localhost/mydatabase

• MySQL:

mysql://scott:tiger@localhost/mydatabase

• Oracle:

  - oracle://scott:tiger@127.0.0.1:1521/sidname

• SQLite (note the first four slashes):

sqlite:absolute/path/to/foo.db

Common SQLAlchemy field types

Type name	Types in python	explain
Integer	int	Ordinary integer, usually 32 bits
SmallInteger	int	An integer with a small value range, usually 16 bits
BigInteger	int or long	Integer with unlimited precision
Float	float	Floating point number
Numeric	decimal.Decimal	Ordinary integer, usually 32 bits
String	str	Variable length string
Text	str	Variable length string, optimized for long or unlimited length strings
Unicode	unicode	Variable length Unicode string
UnicodeText	unicode	Variable length Unicode string, optimized for long or unlimited length strings
Boolean	bool	Boolean value
Date	datetime.date	time
Time	datetime.datetime	Date and time
LargeBinary	str	Binary file

Common SQLAlchemy column options

Option name	explain
primary_key	If True, it represents the primary key of the table
unique	If True, it means that duplicate values are not allowed in this column
index	If True, create an index for this column to improve query efficiency
nullable	If True, null values are allowed; if False, null values are not allowed
default	Define default values for this column

Common SQLAlchemy relationship options

Option name	explain
backref	Add reverse references to the first mock exam in another model of the relationship.
primary join	Explicitly specify the join conditions used between the two models
uselist	If False, scalar values are used instead of lists
order_by	Specifies how records in the relationship are sorted
secondary	Specifies the name of the relationship table in a many to many relationship
secondary join	Specify a secondary join condition in a many to many relationship when you cannot decide for yourself in SQLAlchemy

Basic database operation

• In flask Sqlalchemy, insert, modify and delete operations are managed by the database session.
  • Session dB Session representation. Before you write the data to the database, you first add the data to the session and then call the commit() method to submit the session.
• In flask Sqlalchemy, the query operation is to manipulate data through the query object.
  • The most basic query is to return all the data in the table. You can query the database more accurately through the filter.

Define the model class in the view function

from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)

#Set the URL to connect to the database
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/test'

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
#The original SQL statement is displayed when querying
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)

class Role(db.Model):
    # Define table name
    __tablename__ = 'roles'
    # Define column objects
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    us = db.relationship('User', backref='role')

    #The repr() method displays a readable string
    def __repr__(self):
        return 'Role:%s'% self.name

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

    def __repr__(self):
        return 'User:%s'%self.name
      
if __name__ == '__main__':
    app.run(debug=True)

Common SQL alchemy query filters

filter	explain
filter()	Add the filter to the original query and return a new query
filter_by()	Add the equivalent filter to the original query and return a new query
limit	Use the specified value to limit the results returned by the original query
offset()	Offset the result returned by the original query and return a new query
order_by()	Sort the original query results according to the specified criteria and return a new query
group_by()	Group the original query results according to the specified criteria and return a new query

Common SQLAlchemy query executors

method	explain
all()	Returns all the results of the query as a list
first()	Returns the first result of the query. If it is not found, it returns None
first_or_404()	The first result of the query is returned. If it is not found, 404 is returned
get()	Returns the row corresponding to the specified primary key. If it does not exist, returns None
get_or_404()	Returns the row corresponding to the specified primary key. If it does not exist, it returns 404
count()	Returns the number of query results
paginate()	Returns a Paginate object that contains the results within the specified range

Create table

db.create_all()

Delete table

db.drop_all()

Insert a piece of data

ro1 = Role(name='admin')
db.session.add(ro1)
db.session.commit()
#Insert a piece of data again
ro2 = Role(name='user')
db.session.add(ro2)
db.session.commit()

Insert multiple pieces of data at a time

us1 = User(name='wang', password='123456', role_id=ro1.id)
    us2 = User(name='zhang',  password='201512', role_id=ro2.id)
    us3 = User(name='chen',  password='987654', role_id=ro2.id)
    us4 = User(name='zhou',  password='456789', role_id=ro1.id)
    us5 = User(name='tang', password='158104', role_id=ro2.id)
    us6 = User(name='wu',    password='5623514', role_id=ro2.id)
    us7 = User(name='qian',  password='1543567', role_id=ro1.id)
    us8 = User(name='liu',  password='867322', role_id=ro1.id)
    us9 = User(name='li',  password='4526342', role_id=ro2.id)
    us10 = User(name='sun', password='235523', role_id=ro2.id)
    db.session.add_all([us1, us2, us3, us4, us5, us6, us7, us8, us9, us10])
    db.session.commit()

Query: filter_by exact query

Returns everyone whose name equals wang

User.query.filter_by(name='wang').all()

first() returns the first object queried

User.query.first()

filter fuzzy query, which returns all data with g at the end of the name.

User.query.filter(User.name.endswith('g')).all()

get(): the parameter is the primary key. If the primary key does not exist, no content is returned

User.query.get()
User.query.get('1')

Logical non, return all data whose name is not equal to wang

User.query.filter(User.name!='wang').all()

not_ Equivalent to negation

from sqlalchemy import not_
User.query.filter(not_(User.name=='chen')).all()

For logical and, you need to import and and return all data satisfying the and() condition

from sqlalchemy import and_
User.query.filter(and_(User.name!='wang',User.email.endswith('163.com'))).all()

Logical or, or needs to be imported_

from sqlalchemy import or_
User.query.filter(or_(User.name!='wang',User.email.endswith('163.com'))).all()

Delete after querying data

user = User.query.first()
db.session.delete(user)
db.session.commit()
User.query.all()

Update data

user = User.query.first()
user.name = 'dong'
db.session.commit()
User.query.first()

Association query example

• Query all users in role

#Query roles with table id 1
ro1 = Role.query.get(1)
#Query all users in this role
ro1.us.all()

• Query user's role

#Query the users whose table id is 3
us1 = User.query.get(3)
#What role does the query user belong to
us1.role

Comprehensive case

Database migration

• During the development process, the database model needs to be modified, and the database needs to be updated after modification. The most direct way is to delete the old table, but this will lose data.
• A better solution is to use the database migration framework, which can track changes in the database schema and then apply the changes to the database.
• In flash, you can use the flash migrate extension to realize data migration. And integrated into flash script, all operations can be completed through commands.
• To export database migration commands, flash migrate provides a MigrateCommand class that can be attached to the manager object of flash script.

First, install flash migrate in the virtual environment.

pip install flask-migrate

The following example: define a model class.

# _*_ coding: utf-8 _*_
# @Author : zzg
# motto: teach without class, accompany and care

from  flask import Flask
from flask_script import Manager
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate,MigrateCommand
from datetime import datetime


app = Flask(__name__)

manager = Manager(app)
#Set connection data
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@127.0.0.1:3306/test2'

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)


#The first parameter is the instance of Flask, and the second parameter is the Sqlalchemy database instance
Migrate(app,db)

#manager is an instance of flash script. This statement adds a db command to flash script
manager.add_command('db',MigrateCommand)


class Infos(db.Model):
    # __table_args__ = {'extend_existing': True}
    __tablename__ = 'db_infos'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50), nullable=True)
    codecoll = db.Column(db.String(10),nullable=True)
    echnology = db.Column(db.String(200),nullable=True)
    direction = db.Column(db.String(200),nullable=True)
    price = db.Column(db.String(50),nullable=True)
    create_time = db.Column(db.DateTime,default=datetime.now)

    def __repr__(self):
        return "<Role %s>" % self.name

@app.route('/')
def index():
    return 'adad'

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

Create migration warehouse

#This command will create the migrations folder, where all migrated files will be placed.
python manage.py db init

Generate migration script

python manage.py db migrate

Update database

python manage.py db upgrade

Add test data

insert into `db_infos` values (null ,'Wang Fafen','2','python','network security','9K',now());
insert into `db_infos` values (null ,'Li Qianxiang','3','python','Reptile development','15K',now());
insert into `db_infos` values (null ,'De Shuai Zhang','3','python','web development','12K',now());
insert into `db_infos` values (null ,'army officer's hat ornaments','3','python','web development','12K',now());
insert into `db_infos` values (null ,'Lv Bu','3','python','web development','12K',now());
insert into `db_infos` values (null ,'Wang Zhaojun','3','python','web development','12K',now());
insert into `db_infos` values (null ,'Luban','3','python','web development', '12K',now());
insert into `db_infos` values (null ,'Monkey Sun','3','python','web development','12K',now());
insert into `db_infos` values (null ,'favorite concubine of emperor Minghuang','3','python','web development', '12K',now());

Execute database import script

mysql -uroot -p -D test2 < data.sql  

Front end page writing

• Open the WEB file provided by the courseware

<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <title>CSDN Project practice course</title>
    <link rel="stylesheet" href="../static/jq-1.css">
      <link rel="styleSheet" href="../static/index.css" >
      <style>
          .box{
              float: right;
          }
          #boxs{
            width: 100%;
          }
          #box1{
              margin-left: 36%;
          }
      </style>
  </head>
  <body>

    <nav class="navbar navbar-inverse navbar-fixed-top">
      <div class="container-fluid">
        <div class="navbar-header">
          <a class="navbar-brand" href="#"> my project practice course</a>
        </div>
        <div id="navbar" class="navbar-collapse collapse">
          <ul class="nav navbar-nav navbar-right">
           <li><a href="javascript:void(0);" target="_blank">Login</a></li>
            <li><a href="#"> CSDN College</a></li>
            <li><a href="#">help</a></li>
          </ul>
        </div>
      </div>
    </nav>


    <div class="container-fluid">
      <div class="row">
        <div class="col-sm-3 col-md-2 sidebar">
          <ul class="nav nav-sidebar">
            <li class="active"><a href="#" style="color: #0f0f0f; font-size: 18px; Background color: grey "> menu bar</a></li>
            <li><a href="#"> Data Center</a></li>
            <li><a href="#"> your little secret</a></li>
            <li><a href="#"> waiting for you to develop</a></li>
          </ul>

        </div>

        <div class="col-sm-9 col-sm-offset-3 col-md-10 col-md-offset-2 main">

            <form class="navbar-form" role="search">
             <div class="form-group">
                <input type="text" class="form-control" placeholder="Search" name="key">
             </div>
            <button type="submit" class="btn btn-info">search</button>
        </form>
            <div class="box">
              <button type="submit" class="btn btn-success">add to</button>
            </div>

          <h3 class="sub-header">I am the background management page</h3>
          <div class="table-responsive">
            <table class="table table-striped">
              <thead>
                <tr>
                  <th>number</th>
                  <th>Code</th>
                  <th>Code age</th>
                  <th>Familiar with language</th>
                  <th>main direction of attack</th>
                    <th>salary</th>
                  <th>operation</th>
                </tr>
              </thead>
              <tbody class="datas">
                 <tr>
                  <td>1</td>
                  <td>Lao Wang</td>
                  <td>2 year</td>
                  <td>python/Java</td>
                  <td>web development</td>
                  <td>25K</td>
                <td><button type="button" class="btn btn-danger btn-sm">delete</button>
                    <button type="button" class="btn btn-warning btn-sm">edit</button></td>
                </tr>

               <tr>
                  <td>2</td>
                  <td>Lao Li</td>
                  <td>3 year</td>
                  <td>python/Java</td>
                  <td>Reptile</td>
                  <td>30K</td>
                <td><button type="button" class="btn btn-danger btn-sm">delete</button>
                    <button type="button" class="btn btn-warning btn-sm">edit</button></td>
                </tr>


              </tbody>
            </table>

          </div>


            <div id="boxs">

     <ul class="pagination" id="box1">
    <li><a href="#">&laquo;</a></li>
    <li class="active"><a href="#">1</a></li>
    <li class="disabled"><a href="#">2</a></li>
    <li><a href="#">3</a></li>
    <li><a href="#">4</a></li>
    <li><a href="#">5</a></li>
    <li><a href="#">&raquo;</a></li>
</ul>
            </div>

        </div>
      </div>
    </div>

    <!-- Bootstrap core JavaScript
    ================================================== -->
    <!-- Placed at the end of the document so the pages load faster -->
   <script src="https://cdn.bootcss.com/jquery/1.12.4/jquery.min.js"></script>
    <script src="https://cdn.bootcss.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>

    <script>
        var $lis = $('.nav-sidebar li');
        $lis.click(function () {
            $(this).addClass('active').siblings('li').removeClass('active');
        });

    </script>


  </body>
</html>

Data display

class Admins(MethodView):

    def get(self):
        page = request.args.get('page', default=1, type=int)
        key = request.args.get('key',None)
        data = Infos.query.filter(Infos.name.like('%'+key+'%')) if key else Infos.query
        pagination = data.paginate(
            page=page,
            per_page=2,
        )

        persons = pagination.items
        return render_template('admins.html',pagination=pagination,person=persons)


app.add_url_rule('/',view_func=Admins.as_view(name='mains'))

Data addition

Front end template page

{% extends 'admins.html' %}

{% block content %}


<form class="form-horizontal" >
  <div class="form-group">
    <label for="inputEmail3" class="col-sm-2 control-label">Code</label>
       <div class="col-sm-8">
    <input type="text" class="form-control" id="inputEmail3" placeholder="Please enter code">
  </div>
  </div>

  <div class="form-group">
    <label for="inputEmail4" class="col-sm-2 control-label">Code age</label>
       <div class="col-sm-8">
    <input type="text" class="form-control" id="inputEmail4" placeholder="Please enter code">
  </div>
  </div>

  <div class="form-group">
    <label for="inputEmail5" class="col-sm-2 control-label">direction</label>
       <div class="col-sm-8">
    <input type="text" class="form-control" id="inputEmail5" placeholder="Please enter code">
  </div>
  </div>

      <div class="form-group">
    <label for="inputEmail6" class="col-sm-2 control-label">language</label>
       <div class="col-sm-8">
    <input type="text" class="form-control" id="inputEmail6" placeholder="Please enter code">
  </div>
  </div>

      <div class="form-group">
    <label for="inputEmail7" class="col-sm-2 control-label">salary</label>
       <div class="col-sm-8">
    <input type="text" class="form-control" id="inputEmail7" placeholder="Please enter code">
  </div>
  </div>

     <div class="form-group">
    <div class="col-sm-offset-2 col-sm-10">
      <button type="submit" class="btn btn-success" >Submit</button>
    </div>
  </div>


</form>


{% endblock %}

Background business code

@app.route('/add',methods=['GET','POST'])
def add_data():
    if request.method.lower() =='post':
        form = DataForm(request.form)
        if form.validate():
            info = Infos()
            info.name = form.dh.data
            info.codecoll = form.ml.data
            info.echnology = form.yy.data
            info.direction = form.fx.data
            info.price =  request.form.get('xz')
            db.session.add(info)
            db.session.commit()
            flash('Data added successfully')
            return redirect('/')
        else:
            return jsonify({'message':form.errors})

    return render_template('add_edit.html')

Data authentication – using flash forms

from flask_wtf import FlaskForm
from wtforms import StringField,IntegerField
from wtforms.validators import Regexp,ValidationError,EqualTo,InputRequired,Length,Required,DataRequired


class DataForm(FlaskForm):
    dh = StringField(validators=[DataRequired(message='Please fill in the code'),Length(1, 50,message='Must be at 6～50 Between characters')])
    ml = StringField(validators=[DataRequired(message='Please fill in Maling'),Length(1, 3,message='Must be within 1～3 Between characters')])
    yy = StringField(validators=[DataRequired(message='Please fill in the language'),Length(1, 50,message='Must be within 1～50 Between characters')])
    fx = StringField(validators=[DataRequired(message='Please fill in the direction'),Length(1, 50,message='Must be within 1～50 Between characters')])
    xz = StringField(validators=[DataRequired(message='Please fill in salary'),Length(1, 50,message='Must be within 1～50 Between characters')])

Note: Flash needs authentication for data submission, and sets the token to return to the front-end page

from flask_wtf import CSRFProtect
app.config["SECRET_KEY"] = '79537d00f4834892986f09a100aa1edf'
CSRFProtect(app)

...
 <input type="hidden" name="csrf_token" value="{{ csrf_token() }}" />

Front end business processing

• Front end routing

<script>
       $('.box button').click(function () {
           window.location.href = '/add'
        });
    </script>

• Encapsulating the front end with macros

{% extends 'admins.html' %}

{% block content %}

    <!-- Ding Yihong -->
{% macro input(for="",label="", type="text", name="",id="", value="",pl="") %}
     <div class="form-group">
<label for="{{ for }}" class="col-sm-2 control-label">{{ label }}</label>
      <div class="col-sm-8">
    <input type="{{ type }}" name="{{ name }}"  class="form-control" id="{{ id }}" value="{{ value }}" placeholder="{{ pl }}">
      </div>
  </div>
{% endmacro %}

   <!-- Call macro -->
<form class="form-horizontal" style="margin: 80px auto" method="post">
     <input type="hidden" name="csrf_token" value="{{ csrf_token() }}" />

 {{ input("inputEmail3","Code", name="dh",id='inputEmail3',pl='Please enter code') }}
 {{ input("inputEmail4","Code age", name="ml",id='inputEmail4',pl='Please enter the collar code') }}
 {{ input("inputEmail5","language", name="yy",id='inputEmail5',pl='Please enter language') }}
 {{ input("inputEmail6","direction", name="fx",id='inputEmail6',pl='Please enter the direction') }}
 {{ input("inputEmail7","salary", name="xz",id='inputEmail7',pl='Please enter salary') }}

    <div class="form-group">
    <div class="col-sm-offset-2 col-sm-10">
      <button type="submit" class="btn btn-success" >add to</button>
    </div>
  </div>

</form>

{% endblock %}

Data modification

View processing

@app.route('/edit/<id>',methods=['GET','POST'])
def edit_data(id):
    data = Infos.query.filter_by(id=id).first()
    if request.method.lower() == 'post':
        if data:
            form = DataForm(request.form)
            if form.validate():
                data.name = form.dh.data
                data.codecoll = form.ml.data
                data.echnology = form.yy.data
                data.direction = form.fx.data
                data.price = request.form.get('xz')
                db.session.add(data)
                db.session.commit()
                flash('Data modification succeeded')
                return redirect('/')
            else:
                return jsonify({'message': form.errors})
        else:
            abort(Response('Brother, don't bother'))
        
    else:
        return render_template('add_edit.html',data=data)

Data authentication

Note: if the data are the same, the same form can be used for authentication

Front end processing

• Front end routing

  <script>
  

          $('#edit').click(function () {
  

              console.log($(this).data('id'));
  

                  window.location.href = '/edit/' + $(this).data('id')
  

          })
  

      </script>
  

  Note: the ID selector is unique. You need to use the class selector to get the ID, otherwise you can only get the first row

• Front end business code

<form class="form-horizontal" style="margin: 80px auto" method="post">
     <input type="hidden" name="csrf_token" value="{{ csrf_token() }}" />
    {% if data %}
 {{ input("inputEmail3","Code", name="dh",id='inputEmail3',value=data.name,pl='Please enter code') }}
 {{ input("inputEmail4","Code age", name="ml",id='inputEmail4',value=data.codecoll,pl='Please enter the collar code') }}
 {{ input("inputEmail5","language", name="yy",id='inputEmail5',value=data.echnology,pl='Please enter language') }}
 {{ input("inputEmail6","direction", name="fx",id='inputEmail6',value= data.direction ,pl='Please enter the direction') }}
 {{ input("inputEmail7","salary", name="xz",id='inputEmail7',value=data.price ,pl='Please enter salary') }}
    {% else %}
 {{ input("inputEmail3","Code", name="dh",id='inputEmail3',pl='Please enter code') }}
 {{ input("inputEmail4","Code age", name="ml",id='inputEmail4',pl='Please enter the collar code') }}
 {{ input("inputEmail5","language", name="yy",id='inputEmail5',pl='Please enter language') }}
 {{ input("inputEmail6","direction", name="fx",id='inputEmail6',pl='Please enter the direction') }}
 {{ input("inputEmail7","salary", name="xz",id='inputEmail7',pl='Please enter salary') }}
    {% endif %}

    <div class="form-group">
    <div class="col-sm-offset-2 col-sm-10">
      <button type="submit" class="btn btn-success" >{% if not data %}add to{% else %}modify{% endif %}</button>
    </div>
  </div>

</form>

Data deletion

Background view

@app.route('/del/<id>',methods=['GET','POST'])
def del_data(id):
    data = Infos.query.filter_by(id=id).first()
    if not data:
        flash("There is a problem with the parameters")
    try:
        db.session.delete(data)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
    return redirect('/')

Front end routing

$('.del').click(function () {
        window.location.href = '/del/' + $(this).data('id')
})