orm模型与表的映射

创建表

• app.py

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
 
app = Flask(__name__)
 
HOSTNAME = "127.0.0.1"
PORT = 3306
USERNAME = "root"
PASSWORD = "123456"
DATABASE = "db_test"
app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8mb4"
 
db = SQLAlchemy(app)
 
class User(db.Model):
    __tablename__ = "user"
    # 类型，主键，自增
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # varchar，null=
    username = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)
 
# 创建表
with app.app_context():
    db.create_all()
 
@app.route('/')
def hello_world():
    return 'Hello World!'
 
if __name__ == '__main__':
    app.run()

• 查看数据库，表创建成功
  

crud操作

• 添加数据

# app.py
 
@app.route("/user/add")
def add_user():
    #1.创建ORM对象
    user = User(username="张三", password='123456')
    #2.将0RM对象添加到db.session中
    db.session.add(user)
    #3.将db.session中的改变同步到数据库中
    db.session.commit()
    return "用户创建成功!"

• 查询数据

@app.route("/user/query")
def query_user():
    # 1.get查找:根据主键查找
    # user = User.query.get(1)
    # print(f"{user.id}:{user.username}-{user.password}")
 
    # 2.filter_by查找
    # Query:类数组
    users = User.query.filter_by(username="张三")
    for user in users:
        print(f"{user.username}---{user.password}")
    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():
    # 1.查找
    user = User.query.get(1)
    # 2.从db.session中删除
    db.session.delete(user)
    # 3.将db.session中的修改，同步到数据库中
    db.session.commit()
    return "数据删除成功!"

设置外键

• app.py

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)
    # 添加作者的外键
    author_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    # backref:会自动的给User模型添加个articles的属性，用来获取文章列表
    author = db.relationship("User", backref="articles")
 
# 创建表
with app.app_context():
    db.create_all()
 
# 添加文章
@app.route("/article/add")
def article_add():
    article1 = Article(title="Flask学习大纲", content="Flaskxxxx")
    article1.author = User.query.get(2)
 
    article2 = Article(title="Django学习大纲", content="Django最全学习大纲")
    article2.author = User.query.get(2)
    # 添加到session中
    db.session.add_all([article1, article2])
    # 同步session中的数据到数据库中
    db.session.commit()
    return "文章添加成功!"
 
# 查找用户下的文章
@app.route("/article/query")
def query_article():
    user = User.query.get(2)
    for article in user.articles:
        print(article.title)
        # print(f"{article.title}---{article.author}")
        return "文章查找成功!"