安装相关模块
pip install flask-sqlalchemy
启动代码
from flask_sqlalchemy import SQLAlchemy
import datetime
app = Flask(__name__)
bootstrap = Bootstrap(app)
app.debug = True
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db/test.db'
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
db = SQLAlchemy(app)
#这个模块一定要在db变量声明之后
#否则在model.user模块中将引用不到db变量
from model.user import Userinfoflask app.py
from app import db
class Userinfo(db.Model):
id = db.Column(db.Integer, primary_key=True,autoincrement=True)
name = db.Column(db.String(20),nullable=False,unique=True)
passwd = db.Column(db.String(20),nullable=False)
usertime=db.Column(db.DateTime,nullable=False)
def __repr__(self):
return '<User %r>' % self.namemodels.user.py
实现初始化数据库数据
from model.user import Userinfo
@app.route('/')
def main():
db.create_all()
users=Userinfo.query.filter_by(name='admin').first()
if not users:
admin=Userinfo(name='admin',passwd='admin',usertime=datetime.datetime.now())
db.session.add(admin)
db.session.commit()
return render_template('index.html', msg=m.loginmsg)View Code
pycharm可视化打开sqlite
首先打开database，一般在pycharm的右边侧栏中，也可以通过（View-->Tool Windows --> database）打开
Data Source -> Sqlite(Xerial)
实现model的增删改查
# 查询所有用户
users_list = User.query.all()
# 查询用户名称为 test 的第一个用户, 并返回用户实例, 因为之前定义数据库的时候定义用户名称唯一, 所以数据库中用户名称为 test 的应该只有一个.
user = User.query.filter_by(username = 'test').first()
# or
user = User.query.filter(User.username == 'test').first()
# 查询用户 id, 用户 email, 用户 roles 类型
# 模糊查询, 查找用户名以abc 结尾的所有用户
users_list = User.query.filter(User.username.endsWith('c')).all()
#查询用户名不是 test 的第一个用户
user = User.query.filter(User.username != 'test').first()查
# 根据用户名, 判断用户是否已经存在
if User.query.filter_by(username=req_data['username']) is not None:
return {'msg': '用户已存在'}
# 创建一个新用户
user = User()
user.username = req_data['username']
user.email = user.username + "@4paradigm.com"
user.password = req_data['password']
for user_id in req_data['roles']:
role = Role.query.filter(Role.id == user_id).first()
user.roles.append(role)
# 将新创建的用户添加到数据库会话中
db.session.add(user)
# 将数据库会话中的变动提交到数据库中, 记住, 如果不 commit, 数据库中是没有变化的.
db.session.commit()添加
# 获取用户对象
user = User.qurey.filter_by(id = user_id).first()
# 修改用户信息
user.username = 'test_update'
# 提交数据库会话
db.session.commit()修改
# 获取用户对象
user = User.qurey.filter_by(id = user_id).first()
# 删除用户
db.session.delete(user)
# 提交数据库会话
db.session.commit()删除
日期和时间的计算处理
from datetime import date
from datetime import timedelta
class Userinfo(db.Model):
usertime=db.Column(db.Date,nullable=False,default=date.today())
date1 = user.usertime
date2 = date.today()
#date2 = date.today()+ timedelta(days=5)
# print(date1)
# print(date2)
# print((date2-date1).days,"**********")
tmpday=(date2-date1).daysdate日期计算
from datetime import datetime
from datetime import timedelta
def get_date(days=N):
return datetime.now() - timedelta(days=days)datetime时间处理
flask实现单表数据序列化
from app import db
from datetime import date
class Userinfo(db.Model):
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
name = db.Column(db.String(20),nullable=False,unique=True)
passwd = db.Column(db.String(20),nullable=False)
type =db.Column(db.Integer,nullable=False)
usertime=db.Column(db.Date,nullable=False,default=date.today())
def __repr__(self):
return '<User %r>' % self.name
def to_json(self):
jsonstr = {
'id': self.id,
'name': self.name,
'type': self.type,
'usertime': self.usertime.strftime("%Y-%m-%d"),
'aaa':"bbbbbb"
}
return jsonstr需要序列化的model
@app.route('/users',methods=['GET'])
def get_users():
users = Userinfo.query.all()
res=[]
for user in users:
obj=user.to_json()
res.append(obj)
return jsonify({'users': res})views中序列化
flask实现关联表序列化
artist_songs = db.Table('artist_songs',
db.Column('song_id', db.Integer, db.ForeignKey('songs.id')),
db.Column('artist_id', db.Integer, db.ForeignKey('artists.id'))
)
class songs(db.Model):
__tablename__ = 'songs'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
title = db.Column(db.String(255), nullable=True)
lyrics = db.Column(db.Text(), nullable=True)
created_at = db.Column(db.DateTime, nullable=False)
artists = db.relationship('artists', secondary=artist_songs, backref=db.backref('artists'), lazy="select")
lyric_copies = db.relationship('lyric_copies', backref='songs', lazy='select')
def jsonstr(self):
artist = []
if self.artists is not None and len(self.artists) != 0:
for art in self.artists:
artist.append(art.name)
lyricarr = []
if self.lyric_copies is not None and len(self.lyric_copies) != 0:
for lyr in self.lyric_copies:
lyricarr.append(lyr.jsonstr())
jsondata = {
'ID': self.id,
'title': self.title,
'artist': artist,
'lyric': self.lyrics,
'copyrights': {
'lyric': lyricarr
}
}
return jsondata
class artists(db.Model):
__tablename__ = 'artists'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(255), nullable=True)
gender = db.Column(db.Integer, nullable=True)
location = db.Column(db.String(255), nullable=True)
created_at = db.Column(db.DateTime, nullable=False)
updated_at = db.Column(db.DateTime, nullable=False)models
