学习Flask之Flask-SQLAlchemy 数据库操作

安装

pip install flask-sqlalchemy pymysql

安装 flask-sqlalchemy，pymysql，都是用于操作数据库的

调用

from flask_sqlalchemy import SQLAlchemy
.......
db = SQLAlchemy()

初始化

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from epay.extendsions import db
from epay.config import mysql_config
import pymysql

def register_extensions(app):
    # 初始化 db
    app.config['SQLALCHEMY_DATABASE_URI'] = mysql_config['DIALECT']+'://'+mysql_config['USERNAME']+':'+mysql_config['PASSWORD']+'@'+mysql_config['HOST']+':'+mysql_config['PORT']+'/'+mysql_config['DATABASE']+'?charset=utf8'
    app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
    # 初始化mysql
    pymysql.install_as_MySQLdb()

    db.init_app(app)

config 配置文件是这样的

import os
# mysql 连接参数
mysql_config = {
    'DIALECT'   :'mysql',
    'DRIVER'    :'pymysql',
    'USERNAME'  : 'root',
    'PASSWORD'  : 'root',
    'HOST'      : '127.0.0.1',
    'PORT'      : '3306',
    'DATABASE'  : 'mpaynow_for_py'
}

# --------------------分页-------------------------
POST_PER_PAGE = 5 # 每页的数量


# -------------------上传路径---------------
basepath = os.path.dirname(__file__)  
upload_path = os.path.join(basepath, 'static/uploads')
UPLOAD_PATH = os.path.abspath(upload_path)

接下来就是创建数据表的映射
models.py

from sqlalchemy.dialects.mysql import DOUBLE
from datetime import datetime
from flask_login import UserMixin
from epay import db

class User(db.Model,UserMixin):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)
    created_at = db.Column(db.Date, nullable=True)
    login_date = db.Column(db.Date, nullable=True)
    

class Order(db.Model):
    __tablename__ = 'order'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    order_no = db.Column(db.String(100), nullable=False)
    amount = db.Column(DOUBLE(10,2), nullable=False)
    email = db.Column(db.String(100), nullable=False)
    phone = db.Column(db.String(100), nullable=False)
    remark = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, nullable=False)
    status = db.Column(db.Enum('pending','paid','complete'),server_default='pending',nullable=False)
    created_at = db.Column(db.Date, nullable=True)



class Merchant(db.Model):
    __tablename__ = 'merchant'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    merchant = db.Column(db.String(100), nullable=False)
    logo = db.Column(db.Text, nullable=False)
    tel = db.Column(db.String(100), nullable=False)
    address = db.Column(db.String(100), nullable=False)
    created_at = db.Column(db.Date, nullable=True)

form.py 和 Model.py，还是有区别的，虽然有相同的字段，但是form 是对应是用户可以通过表单提交的字段，而model.py 里面的，则是数据表中的所有字段，两者有本质上的区别

接下来就是通过 model.py 的类和db 做数据库的操作
order.py

from os import rename
from flask import Blueprint,current_app,render_template,url_for,redirect,request
import flask
from flask.helpers import flash

from flask_login import login_required, current_user
from flask_login.utils import login_user
from flask_wtf import form
from epay.extendsions import db
from epay.models import User
from epay.models import Order
from epay.config import POST_PER_PAGE
import time
import random

from epay.forms import OrderForm

order_bp = Blueprint('order',__name__)

@order_bp.route('/')
@order_bp.route('/index')
@login_required
def index():
    page = request.args.get('page', 1, type=int)
    pagination = Order.query.order_by(Order.created_at.desc()).paginate(page, per_page=POST_PER_PAGE)
    posts = pagination.items
    print(pagination)
    print(posts)
    return render_template('admin/order/index.html',pagination=pagination, posts=posts)


@order_bp.route('/create')
@login_required
def create():
    form = OrderForm()
    return render_template('admin/order/create.html',form=form)


@order_bp.route('/save',methods=['POST'])
@login_required
def save():

    form = OrderForm()
    if form.validate_on_submit():
        print(form.email.data)
        print(form.phone.data)
        print(form.amount.data)
        print(form.remark.data)

        if request.form.get('order_no') is None:
            email = request.form.get('email')
            phone = request.form.get('phone')
            amount = request.form.get('amount')
            remark = request.form.get('remark')

            randnum = [random.randint(0,9) for _ in range(4)]
            randnum = ''.join(str(i) for i in randnum)
            # print(randnum)
            order_no =  'EP'+time.strftime("%Y%m%d%H%M%S", time.localtime())+randnum
            # print(order_no)
            user_id = current_user.id
            # print(user_id)
            status = 'pending'
            created_at = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            # print(created_at)
            order = Order(email=email,phone=phone,amount=amount,remark=remark,order_no=order_no,user_id=user_id,status=status,created_at=created_at)
            db.session.add(order)
            db.session.commit()
            flash('Create Success')
            return redirect(url_for('admin.order.index'))
        else:
            order_no = request.form.get('order_no')
            remark = request.form.get('remark')

            order = Order.query.filter(Order.order_no == order_no).first()
            order.remark = remark
            db.session.commit()
            flash('Update Success')
            return redirect(url_for('admin.order.index'))

    else:
        error = ''
        if form.email.errors:
            error = form.email.errors[0]
        if form.phone.errors:
            error = form.phone.errors[0]     
        if form.amount.errors:
            error = form.amount.errors[0] 

        flash(error)
        return redirect(url_for('admin.order.create'))


@order_bp.route('/detail/<string:order_no>')
@login_required
def detail(order_no):
    order = Order.query.filter_by(order_no=order_no).first_or_404()
    return render_template('admin/order/detail.html',order=order)


@order_bp.route('/delete/<string:order_no>')
@login_required
def delete(order_no):
    order = Order.query.filter_by(order_no=order_no).first()
    db.session.delete(order)
    db.session.commit()
    flash('Delete Success')
    return redirect(url_for('admin.order.index'))

简单总结一下：
查找：

Order.query.order_by(Order.created_at.desc()).all()

插入：

order = Order(email=email,phone=phone,amount=amount,remark=remark,order_no=order_no,user_id=user_id,status=status,created_at=created_at)
            db.session.add(order)
            db.session.commit()

修改：

order = Order.query.filter(Order.order_no == order_no).first()
            order.remark = remark
            db.session.commit()

删除：

order = Order.query.filter_by(order_no=order_no).first()
    db.session.delete(order)
    db.session.commit()

Flask-SQLAlchemy 的简单用法基本上是这样，还有哪些一对多，多对多的关系，以后有时间会补充