flask-sqlalchemy 数据基本操作--实例

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>]