python增删改查的框架_Python ORM框架Peewee初探【三】增删改查

增

单条增加

方法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():

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

查

1.一般查询

st1 = StudentsInfo.select()

查询所有的记录并获取他们

for i in st1:

print i.student_no, i.student_name

2.单条查询

st2 = StudentsInfo.get(StudentsInfo.student_no == 883)

print st2.student_no, st2.student_name

对比1和2个区别

先获取他们的类型

print type(st1) == >

Print type(st2) == >

st1是’SelectQuery'类型需要使用for循环逐条获取，而st2本身就是一个实例的对象可以直接获取它的属性

3.查询部分字段

st3 = StudentsInfo.select(StudentsInfo.student_no)

4.有条件查询

st4 = StudentsInfo.select().where(StudentsInfo.student_no == 883)

5.随机查询

需要先引入fn

from peewee import fn

st5 = StudentsInfo.select().order_by(fn.Random()).limit(2)

6.排序查询

正序

st6 = StudentsInfo.select().order_by(StudentsInfo.student_no.asc())

反序

st6 = StudentsInfo.select().order_by(StudentsInfo.student_no.desc())

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

8.模糊查询

比如想要查询学生名字包含’ba’的学生以及学号

%符号就相当于sql里的like

st8 = StudentsInfo.select().where(StudentsInfo.student_name % '%ba%')

for i in st8:

print i.student_no,i.student_name

纯手打，多谢支持。