增
单条增加
方法1
StudentsInfo.create(student_name='amos', student_no=880)
方法2
StudentsInfo.insert(student_name='lucy', student_no=881).execute()
等同于insert into student_info (student_name, student_no) values ('lee',882)
多条增加
方法1
data_source = [
{'student_name': 'lance', 'student_no': 883},
{'student_name': 'john', 'student_no': 884},
# ...
]
for data_dict in data_source:
StudentsInfo.create(**data_dict)
方法2（这个方法会快很多）
data_source = [
{'student_name': 'jason', 'student_no': 886},
{'student_name': 'tom', 'student_no': 887},
# ...
]
with database.atomic():
for data_dict in data_source:
StudentsInfo.create(**data_dict)
方法3（最快的方法）
data_source = [
{'student_name': 'hom', 'student_no': 888},
{'student_name': 'baby', 'student_no': 889},
# ...
]
with database.atomic():
StudentsInfo.insert_many(data_source).execute()
如果数据量太大或许你需要分开处理，比如一次处理100条：
data_source = [
{'student_name': 'hom', 'student_no': 888},
{'student_name': 'baby', 'student_no': 889},
# ...
]
with database.atomic():
**for** idx **in** range(0,len(data_source),100):
StudentsInfo.insert_many(data_source[idx:idx+100]).execute()
删
单条删除
st = StudentsInfo.get(student_name='hom')
st.delete_instance()
等同于DELETE from student_info where student_name = 'hom'
多条删除
StudentsInfo.delete().where(StudentsInfo.student_no < 883).execute()
等同于DELETE from student_info where student_no < 883
改
方法1指定数据
StudentsInfo.update(student_no=890).where(StudentsInfo.student_name == 'baby').execute()
方法2依据原有数据自动更新
StudentsInfo.update(student_no=StudentsInfo.student_no + 1).where \
(StudentsInfo.student_name == 'baby').execute()
方法3 多字段更新
StudentsInfo.update(student_no=890,student_name='lady').where \
(StudentsInfo.student_name == 'baby').execute()
查
- 一般查询
st1 = StudentsInfo.select()
查询所有的记录并获取他们
for i in st1:
print i.student_no, i.student_name
- 单条查询
st2 = StudentsInfo.get(StudentsInfo.student_no == 883)
print st2.student_no, st2.student_name
对比1和2个区别
先获取他们的类型
print type(st1) == > <class 'peewee.SelectQuery'>
Print type(st2) == > <class 'createDB.StudentsInfo'>
st1是’SelectQuery'类型需要使用for循环逐条获取，而st2本身就是一个实例的对象可以直接获取它的属性
- 查询部分字段
st3 = StudentsInfo.select(StudentsInfo.student_no)
- 有条件查询
st4 = StudentsInfo.select().where(StudentsInfo.student_no == 883)
- 随机查询
需要先引入fn
from peewee import fn
st5 = StudentsInfo.select().order_by(fn.Random()).limit(2)
- 排序查询
正序
st6 = StudentsInfo.select().order_by(StudentsInfo.student_no.asc())
反序
st6 = StudentsInfo.select().order_by(StudentsInfo.student_no.desc())
- Not in组合查询
简单举例，现有学生信息表student_info学生姓名student_name和学号student_no，学生成绩表score_table学号student_no和分数score
st7 = StudentsInfo.select(StudentsInfo.student_no).where(StudentsInfo.student_no > 880)
sc = StudentsScore.select().where(StudentsScore.student_no.not_in(st7))
- 模糊查询
比如想要查询学生名字包含’ba’的学生以及学号
%符号就相当于sql里的like
st8 = StudentsInfo.select().where(StudentsInfo.student_name % '%ba%')
for i in st8:
print i.student_no,i.student_name
网友评论