Flask进阶篇（二）数据库操作

方法一：

import sqlalchemy as db
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base


engine = db.create_engine('mysql+pymysql://root:root@host:3306/test')


# 创建数据库sqlalchemy工具对象
Base = declarative_base()


class Role(Base):
    __tablename__ = "tb_role"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)


class User(Base):
    __tablename__ = "tb_user"
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(64), unique=True)
    email = db.Column(db.String(64), unique=True)
    pwd = db.Column(db.String(64))
    role_id = db.Column(db.ForeignKey(Role.id))
    company = relationship("Role", backref="user_of_role")


DBsession = sessionmaker(bind=engine)
session = DBsession()


if __name__ == '__main__':
    # 清空当前数据库中所有的表
    Base.metadata.drop_all(engine)
    # 创建表
    Base.metadata.create_all(engine)

    """增加"""
    # 新增一条数据
    # user = User(id=2, name='zhangsan', email='10000@qq.com', pwd='123456')
    # session.add(user)
    # 新增多条数据
    # role = [Role(id=1, name='剑魂'), Role(id=2, name='法师'), Role(id=3, name='元素')]
    # session.add_all(role)
    # session.commit()
    # session.close()

    """查询"""
    # query = session.query(User)
    # for user in query:
    #     print(user.name)
    # query2 = query.filter(User.id == 1).scalar()
    # print(query2.name)
    # query3 = query.filter(User.email.endswith("qq.com"))
    # for user in query3:
    #     print(user.name)

    """修改"""
    # query = session.query(User)
    # query.filter(User.id == 1).update({User.name: 'lisi'})
    # session.commit()

    """删除"""
    # query = session.query(User)
    # query.filter(User.id == 1).delete()
    # session.commit()

方法二：

from flask_sqlalchemy import SQLAlchemy
from flask import Flask
# 配置数据库连接
app=Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@host:3306/test'
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)
# 创建用户角色表
class Role(db.Model):
    __tablename__ = 'role'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True, nullable=True)
    # 定义一对多关联关系
    user = db.relationship('User', backref='role')

    def __repr__(self):
        return '<Role %r>' % self.name
# 创建用户表
class User(db.Model):
    __tablename__ = 'user'
    uid = db.Column(db.String(11), primary_key=True)
    uname = db.Column(db.String(11), unique=True, nullable=True)
    pwd = db.Column(db.String(11), nullable=True)
    phone = db.Column(db.String(11), nullable=True)
    regtime = db.Column(db.String(20), nullable=True)
    stat = db.Column(db.String(1), nullable=True)
    # 定义外键
    role_id = db.Column(db.Integer, db.ForeignKey('role.id'))

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


db.create_all()