Flask中SQLAlchemy的使用

更多Flask阅读指南：

Flask基础学习

• 使用virtualenv实现python的虚拟环境
• Python Flask 入门开发
• Python Flask框架配置管理加载的三种方式
• Python Flask 路由配置
• Python Flask静态文件配置
• Python Flask模板渲染
• Python Flask 蓝图Blueprint
• Flask 使用Context上下文
• Flask中SQLAlchemy的使用

Flask 扩展

• 使用flask-script实现Flask项目定制shell功能
• Flask 中使用flask-admin实现数据模型绑定视图
• Flask 中使用mysql数据库
• Flask使用flask-migrate实现数据库迁移

Flask 进阶

• Flask中使用Blinker实现信号传递
• Flask中使用flask-httpauth实现HTTP协议的token验证

SQLAlchemy 是python里提供的一款SQL工具，支持数据库连接、映射、对象关系映射（ORM）以及数据库操作等功能。SQLAlchemy 在构建在 WSGI 规范上的下一代 Python Web 框架中得到了广泛应用。如下介绍如何在Flask项目中配合SQLAlchemey实现数据库管理。

安装

Flask中提供Sqlalchemy插件包，可以通过pip进行安装下载：

$ pip install flask_sqlalchemy

使用

1、创建model.py

from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from sqlalchemy import Integer, String, Text, DateTime, Boolean, ForeignKey, Column
from sqlalchemy.orm import relationship
from flask_login import UserMixin, AnonymousUserMixin, current_user

db = SQLAlchemy()

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    }

    id = Column(Integer, primary_key=True, autoincrement=True)
    email = Column(String(64), unique=True, index=True)
    username = Column(String(20), unique=True, index=True)
    password_hash = Column(String(128))
    role_id = Column(Integer, ForeignKey('roles.id'))
    role = relationship('Role', back_populates='users')

    # basic info
    name = Column(String(64))
    location = Column(String(64))
    about_me = Column(Text())
    member_since = Column(DateTime(), default=datetime.utcnow())
    last_seen = Column(DateTime(), default=datetime.utcnow())
    confirmed = Column(Boolean, default=False)
    posts = relationship('Post', back_populates="user")

    def __init__(self, **kwargs):
        super(User, self).__init__(**kwargs)
        if self.role is None:
            if self.email == current_app.config['ADMIN_EMAIL'] or self.email == "admin@enjoytoday.cn":
                self.role = Role.query.filter_by(pemissions=0xff).first()
            if self.role is None:
                self.role = Role.query.filter_by(default=True).first()

    def ping(self):
        self.last_seen = datetime.utcnow()
        db.session.add(self)

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

    def can(self, permissions):
        u"""
        权限认证
        :param permissions:
        :return:
        """
        return True
        # return self.role is not None and \
        #        (self.role.permissions & permissions) == permissions

    def is_administrator(self):
        return self.can(Permission.ADMINISTER)

    @property
    def password(self):
        raise AttributeError('password is not a readable attribute.')

    @password.setter
    def password(self,password):
        self.password_hash = generate_password_hash(password)

    def verify_password(self, password):
        u"""
        验证密码
        :param password:
        :return:
        """
        return check_password_hash(self.password_hash, password)

    @staticmethod
    def insert_default_users():
        u"""
        初始化插入几个基本的账户
        :return:
        """
        users= {
            'admin': ('admin@enjoytoday.cn', 'cai123', 1),
            'hfcai': ('1710600212@qq.com', 'enjoytoday123', 3),
            'caihaifei': ('caihaifei@enjoytoday.cn', '123456',3)
        }

        for username in users:
            u=User.query.filter_by(username=username).first()
            if u is None:
                u = User(username=username)
            u.password = users[username][1]
            u.email = users[username][0]
            u.role = Role.query.filter_by(id=users[username][2]).first()
            # u.role_id = User[username][2]
            db.session.add(u)
        db.session.commit()

    @property
    def to_json(self):
        json={
            "user_id": self.id,
            "name": self.name,
            "username": self.username,
            "email": self.email,
            "about_me": self.about_me,
            "role_id": self.role_id,
            "location": self.location,
            "member_since": self.member_since,
            "last_seen": self.last_seen
        }
        return json

2、main.py中使用

from flask import Flask
from model import db

app = Flask(__name__)

if __name__ == '__main__':
    db.init_app(app)
    app.run() 

3、更多

有关SQLAlchemy更多的使用，可访问SQLAlchemy官方文档。