FLASK & flask_sqlalchemy过滤方法和查询方法总结

金刚

### 数据库模型

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.<过滤方法>.<查询方法>
* 常用的查询方法

sqlalchemy查询方法

sqlalchemy查询方法

##### 查找全部记录

>>> 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过滤方法

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()
>>>

Judie

笔记呢？

老八秘制

？？？？？