flask_sqlalchemy 搭建flask项目指南（一）

1 app.py

# coding=utf-8
from __future__ import print_function
 
from flask import Flask
from flask import Flask, jsonify, request, abort
from flask_cors import CORS
from werkzeug.security import safe_str_cmp
 
 
app = Flask(__name__, static_folder='/data/www/web/static')
app.debug = True
 
CORS(app, resources={r"/*": {"origins": "*"}}, supports_credentials=True)
 
 
# 数据库基本配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:root@127.0.0.1:3306/shop_agent'
# 动态追踪修改设置，如未设置只会提示警告
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
# 查询时会显示原始SQL语句
# app.config['SQLALCHEMY_ECHO'] = True
# 连接池个数
app.config['SQLALCHEMY_POOL_SIZE'] = 10
 
 
from models import db
db.init_app(app)
 
 
from views import *
# 随意无权限路由，简单检查是否正常部署
app.add_url_rule('/user/helloworld', view_func=helloworld, methods=['GET'])
app.add_url_rule('/user/deletedb', view_func=delete_db, methods=['GET'])
app.add_url_rule('/user/initdb', view_func=init_db, methods=['GET'])
 
if __name__ == "__main__":
    app.run(host="0.0.0.0", port=9000, debug=True)

2 models.py

# coding=utf-8
from datetime import datetime
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
 
 
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
 
    # build foreign key relation
    category = db.relationship('Category', backref=db.backref('users', lazy='dynamic'))
 
    created_at = db.Column(db.DateTime, default=None)
    updated_at = db.Column(db.DateTime, default=None)
    deleted_at = db.Column(db.DateTime, default=None)
 
    created = db.Column(db.DateTime)
 
    def __init__(self, username, email, category, category_id, created_at=None, updated_at=None, deleted_at=None):
        self.username = username
        self.email = email
 
        self.category_id = category_id
        self.category = category
 
 
        if updated_at is None:
            updated_at = datetime.utcnow()
        if created_at is None:
            created_at = datetime.utcnow()
        self.updated_at = updated_at
        self.created_at = created_at
        self.deleted_at = deleted_at
 
    def __repr__(self):
        return '<User %r>' % self.username
 
 
class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
 
    def __init__(self, name):
        self.name = name
 
    def __repr__(self):
        return '<Category %r>' % self.name

3 views.py

# coding=utf-8
import sys
import os
import stat
import re
import datetime
import json
import functools
import time
import mimetypes
import uuid
import random
from flask import request
from flask import Flask, jsonify, request, abort, make_response
from flask import send_file, send_from_directory
from werkzeug.utils import secure_filename
from werkzeug.security import generate_password_hash, check_password_hash
 
from auth.auth import Auth
from utils import trueReturn, falseReturn, formalReturn, get_ip_info
from aliyunapi.dysms_python.demo_sms_send import send_sms
from settings import UPLOAD_PATH, DOWNLOAD_PATH, LOG_PATH, MONGO_URL, FILENAME, ERROR_FILENAME
 
from log_shop_agent import log, errlog
from models import db, Category, User
 
 
 
def helloworld():
    """
    
    :return: 
    """
 
    message = "hello world!\n developer: victor\n version: 1.0"
    return jsonify(message)
 
 
def init_db():
    ret = db.create_all()
 
    category_python = Category(name='Python')
    user = User(username='victor', email='bailuzhou163@163.com', category_id=category_python.id, category=category_python)
    db.session.add(category_python)
    db.session.add(user)
    db.session.commit()
 
    users = category_python.users.all()
    user_category = user.category.name
 
    log.logger.info("users: {users}".format(users=users))
    log.logger.info("user_category: {user_category}".format(user_category=user_category))
 
    return jsonify(ret)
 
 
def delete_db():
    ret = db.drop_all()
    return jsonify(ret)

4 sqlalchemy的外键建立

基本结构（看models.py）：

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))

    # build foreign key relation
    category = db.relationship('Category', backref=db.backref('users', lazy='dynamic'))

    created_at = db.Column(db.DateTime, default=None)
    updated_at = db.Column(db.DateTime, default=None)
    deleted_at = db.Column(db.DateTime, default=None)

    created = db.Column(db.DateTime)

    def __init__(self, username, email, category, category_id, created_at=None, updated_at=None, deleted_at=None):
        self.username = username
        self.email = email

        self.category_id = category_id
        self.category = category


        if updated_at is None:
            updated_at = datetime.utcnow()
        if created_at is None:
            created_at = datetime.utcnow()
        self.updated_at = updated_at
        self.created_at = created_at
        self.deleted_at = deleted_at

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


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

    def __init__(self, name):
        self.name = name

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

 

如何建立外键关联：

对用户进行分类，用户要关联分类表，用户表要建立关于分类的关联，被关联的分类表什么都不用做：

category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
category = db.relationship('Category', backref=db.backref('users', lazy='dynamic'))

注意事项：

category_id=初始化必填字段是category.id
category=Category对象
ForeignKey('category.id')里的是Category表的小写

关联要写大写表relationship-'Category'
category对象查询反向查询关联用户的时候用users,即category_obj.users.all()
user正向查询关联分类就用user.category.name