Python ORM框架Peewee初探 增删改查

2018/12 29 08:12

单条增加

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

 

  1. 一般查询

st1 = StudentsInfo.select()

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

for i in st1:

print i.student_no, i.student_name

 

  1. 单条查询

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本身就是一个实例的对象可以直接获取它的属性

 

  1. 查询部分字段

st3 = StudentsInfo.select(StudentsInfo.student_no)

 

  1. 有条件查询

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

 

  1. 随机查询

需要先引入fn

from peewee import fn

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

 

  1. 排序查询

正序

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

反序

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

 

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

  1. 模糊查询

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

%符号就相当于sql里的like

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

for i in st8:

print i.student_no,i.student_name

纯手打,多谢支持。

--转载请注明: http://91o.cc/python-orm%e6%a1%86%e6%9e%b6peewee%e5%88%9d%e6%8e%a2-%e5%a2%9e%e5%88%a0%e6%94%b9%e6%9f%a5/