Flask操作数据库

发表于 分类于 阅读次数：

增

1
2
3
4
5
6
7
student = Student(id=id, name=name, age=age)
try:
    db.session.add(student)
    db.session.commit()
except:
	# 事务回滚
    db.session.rollback()

删

1
2
db.session.delete(user)
db.session.commit()

查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
# 查询所有⽤户数据
user_list = Wdtest.query.all()

# 查询⽤户数量
user_list_num = Wdtest.query.count()

# 查询第1个⽤户
user= Wdtest.query.first()

# 查询id为3的⽤户[3种⽅式]
user = Wdtest.query.get(3)  # 根据主键查询
user_list = Wdtest.query.filter_by(id=3).all()  # 以关键字实参形式进行匹配字段
user_list = Wdtest.query.filter(Wdtest.id == 3).all()  # 以恒等式形式匹配字段

# 查询名字结尾字符为g的所有⽤户
Wdtest.query.filter(Wdtest.name.endswith('g')).all()

# 查询名字包含‘wa'的所有用户
user_list = Wdtest.query.filter(Wdtest.name.contains('wa')).all()

# 模糊查询
user_list = Wdtest.query.filter(Wdtest.name.like('%a%')).all()

# 与
user_list = Wdtest.query.filter(Wdtest.name.startswith('wa'), Wdtest.age == 20).all()
from sqlalchemy import and_
user_list = Wdtest.query.filter(and_(Wdtest.name.startswith('wa'), Wdtest.age == 20)).all()

# 或
from sqlalchemy import or_
user_list = Wdtest.query.filter(or_(Wdtest.name.startswith('wa'), Wdtest.age == 20)).all()

# 非
from sqlalchemy import not_
user_list = Wdtest.query.filter(not_(Wdtest.name == 'wade')).all()
user_list = Wdtest.query.filter(Wdtest.name != 'wade').all()

# in 条件查询
user_list = Wdtest.query.filter(Wdtest.id.in_(['97124f50-0208-11ea-a66c-04ea56212bdf', '3'])).all()

# 所有⽤户先按年龄从⼩到⼤, 再按id从⼤到⼩排序, 取前5个
user_list = Wdtest.query.order_by(Wdtest.age, Wdtest.id.desc()).limit(5).all()

# 分⻚查询, 每⻚3个, 查询第2⻚的数据
pn = Wdtest.query.paginate(2,3)
print(pn.pages)
print(pn.page)
print(pn.items)

改

1
2
3
4
5
6
7
user = User.query.first()
user.name = 'silence'
db.session.commit()
User.query.first()

# 或者
User.query.filter_by(name='zhang').update({'name':'silence'})