Flask建立model并且跟数据库进行关联,需要使用Flask-SQLAlchemy 模块
目录
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()