python3 flask开发06 数据库 创建 插入 更新 操作

QQ群：476842922(欢迎加群讨论学习)
新建app.py文件

from flask_sqlalchemy import SQLAlchemy  # 导入扩展类
import os
import sys
from flask import Flask

name = 'Grey Li'
movies = [
   {'title': 'My Neighbor Totoro', 'year': '1988'},
   {'title': 'Dead Poets Society', 'year': '1989'},
   {'title': 'A Perfect World', 'year': '1993'},
   {'title': 'Leon', 'year': '1994'},
   {'title': 'Mahjong', 'year': '1996'},
   {'title': 'Swallowtail Butterfly', 'year': '1996'},
   {'title': 'King of Comedy', 'year': '1999'},
   {'title': 'Devils on the Doorstep', 'year': '1999'},
   {'title': 'WALL-E', 'year': '2008'},
   {'title': 'The Pork of Music', 'year': '2012'},
]

app = Flask(__name__)
#位置不能调整，调整会报错
app.config['SQLALCHEMY_DATABASE_URI'] = prefix + os.path.join(app.root_path, 'data.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False  # 关闭对模型修改的监控

db = SQLAlchemy(app)

#######################################################
WIN = sys.platform.startswith('win')
if WIN:  # 如果是 Windows 系统，使用三个斜线
   prefix = 'sqlite:///'
else:  # 否则使用四个斜线
   prefix = 'sqlite:////'

#########################################################
class User(db.Model):  # 表名将会是 user（自动生成，小写处理）
   id = db.Column(db.Integer, primary_key=True)  # 主键
   name = db.Column(db.String(20))  # 名字

class Movie(db.Model):  # 表名将会是 movie
   id = db.Column(db.Integer, primary_key=True)  # 主键
   title = db.Column(db.String(60))  # 电影标题
   year = db.Column(db.String(4))  # 电影年份

@app.route('/')
def index():
   user = User.query.first()  # 读取用户记录
   movies = Movie.query.all()  # 读取所有电影记录
   return render_template('index.html', user=user, movies=movies)

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

执行命令python

from app import db
db.create_all()
创建数据库文件data.db
#####执行数据库数据插入、更新操作

from app import db
db.create_all()
from app import User, Movie
user = User(name=‘Grey Li’)
m1 = Movie(title=‘Leon’, year=‘1994’)
m2 = Movie(title=‘Mahjong’, year=‘1996’)
db.session.add(user)
db.session.add(m1)
db.session.add(m2)
db.session.commit()
from app import Movie
movie = Movie.query.first()
movie.title
‘Leon’

Movie.query.all()
[<Movie 1>, <Movie 2>]

Movie.query.count()
2

Movie.query.get(1)
<Movie 1>

Movie.query.filter_by(title=‘Mahjong’).first()
<Movie 2>

Movie.query.filter(Movie.title==‘Mahjong’).first()
<Movie 2>

movie = Movie.query.get(2)
movie.title = ‘WALL-E’
movie.year = ‘2008’
db.session.commit()

命令行输入如下代码执行

    name = 'Grey Li'
    movies = [
        {'title': 'My Neighbor Totoro', 'year': '1988'},
        {'title': 'Dead Poets Society', 'year': '1989'},
        {'title': 'A Perfect World', 'year': '1993'},
        {'title': 'Leon', 'year': '1994'},
        {'title': 'Mahjong', 'year': '1996'},
        {'title': 'Swallowtail Butterfly', 'year': '1996'},
        {'title': 'King of Comedy', 'year': '1999'},
        {'title': 'Devils on the Doorstep', 'year': '1999'},
        {'title': 'WALL-E', 'year': '2008'},
        {'title': 'The Pork of Music', 'year': '2012'},
    ]    
    user = User(name=name)
    db.session.add(user)
    for m in movies:
        movie = Movie(title=m['title'], year=m['year'])
        db.session.add(movie)
    
    db.session.commit()