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

金刚

### 数据库模型

1. from flask import Flask
   
2. from flask_sqlalchemy import SQLAlchemy
   
3. import click
   
4. import os
   
5. 
   
6. app = Flask(__name__)
   
7. app.config["SQLALCHEMY_DATABASE_URI"] = os.getenv(
   
8.     "DATABASE_URL", "sqlite:///" + os.path.join(app.root_path, "data.db"))
   
9. app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
   
10. db = SQLAlchemy(app)
    
11. 
    
12. 
    
13. @app.cli.command()
    
14. def initdb():
    
15.     db.create_all()
    
16.     click.echo("initialized database.")
    
17. 
    
18. 
    
19. class Note(db.Model):
    
20.     id = db.Column(db.Integer, primary_key=True)
    
21.     body = db.Column(db.Text)
    
22. 
    
23.     def __repr__(self):
    
24.         return "<Note %r>" % self.body

复制代码

### CRUD

#### Create （添加数据）

1. >>> from sqlalchemy_test import db, Note
   
2. >>> note1 = Note(body="remember Sammy Jankis")
   
3. >>> note2 = Note(body="SHAVE")
   
4. >>> note3 = Note(body="DON'T BELIEVE HIS LIES, HE IS THE ONE, KILL HIM")
   
5. >>> db.session.add(note1)
   
6. >>> db.session.add(note2)
   
7. >>> db.session.add(note3)
   
8. >>> db.session.commit()
   
9. >>>

复制代码

* 模型类代表一整个表数据

* 模型类的实例对象代表一条记录

1. >>> note1
   
2. <Note 'remember Sammy Jankis'>
   
3. >>> note2
   
4. <Note 'SHAVE'>
   
5. >>> note3
   
6. <Note "DON'T BELIEVE HIS LIES, HE IS THE ONE, KILL HIM">

复制代码

* 模型类的属性代表一个字段

1. >>> note1.id
   
2. 4
   
3. >>> note1.body
   
4. 'remember Sammy Jankis'
   
5. >>> note2.id
   
6. 5
   
7. >>> note2.body
   
8. 'SHAVE'
   
9. >>>

复制代码

#### Read（数据库中读取数据）
总体格式：<模型类>.query.<过滤方法>.<查询方法>
* 常用的查询方法

sqlalchemy查询方法

sqlalchemy查询方法

##### 查找全部记录

1. >>> Note.query.all()
   
2. [<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
   
3. ELIEVE HIS LIES, HE IS THE ONE, KILL HIM">]
   
4. >>>

复制代码

##### 查询第一条记录

1. >>> Note.query.first()
   
2. <Note 'remember Sammy Jankis'>
   
3. >>>

复制代码

##### get 返回指定主键值

1. >>> note2 = Note.query.get(2)
   
2. >>> note2
   
3. <Note 'SHAVE'>
   
4. >>>

复制代码

##### count()返回记录的数量

1. >>> Note.query.count()
   
2. 6
   
3. >>>

复制代码

##### 常用的SQLALchemy过滤方法

sqlalchemy过滤方法

###### filter过滤（注意：下面的不是赋值号，而是等号）

1. >>> Note.query.filter(Note.body=="SHAVE").first()
   
2. <Note 'SHAVE'>
   
3. >>>

复制代码

* 查看sql语句

1. >>> print(Note.query.filter(Note.body=="SHAVE"))
   
2. SELECT note.id AS note_id, note.body AS note_body
   
3. FROM note
   
4. WHERE note.body = ?
   
5. >>>

复制代码

* 常用的查询操作符
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:

1. >>> Note.query.filter(Note.body.in_(["BELIEVE","Jankis","SHAVE"])).all()
   
2. [<Note 'SHAVE'>, <Note 'SHAVE'>]
   
3. >>> Note.query.filter(Note.body.in_(["BELIEVE","Jankis","SHAVE"])).first()
   
4. <Note 'SHAVE'>

复制代码

NOT IN:

1. >>> Note.query.filter(~Note.body.in_(["SHAVE"])).all()
   
2. [<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,
   
3. KILL HIM">]
   
4. >>> Note.query.filter(~Note.body.in_(["SHAVE"])).first()
   
5. <Note 'remember Sammy Jankis'>
   
6. >>>

复制代码

AND:

1. * 使用and_()
   
2. from sqlalchemy import and_
   
3. >>> Note.query.filter(and_(Note.body=="remember Sammy Jankis", Note.id=="1")).all()
   
4. [<Note 'remember Sammy Jankis'>]
   
5. >>> Note.query.filter(and_(Note.body=="remember Sammy Jankis", Note.id=="4")).all()
   
6. [<Note 'remember Sammy Jankis'>]

复制代码

1. 或在filter()中加入多个表达式，使用逗号分隔
   
2. >>> Note.query.filter(Note.body=="SHAVE", Note.id==2).all()
   
3. [<Note 'SHAVE'>]
   
4. >>> Note.query.filter(Note.body=="SHAVE", Note.id==5).all()
   
5. [<Note 'SHAVE'>]
   
6. >>>

复制代码

1. 或叠加调用多个filter()/filter_by方法
   
2. >>> Note.query.filter(Note.body=="SHAVE").filter(Note.id==2).all()
   
3. [<Note 'SHAVE'>]
   
4. >>> Note.query.filter(Note.body=="SHAVE").filter(Note.id==5).all()
   
5. [<Note 'SHAVE'>]
   
6. >>>

复制代码

OR:

1. >>> from sqlalchemy import or_
   
2. >>> Note.query.filter(or_(Note.body=="SHAVE", Note.id==2)).all()
   
3. [<Note 'SHAVE'>, <Note 'SHAVE'>]
   
4. >>> Note.query.filter(or_(Note.body=="SHAVE", Note.id==1)).all()
   
5. [<Note 'remember Sammy Jankis'>, <Note 'SHAVE'>, <Note 'SHAVE'>]
   
6. >>>

复制代码

##### filter_by()使用方式

1. >>> Note.query.filter_by(body="SHAVE").all()
   
2. [<Note 'SHAVE'>, <Note 'SHAVE'>]
   
3. >>>

复制代码

#### Update()（更新一条记录）
*更新一条记录，直接赋值给模型类的字段属性一个值

1. >>> note = Note.query.get(2)
   
2. >>> note.body
   
3. 'SHAVE'
   
4. >>> note.body = "SHAVE LEFT THIGH"
   
5. >>> db.session.commit()
   
6. >>>

复制代码

#### Delete() （删除一条记录）

1. >>> note = Note.query.get(2)
   
2. >>> db.session.delete(note)
   
3. >>> db.session.commit()
   
4. >>>

复制代码

Judie

笔记呢？

老八秘制

？？？？？