Flask框架学习4---Model和数据库

Flask建立model并且跟数据库进行关联，需要使用Flask-SQLAlchemy 模块

目录

1.安装

2.连接数据库

3.创建数据模型

4.创建与删除表

5.增删改查

---

1.安装

pip install flask_sqlalchemy

2.连接数据库

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 数据库需要提前建立好
# 需要安装PyMysql模块，root账号，talent密码@后是数据库地址跟端口，learn_sqlalchemy是数据库名称
DB_URI = "mysql+pymysql://root:talent@127.0.0.1:3306/learn_sqlalchemy?charset=utf8"
# 3.添加到到配置中
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 4.实例化一个对象，将app传进去
db = SQLAlchemy(app)

3.创建数据模型

# 继承db.Model
class User(db.Model):
    __tablename__ = 'user'  # 表名
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), nullable=False)


class Article(db.Model):
    __tablename__ = "article"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(50), nullable=False)
    uid = db.Column(db.Integer, db.ForeignKey("user.id"))  # 通过外键关联user的id
    author = db.relationship("User", backref='article')  # 关联user表

4.创建与删除表

db.drop_all()         #删除表
db.create_all()       #创建表

5.增删改查

@app.route('/add/')
def add_article():
    user = User(username='大力')
    article = Article(title='今天1/20号')#创建模型对象
    article.author = user
    db.session.add(article)#添加到表中
    db.session.commit()    #提交
    return '添加一个article'


@app.route('/query/')
def query_user():
    s = ''
    users = User.query.all()#查询所用用户信息
    for user in users:
        s += user.username
    return s


@app.route('/update/')
def updatae_name():
    user1 = User.query.filter(User.username == 'derek').first()#查询名称等于derek的用户
    user1.username = 'lll'            #修改username属性
    db.session.commit()               #提交修改结果
    return '修改成功'


@app.route('/delete/')
def delete_name():
    user1 = User.query.filter(User.username == 'ljj').first()#查询名称等于ljj的用户
    db.session.delete(user1) #删除
    db.session.commit()        #提交
    return '删除成功'

全部代码

#!usr/bin/python
# -*- coding: utf-8 -*-
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 数据库需要提前建立好
# 需要安装PyMysql模块，root账号，talent密码@后是数据库地址跟端口，learn_sqlalchemy是数据库名称
DB_URI = "mysql+pymysql://root:talent@127.0.0.1:3306/learn_sqlalchemy?charset=utf8"
# 3.添加到到配置中
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 4.实例化一个对象，将app传进去
db = SQLAlchemy(app)


# 继承db.Model
class User(db.Model):
    __tablename__ = 'user'  # 表名
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), nullable=False)


class Article(db.Model):
    __tablename__ = "article"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(50), nullable=False)
    uid = db.Column(db.Integer, db.ForeignKey("user.id"))  # 通过外键关联user的id
    author = db.relationship("User", backref='article')  # 关联user表


# db.drop_all()  # 删除表
# db.create_all()  # 创建表

# user = User(username='derek')
# article = Article(title='今天1/20号')
# article.author = user
#
# db.session.add(article)
# db.session.commit()


@app.route('/')
def hello_world():
    return 'Hello World!'


@app.route('/add/')
def add_article():
    user = User(username='大力')
    article = Article(title='今天1/20号')
    article.author = user
    db.session.add(article)
    db.session.commit()
    return '添加一个article'


@app.route('/query/')
def query_user():
    s = ''
    users = User.query.all()
    for user in users:
        s += user.username
    return s


@app.route('/update/')
def updatae_name():
    user1 = User.query.filter(User.username == 'derek').first()
    user1.username = 'lll'
    db.session.commit()
    return '修改成功'


@app.route('/delete/')
def delete_name():
    user1 = User.query.filter(User.username == 'ljj').first()
    db.session.delete(user1)
    db.session.commit()
    return '删除成功'


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