Flask之数据库

MySQL8软件安装

略

连接MySQL数据库

pip install pymysql

pip install flask-sqlalchemy

可能出现的问题：已解决：sqlalchemy.exc.ObjectNotExecutableError: Not an executable object‘……’的报错问题_杜小白也想的美的博客-CSDN博客

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text


app = Flask(__name__)

# MySQL 所在的主机名
HOSTNAME = "127.0.0.1"
# MySQL 监听的端口号
PORT = 3306
# 连接 MySQL 的用户名
USERNAME = "root"
# 连接 MySQL 的密码
PASSWORD = "2618"
# MySQL 上创建的数据库的名称
DATABASE = "mydatabase"
# 数据库字符集
CHARSET = "utf8mb4"

app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset={CHARSET}"


# 在 app.config 中设置好连接数据库的信息
# 然后使用 SQLAlchemy(app) 创建一个 db 信息
# SQLAlchemy 会自动读取 app.config 中连接数据库的信息
db = SQLAlchemy(app)

with app.app_context():
    with db.engine.connect() as con:
        res = con.execute(text("SELECT 1"))
        print(res.fetchone())  # 连接成功会输出“(1, )”


@app.route('/')
def hello_world():  # put application's code here
    return 'Hello World!'


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

ORM模型与表的映射

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text

app = Flask(__name__)
# configuration...
app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset={CHARSET}"
db = SQLAlchemy(app)


# 创建 User 表
class User(db.Model):
    __tabelname__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # varchar
    username = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)


# 声明对象, 不算执行 (插入到数据库中)
user = User(username="张三", password='111111')
# sql: insert user(username, password) values('张三', '111111');


# 将表同步到数据库中
with app.app_context():
    db.create_all()


@app.route('/')
def hello_world():  # put application's code here
    return 'Hello World!'


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

ORM模型的CRUD操作

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text

app = Flask(__name__)
# configuration...
app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset={CHARSET}"


# 增
@app.route("/user/add")
def add_user():
    # 创建 ORM 对象
    user1 = User(username="张三", password='111111')
    # sql: insert user(username, password) values('张三', '111111');
    user2 = User(username="李四", password='222222')
    user3 = User(username="王五", password='333333')

    # 将 ORM 对象添加到 db.session 中
    db.session.add(user1)
    # 将 db.session 中的改变同步到数据库中
    db.session.commit()

    return "用户创建成功"


# 查
@app.route("/user/query")
def query_user():
    # get 查找: 根据主键查找
    # user = User.query.get(1)
    # print(f"{user.id}: {user.username}-{user.password}")

    # filter_by查找
    users = User.query.filter_by(username="张三")
    print(type(users))  # <class 'flask_sqlalchemy.query.Query'>
    for user in users:
        print(type(user))

    return "数据查找成功"


# 改
@app.route("/user/update")
def update_user():
    user = User.query.filter_by(username="张三").first()
    user.password = "222222"
    db.session.commit()

    return "数据修改成功"


# 删
@app.route("/user/delete")
def delete_user():
    user = User.query.get(1)
    db.session.delete(user)
    db.session.commit()

    return "数据删除成功"


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

ORM模型外键与表的关系

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text


app = Flask(__name__)
# configuration...
app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset={CHARSET}"
db = SQLAlchemy(app)


# 创建 User 表
class User(db.Model):
    __tabelname__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # varchar
    username = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)

    # step 2, way 1.2
    # articles = db.relationship("Article", back_populates="author")


class Article(db.Model):
    __tablename__ = "article"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)

    # 添加作者的外键
    # step 1
    author_id = db.Column(db.Integer, db.ForeignKey("user.id"))

    # step 2, way 1.1
    # author = db.relationship("User", back_populates="articles")
    # step 2, way 2
    author = db.relationship("User", backref="articles")


@app.route("/article/add")
def article_add():
    article1 = Article(title="Flask", content="Flask*****")
    article1.author = User.query.get(2)

    article2 = Article(title="Django", content="Django*****")
    article2.author = User.query.get(2)

    db.session.add_all([article1, article2])
    db.session.commit()

    return "文章添加成功"

@app.route("/article/query")
def article_query():
    user = User.query.get(2)
    for article in user.articles:
        print(article.title)

    return "文章查找成功"


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

flask-migrate迁移ORM模型

pip install flask-migrate

1. flask db init，只需要在项目新建时执行一次，会生成migrations文件夹，如确实有需要，可将该文件夹删除再重新执行flask db init
2. flask db migrate，当表的结构有变化或新增、删除表时执行，先migrate
3. flask db upgrade，当表的结构有变化或新增、删除表时执行，再upgrade