FLASK & flask_sqlalchemy过滤方法和查询方法总结
本帖最后由 金刚 于 2020-4-21 14:40 编辑### 数据库模型
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import click
import os
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = os.getenv(
"DATABASE_URL", "sqlite:///" + os.path.join(app.root_path, "data.db"))
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)
@app.cli.command()
def initdb():
db.create_all()
click.echo("initialized database.")
class Note(db.Model):
id = db.Column(db.Integer, primary_key=True)
body = db.Column(db.Text)
def __repr__(self):
return "<Note %r>" % self.body
### CRUD
#### Create （添加数据）
>>> from sqlalchemy_test import db, Note
>>> note1 = Note(body="remember Sammy Jankis")
>>> note2 = Note(body="SHAVE")
>>> note3 = Note(body="DON'T BELIEVE HIS LIES, HE IS THE ONE, KILL HIM")
>>> db.session.add(note1)
>>> db.session.add(note2)
>>> db.session.add(note3)
>>> db.session.commit()
>>>
* 模型类代表一整个表数据
* 模型类的实例对象代表一条记录
>>> note1
<Note 'remember Sammy Jankis'>
>>> note2
<Note 'SHAVE'>
>>> note3
<Note "DON'T BELIEVE HIS LIES, HE IS THE ONE, KILL HIM">
* 模型类的属性代表一个字段
>>> note1.id
4
>>> note1.body
'remember Sammy Jankis'
>>> note2.id
5
>>> note2.body
'SHAVE'
>>>
#### Read（数据库中读取数据）
总体格式：<模型类>.query.<过滤方法>.<查询方法>
* 常用的查询方法
##### 查找全部记录
>>> Note.query.all()
[<Note 'remember Sammy Jankis'>, <Note 'SHAVE'>, <Note "DON'T BELIEVE HIS LIES,HE IS THE ONE, KILL HIM">, <Note 'remember Sammy Jankis'>, <Note 'SHAVE'>, <Note "DON'T B
ELIEVE HIS LIES, HE IS THE ONE, KILL HIM">]
>>>
##### 查询第一条记录
>>> Note.query.first()
<Note 'remember Sammy Jankis'>
>>>
##### get 返回指定主键值
>>> note2 = Note.query.get(2)
>>> note2
<Note 'SHAVE'>
>>>
##### count()返回记录的数量
>>> Note.query.count()
6
>>>
##### 常用的SQLALchemy过滤方法
###### filter过滤（注意：下面的不是赋值号，而是等号）
>>> Note.query.filter(Note.body=="SHAVE").first()
<Note 'SHAVE'>
>>>
* 查看sql语句
>>> print(Note.query.filter(Note.body=="SHAVE"))
SELECT note.id AS note_id, note.body AS note_body
FROM note
WHERE note.body = ?
>>>
* 常用的查询操作符
LIKE:
>>> Note.query.filter(Note.body.like("%BELIEVE%")).all()
[<Note "DON'T BELIEVE HIS LIES,HE IS THE ONE, KILL HIM">, <Note "DON'T BELIEVE HIS LIES, HE IS THE ONE, KILL HIM">]
>>> Note.query.filter(Note.body.like("%BELIEVE%")).first()
<Note "DON'T BELIEVE HIS LIES,HE IS THE ONE, KILL HIM">
>>>
IN:
>>> Note.query.filter(Note.body.in_(["BELIEVE","Jankis","SHAVE"])).all()
[<Note 'SHAVE'>, <Note 'SHAVE'>]
>>> Note.query.filter(Note.body.in_(["BELIEVE","Jankis","SHAVE"])).first()
<Note 'SHAVE'>
NOT IN:
>>> Note.query.filter(~Note.body.in_(["SHAVE"])).all()
[<Note 'remember Sammy Jankis'>, <Note "DON'T BELIEVE HIS LIES,HE IS THE ONE, KILL HIM">, <Note 'remember Sammy Jankis'>, <Note "DON'T BELIEVE HIS LIES, HE IS THE ONE,
KILL HIM">]
>>> Note.query.filter(~Note.body.in_(["SHAVE"])).first()
<Note 'remember Sammy Jankis'>
>>>
AND:
* 使用and_()
from sqlalchemy import and_
>>> Note.query.filter(and_(Note.body=="remember Sammy Jankis", Note.id=="1")).all()
[<Note 'remember Sammy Jankis'>]
>>> Note.query.filter(and_(Note.body=="remember Sammy Jankis", Note.id=="4")).all()
[<Note 'remember Sammy Jankis'>]
或在filter()中加入多个表达式，使用逗号分隔
>>> Note.query.filter(Note.body=="SHAVE", Note.id==2).all()
[<Note 'SHAVE'>]
>>> Note.query.filter(Note.body=="SHAVE", Note.id==5).all()
[<Note 'SHAVE'>]
>>>
或叠加调用多个filter()/filter_by方法
>>> Note.query.filter(Note.body=="SHAVE").filter(Note.id==2).all()
[<Note 'SHAVE'>]
>>> Note.query.filter(Note.body=="SHAVE").filter(Note.id==5).all()
[<Note 'SHAVE'>]
>>>
OR:
>>> from sqlalchemy import or_
>>> Note.query.filter(or_(Note.body=="SHAVE", Note.id==2)).all()
[<Note 'SHAVE'>, <Note 'SHAVE'>]
>>> Note.query.filter(or_(Note.body=="SHAVE", Note.id==1)).all()
[<Note 'remember Sammy Jankis'>, <Note 'SHAVE'>, <Note 'SHAVE'>]
>>>
##### filter_by()使用方式
>>> Note.query.filter_by(body="SHAVE").all()
[<Note 'SHAVE'>, <Note 'SHAVE'>]
>>>
#### Update()（更新一条记录）
*更新一条记录，直接赋值给模型类的字段属性一个值
>>> note = Note.query.get(2)
>>> note.body
'SHAVE'
>>> note.body = "SHAVE LEFT THIGH"
>>> db.session.commit()
>>>
#### Delete() （删除一条记录）
>>> note = Note.query.get(2)
>>> db.session.delete(note)
>>> db.session.commit()
>>>
笔记呢？ ？？？？？
页:
[1]