Flask-SQLAlchemy

Flask-SQLAlchemy旨在通过提供默认和额外辅助功能简化在flask框架中使用SQLAlchemy的过程，使得我们能更容易的完成一些常用功能。也可以说是对SQLAlchemy进行了封装或者扩展，所以我们首先要对SQLAlchemy有一定认知才能够有效的学习Flask-SQLAlchemy

既然是Flask-SQLAlchemy那么我们必须要安装Flask && Flask-SQLAlchemy才能进入正文的学习

pip install flask flask_sqlalchemy

跟着官网我们先上一个基本的ORM示例

1. 定义ORM模型并且初始化数据库

Firstly create your Flask application, load the configuration of choice and then create the SQLAlchemy object by passing it the application.（首先创建Flask应用，然后加载可选配置，接着创建SQLAlchemy对象并绑定到应用中去，最后定义模型并且调用SQLAlchemy.create_all()初始化数据库）

#!/usr/bin/env python
#coding:utf-8
'''
file: flaskSqlalchemy.py
date: 2018/2/12 13:47
author: lockey
email: iooiooi23@163.com
github: https://github.com/LockeyCheng
csdn: http://blog.csdn.net/Lockey23
desc: 
'''
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqlconnector://lockey:lockey23@localhost:3306/test'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

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


if __name__ == '__main__':
    db.create_all()
    print 'Database created!'

2.模型的增删改查操作

增加用户

def addUser():
    admin = User(username='admin', email='admin@example.com')
    guest1 = User(username='guest1', email='guest1@example.com')
    guest2 = User(username='guest2', email='guest2@example.com')
    guest3 = User(username='guest3', email='guest3@example.com')
    db.session.add(admin)#添加单个用户
    db.session.add_all([guest1,guest2,guest3])#批量添加
    db.session.commit()#提交事务

删除用户

def delUser():
    user = User.query.filter_by(username='guest1').first()
    try:
        db.session.delete(user)
        db.session.commit()
    except:
        db.session.rollback()

查找用户

def queryUser():
    users = User.query.all()
    if users:
        for user in users:
            print user.username
    else:
        print 'no user to show!'

修改用户

def updateUser():
    try:
        toUpdate = User.query.filter_by(username='admin').first()
        toUpdate.username = 'lockey'
        db.session.commit()
    except:
        db.session.rollback()

3. 删除数据库

http://flask-sqlalchemy.pocoo.org/2.3/binds/

>>> db.create_all()
>>> db.create_all(bind=['users'])
>>> db.create_all(bind='appmeta')
>>> db.drop_all(bind=None)

4. 一对多关系以及外键约束

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    category = db.relationship('Category',
                               backref=db.backref('user', lazy=True))#一对多关系

    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), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'),
                          nullable=False)#外键约束

def addCategory():
    cat1 = Category(name='category1', user_id=1)
    db.session.add(cat1)
    db.session.commit()

官网文档在此