Python编程：Flask数据库扩展Flask-SQLAlchemy

pip install flask-sqlalchemy
pip install flask-mysqldb

模型类 -> sql
数据库结果 -> 模型类

# python3
pip install pymysql 
pymysql.install_as_MySQLdb()
# python2
pip install MySQL-Python

# 1、数据库连接
app.config["SQLALCHEMY_DATABASE_URI"] = "msyql://root:123456@127.0.0.1:3306/db_name"
# 2、自动提交（不推荐）
app.config["SQLALCHEMY_COMMIT_ON_TEARDOWN"] = True
# 3、修改自动跟踪
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
# 4、显示原始SQL
app.config["SQLALCHEMY_ECHO"] = True

# -*- coding: utf-8 -*-
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
# 配置参数
class Config(object):
    SQLALCHEMY_DATABASE_URI = "mysql://root:123456@127.0.0.1:3306/demo"
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    # SQLALCHEMY_ECHO = True
app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)
# 创建数据库模型类
class Role(db.Model):
    """用户角色"""
    __tablename__ = "tbl_roles"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32), unique=True)
    users = db.relationship("User", backref="role")
    def __repr__(self):
        return "<Role name: {}>".format(self.name)
class User(db.Model):
    """用户表"""
    __tablename__ = "tbl_users"  # 表名
    id = db.Column(db.Integer, primary_key=True)  # 整型主键默认自增
    name = db.Column(db.String(64), unique=True)
    email = db.Column(db.String(128), unique=True)
    password = db.Column(db.String(128))
    role_id = db.Column(db.Integer, db.ForeignKey("tbl_roles.id"))
    def __repr__(self):
        return "<User name: {}>".format(self.name)
if __name__ == '__main__':
    # 删除表
    db.drop_all()
    # 创建表
    db.create_all()

# 1、创建一条数据
role1 = Role(name="admin")
role2 = Role(name="stuff")
db.session.add(role1)
db.session.add(role2)
db.session.commit()
# 2、 一次保存多个数据
user1 = User(name="张三", email="123@qq.com", password="123456", role_id=role1.id)
user2 = User(name="李四", email="456@qq.com", password="12345x", role_id=role2.id)
user3 = User(name="王五", email="339@qq.com", password="12345y", role_id=role1.id)
user4 = User(name="刘能", email="226@qq.com", password="12345v", role_id=role2.id)
user5 = User(name="赵四", email="778@qq.com", password="12345b", role_id=role1.id)
db.session.add_all([user1, user2, user3, user4, user5])
db.session.commit()

# 1、简单查询
rows = Role.query.all()
row = Role.query.first()
row = Role.query.get(<id>)
rows = db.seesion.query(Role).all()
row = db.seesion.query(Role).first()
row = db.seesion.query(Role).get(<id>)
# 2、条件过滤（与关系）
row = Role.query.filter(Role.name="张三", Role.role_id=2).first()
row = Role.query.filter_by(name="张三", role_id=2).first()
# 3、模糊查询（或关系）
from sqlalchemy import or_
row = Role.query.filter(or_(Role.name.startswith("张"), Role.role_id=2)).first()
# 4、分页
rows = Role.query.offset(2).limit(10).all()
# 5、排序
rows = Role.query.order_by("-id").all()
rows = Role.query.order_by(Role.id.desc()).all()
# 6、分组
from sqlalchemy import func
rows = db.session.query(Role.id, func.count(Role.id)).group_by(Role.id)
# 7、外键查询
user = User.query.get(1)
user.role.name
role = Role.query.get(1)
role.users

# 1、更新对象
user = User.query.get(1)
user.name = "python"
db.session.add(user)
db.session.commit()
# 2、直接更新数据
User.query.filter_by(name="张三").update({"name": "python"})
db.session.commit()

user = User.query.get(1)
db.session.delete(user)
db.session.commit()