1.创建User表,表的字段分别为名称,分数,分组
# -*- coding: utf-8 -*-
import os
from flask import Flask
from flask_script import Manager
from flask_sqlalchemy import SQLAlchemy
# 创建flask对象
app = Flask(__name__)
# 创建数据库模型
basedir = os.path.dirname(__file__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'data.sqlite') #配置flask的地址
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app) # 初始化数据库
manager = Manager(app)
@manager.shell # 创建python命令行,在应用上下文中执行
def make_shell_context():
return dict(app=app, db=db, User=User) # 打开命令行时,默认导入工作
class User(db.Model): # 创建User表
id = db.Column(db.Integer, primary_key=True) # 创建id列,并设置为主键
username = db.Column(db.String(80), unique=True) # 创建username列,设置为不可重复
score = db.Column(db.Integer)
group = db.Column(db.String(10))
def __init__(self, username, score, group): # 插入新值的方法
self.username = username
self.score = score
self.group = group
def __repr__(self): # 输出的格式
return '<User %r %i %s>' % (self.username, self.score, self.group)
if __name__ == "__main__":
manager.run()
2.插入数据
#-*- coding: UTF-8 -*-
from ceishi import app, db, User
db.drop_all()
db.create_all()
user = User(username ="wang", score = 98, group = "A")
db.session.add(user)
db.session.commit()
user = User(username ="li", score = 99, group = "B")
db.session.add(user)
db.session.commit()
user = User(username = "ji", score = 89, group = "A")
db.session.add(user)
db.session.commit()
user = User(username = "jiang", score = 95, group = "B")
db.session.add(user)
db.session.commit()
查询数据
User.query.all() 返回所有查询结果
In [1]: User.query.all()
Out[1]:
[<User u'wang' 98 A>,
<User u'li' 99 B>,
<User u'ji' 89 A>,
<User u'jiang' 95 B>]
User.query.first() 返回第一个查询结果
In [2]: User.query.first()
Out[2]: <User u'wang' 98 A>
User.query.get(1)
In [3]: User.query.get(1)
Out[3]: <User u'wang' 98 A>
User.query.count() 计数
In [4]: User.query.count()
Out[4]: 4
User.query.filter().all() 条件过滤
In [5]: User.query.filter(User.score > 90).all()
Out[5]: [<User u'wang' 98 A>, <User u'li' 99 B>, <User u'jiang' 95 B>]
User.query.filter_by().all() 等值过滤
In [6]: User.query.filter_by(score = 99).all()
Out[6]: [<User u'li' 99 B>]
注意条件过滤要在字段前面加上表的类名称,
User.query.limit().all() 限制输出结果
In [7]: User.query.limit(2).all()
Out[7]: [<User u'wang' 98 A>, <User u'li' 99 B>]
User.query.offset().all() 偏移查询
In [8]: User.query.offset(1).all()
Out[8]: [<User u'li' 99 B>, <User u'ji' 89 A>, <User u'jiang' 95 B>]
User.query.order_by(User.score).all() 排序查询
In [9]: User.query.order_by(User.score).all()
Out[9]:
[<User u'ji' 89 A>,
<User u'jiang' 95 B>,
<User u'wang' 98 A>,
<User u'li' 99 B>]
默认是升序既User.score.asc()
还可以降序User.score.desc()
参考:
http://python.usyiyi.cn/documents/sqlalchemy_11/core_tutorial.html#selecting
User.query.group_by(User.group).all() 分组
In [10]: User.query.group_by(User.group).all()
Out[10]: [<User u'ji' 89 A>, <User u'jiang' 95 B>]