PEEWEE基本使用

 

 Content 
Ⅰ  安装
Ⅱ  链接数据库
Ⅲ  建表
Ⅳ  增删改
Ⅴ  基础查询
Ⅵ  ForeignKey
Ⅷ  事务

 参考官方文档:http://docs.peewee-orm.com/en/latest/index.html

1. 安装

pip install peewee

 

2. 链接数据库

以mysql 为例(Peewee提供mysql,postgresql,sqllite)的支持

import peewee
settings = {'host': 'localhost', 'password': '', 'port': 3306, 'user': 'root'} db = peewee.MySQLDatabase("test",**settings)

 

3. 建表

3.1 表关系

class Person(Model):
    name = CharField(verbose_name='姓名', max_length=10, null=False, index=True)
    passwd = CharField(verbose_name='密码', max_length=20, null=False, default='123456')
    email = CharField(verbose_name='邮件', max_length=50, null=True, unique=True)
    gender = IntegerField(verbose_name='姓别', null=False, default=1)
    birthday = DateField(verbose_name='生日', null=True, default=None)
    is_admin = BooleanField(verbose_name='是否是管理员', default=True)
    class Meta:
        database = db  # 这里是数据库链接,为了方便建立多个表,可以把这个部分提炼出来形成一个新的类
    
table_name = 'persons' # 这里可以自定义表名


# 例如:
    class BaseModel(Model):    class Meta:    database = db class Person(BaseModel):    name = CharField()

 

全部数据类型
Field TypeSqlitePostgresqlMySQL
IntegerField integer integer integer
BigIntegerField integer bigint bigint
SmallIntegerField integer smallint smallint
AutoField integer serial integer
FloatField real real real
DoubleField real double precision double precision
DecimalField decimal numeric numeric
CharField varchar varchar varchar
FixedCharField char char char
TextField text text longtext
BlobField blob bytea blob
BitField integer bigint bigint
BigBitField blob bytea blob
UUIDField text uuid varchar(40)
DateTimeField datetime timestamp datetime
DateField date date date
TimeField time time time
TimestampField integer integer integer
IPField integer bigint bigint
BooleanField integer boolean bool
BareField untyped not supported not supported
ForeignKeyField integer integer integer
null = False – 可否为空
index = False – index索引
unique = False – unique索引
column_name = None – string representing the underlying column to use if different, useful for legacy databases
default = None – 默认值,如果callable, 会调用生成!
primary_key = False – 主键
constraints = None - a list of one or more constraints, e.g. [Check('price > 0')]
sequence = None – sequence to populate field (if backend supports it)
collation = None – collation to use for ordering the field / index
unindexed = False – indicate field on virtual table should be unindexed (SQLite-only)
choices = None – an optional iterable containing 2-tuples of value, display
help_text = None – string representing any helpful text for this field
verbose_name = None – string representing the “user-friendly” name of this field
所有属性

 

3.2 链接数据库

db.is_closed()  # 判断数据库是不是链接
db.connect()   # 数据库链接

 

3.3 建库

db.create_tables([Person,]) #建库

#   如果数据表已经存在,执行create_table的时候,将会抛出异常。

 

3.4 主键和约束

class Person(Model):
    first = CharField()
    last = CharField()

    class Meta:
        primary_key = CompositeKey('first', 'last')

class Pet(Model):
    owner_first = CharField()
    owner_last = CharField()
    pet_name = CharField()

    class Meta:
        constraints = [SQL('FOREIGN KEY(owner_first, owner_last) '
                           'REFERENCES person(first, last)')]
主键和约束
class BlogToTag(Model):
    """A simple "through" table for many-to-many relationship."""
    blog = ForeignKeyField(Blog)
    tag = ForeignKeyField(Tag)

    class Meta:
        primary_key = CompositeKey('blog', 'tag')
复合主键
User._meta.auto_increment = False # turn off auto incrementing IDs
关自增

 

4. 插&改&删

4.1 插

p = Person.create(name='master')
####### 或 #########
p = Person(name="master")
p.save()

data = [
    {'facid': 9, 'name': 'Spa', 'membercost': 20, 'guestcost': 30,
     'initialoutlay': 100000, 'monthlymaintenance': 800},
    {'facid': 10, 'name': 'Squash Court 2', 'membercost': 3.5,
     'guestcost': 17.5, 'initialoutlay': 5000, 'monthlymaintenance': 80}]
query = Facility.insert_many(data) # 插入了多个

with db.atomic():  # 一次链接
    for data_dict in data_source:
        MyModel.create(**data_dict)
 
User.insert(username='Mickey').execute()  # >>> 返回主键

 

# insert_from 是指从一个表查数据快速差到另一个表
query = (TweetArchive
         .insert_from(
             Tweet.select(Tweet.user, Tweet.message),
             fields=[Tweet.user, Tweet.message])
         .execute())
insert_from

 

4.2 改

p.email = "qq@qq.com"  # p 为记录对象
p.save()  # 会调用update  但是不要这么写!

query = (Facility
         .update(membercost=6, guestcost=30)
         .where(Facility.name.startswith('Tennis'))) 
query.execute()  # 元子操作

 

4.3 注意

插入的时候,如果字段设置了default值,则会按照default指定的值插入,如果没有指定,同时字段可以为null,则数据库自动初始化值为null,如果字段不能为null,则数据库自动初始化为其零值。 如果字段为非Null,最好设置default值,同时数据库schema也设置其default值,如果字段为可以为null,那么初始值就设置为null即可。

 

4.4 数据删除

p.delete_instance()
query = Member.delete().where(Member.memid == 37)


# DELETE FROM members WHERE NOT EXISTS (SELECT * FROM bookings WHERE bookings.memid = members.memid);
subq = Booking.select().where(Booking.member == Member.memid)
query = Member.delete().where(~fn.EXISTS(subq)) # 同样这样的操作是被推荐的

 

 

5. 基础查询

  基本和sql语句相同

User.get(User.id == 1)

User.get_by_id(1)  # Same as above.

User[1]  # Also same as above.
g = Person.select().where(Person.name == 'Grandma L.').get()   # where是查询一个集合, select是查询字段

g = Person.get(Person.name == 'fff.')   # get是得到第一个

g = Person.select().where(Person.age > 23).get()
# select 代表sql语句中select后面的语句表示要展示的字段 # where 代表where条件语句 得到一个数据集合,用for循环遍历 # get()代表找第一个

 5.1 复合条件

query1 = Person.select().where((Person.name == "fff0") | (Person.name == "sss1"))

query2 = Person.select().where((Person.name == "fff") & (Person.is_relative == True))
==    x equals y
<    x is less than y
<=    x is less than or equal to y
>    x is greater than y
>=    x is greater than or equal to y
!=    x is not equal to y
<<    x IN y, where y is a list or query
>>    x IS y, where y is None/NULL
%    x LIKE y where y may contain wildcards
**    x ILIKE y where y may contain wildcards
^    x XOR y
~    Unary negation (e.g., NOT x)
操作符

 

5.2 模糊查询

#SELECT * FROM person WHERE name ILIKE '%tennis%';

Person.select().where(Person.name ** "%fff%").get()
######### OR ##############
query = Facility.select().where(Facility.name.contains('tennis'))

 5.3 In 查询

# SELECT * FROM facilities WHERE facid IN (1, 5);
query = Facility.select().where(Facility.facid.in_([1, 5]))
person, created = Person.get_or_create(
    first_name=first_name,
    last_name=last_name,
    defaults={'dob': dob, 'favorite_color': 'green'})

# defaults dictionary, which will be used to populate values on newly-created instances.
get_or_create
query = User.select().dicts()
for row in query:
    print(row)
# row 是什么字典
字典展示(还可以tuples(),namedtuples(),objects())

 

5.4 Order by; Limit; Distinct; Group by; Having

query = (Person.select(Person.name).order_by(Person.name).limit(10).distinct())  # 几乎和sql一模一样
Person.select().order_by(Person.birthday.desc())  # 日期排序
query = (Booking
         .select(Booking.facid, fn.SUM(Booking.slots)) 
         .group_by(Booking.facid)   # group_by
         .order_by(Booking.facid))
query = (Booking
         .select(Booking.facility, fn.SUM(Booking.slots))
         .group_by(Booking.facility)
         .having(fn.SUM(Booking.slots) > 1000)   # having
         .order_by(Booking.facility))
 
Tweet.select().order_by(-Tweet.created_date) 
# Similarly you can use "+" to indicate ascending order, though ascending

 

5.5 聚合函数 

# SELECT MAX(birthday) FROM person;
query = Person.select(fn.MAX(Person.birthday))

# SELECT name, is_relative FROM person WHERE birthday = (SELECT MAX(birthday) FROM person);
MemberAlias = Member.alias()  # 如果一个查询中用了两个表,需要这个Alias作为影子
subq = MemberAlias.select(fn.MAX(MemberAlias.joindate))
query = (Member.select(Person.is_relative, Person.name, ).where(Person.birthday == subq))

 

5.6 分页&计数

for tweet in Tweet.select().order_by(Tweet.id).paginate(2, 10):
    print(tweet.message)

# paginate两个参数:page_number 和 items_per_page
分页
# 返回查到了多少条记录
Tweet.select().where(Tweet.id > 50).count()
count

 

5.7 SQL

query = MyModel.raw('SELECT * FROM my_table WHERE data = %s', user_data)
query = MyModel.select().where(SQL('Some SQL expression %s' % user_data))
# sql 注入: ensure that any user-defined data is passed in as a query parameter and not part of the actual SQL query
原生sql

 

6. ForeignKey

6.1 表关系

class Pet(peewee.Model):
    name = peewee.CharField()
    owner = peewee.ForeignKeyField(Person,related_name="pets",backref="petties")   
  # backref是反查的字段,如果有related_name用related_name反查,如果没有直接用petties反查 e.g. [
i.name for i in Person.get(name="aaa").petties]

class Meta: database = db
class Category(Model):
    name = CharField()
    parent = ForeignKeyField('self', null=True, backref='children')
   # 注意自关联永远是null = True
自关联

 

6.2 插

g2 = Person.get(tablesinfo.Person.is_relative == False)
d2 = Pet.create(name="dog2",owner=g2)

 

6.3 正反查

#正查
dog1 = Pet.get(name="dog1")
dog1.owner.name


# 反查
aaa = Person.get(name="aaa").pets  # pets为related_name字段,如果没写用backref字段
for a in aaa:
    print(i.name)

 

6.4 连表查询

g1 = Person.select().join(Pet).where(Pet.name == "dog2")

 

6.5 复杂连表

# SELECT DISTINCT m.firstname, m.surname FROM members AS m2
INNER JOIN members AS m ON (m.memid = m2.recommendedby)
ORDER BY m.surname, m.firstname;

MA = Member.alias()
query = (Member
         .select(Member.firstname, Member.surname)
         .join(MA, on=(MA.recommendedby == Member.memid))  # join中用on表示链接方法
         .order_by(Member.surname, Member.firstname))

 

6.6 连多表

User.select().join(Tweet).join(Comment)

Artist.select().join(Album).switch(Artist).join(Genre) # 如果连一表多次
连多表

 

6.7 多对多

class BaseModel(Model):
    class Meta:
        database = db

class Student(BaseModel):
    name = CharField()

class Course(BaseModel):
    name = CharField()
    students = ManyToManyField(Student, backref='courses')
StudentCourse = Course.students.get_through_model()  # 中间表

 

huey = Student.get(Student.name == 'Huey')

# We can add all the objects represented by a query.
english_courses = Course.select().where(
    Course.name.contains('english'))
huey.courses.add(english_courses)
add
huey = Student.get(Student.name == 'Huey')
for course in huey.courses.order_by(Course.name):
    print(course.name)
huey.courses.remove(Course.select().where(Course.name.startswith('CS')))
engl_101.students.remove(huey)
remove

 

7 事务

with db.atomic() as transaction:  # Opens new transaction.
    try:
        save_some_objects()
    except ErrorSavingData:
        # Because this block of code is wrapped with "atomic", a
        # new transaction will begin automatically after the call
        # to rollback().
        transaction.rollback()
        error_saving = True
atomic和rollback
db = SqliteDatabase(':memory:')

with db.atomic() as txn:
    # This is the outer-most level, so this block corresponds to
    # a transaction.
    User.create(username='charlie')

    with db.atomic() as nested_txn:
        # This block corresponds to a savepoint.
        User.create(username='huey')

        # This will roll back the above create() query.
        nested_txn.rollback()

    User.create(username='mickey')

# When the block ends, the transaction is committed (assuming no error
# occurs). At that point there will be two users, "charlie" and "mickey".
atomic甚至可以多层嵌套
@db.atomic()
def create_user(username):
    # This statement will run in a transaction. If the caller is already
    # running in an `atomic` block, then a savepoint will be used instead.
    return User.create(username=username)

create_user('charlie')
装饰器模式
# 纯手动用commit()提交,用rollback回滚
with db.transaction() as txn:
    User.create(username='mickey')
    txn.commit()  # Changes are saved and a new transaction begins.
    User.create(username='huey')

    # Roll back. "huey" will not be saved, but since "mickey" was already
    # committed, that row will remain in the database.
    txn.rollback()



# 半自动用savepoint上下文管理
with db.transaction() as txn:
    with db.savepoint() as sp:
        User.create(username='mickey')

    with db.savepoint() as sp2:
        User.create(username='zaizee')
        sp2.rollback()  # "zaizee" will not be saved, but "mickey" will be.
手动、半自动

 

8 其他

import uuid

class UUIDField(Field):
    field_type = 'uuid'

    def db_value(self, value):
        return str(value) # convert UUID to str

    def python_value(self, value):
        return uuid.UUID(value) # convert str to UUID
自定义类型!

 

Person._meta.fields   # 用访问_meta访问定义的meta
Person._meta.primary_key   
Person._meta.database

# 属性有:database, table_name, table_function, indexes, primary_key, constraints, schema, only_save_dirty, options, table_alias, depends_on, without_rowid
数据表信息的访问

 

class Article(Model):
    name = TextField()
    timestamp = TimestampField()
    status = IntegerField()
    flags = IntegerField()

# Add an index on "name" and "timestamp" columns.
Article.add_index(Article.name, Article.timestamp)

# Add a partial index on name and timestamp where status = 1.
Article.add_index(Article.name, Article.timestamp,
                  where=(Article.status == 1))

# Create a unique index on timestamp desc, status & 4.
idx = Article.index(
    Article.timestamp.desc(),
    Article.flags.bin_and(4),
    unique=True)
Article.add_index(idx)
索引增加

 

事务autocommit模式(不推荐)

 

Employee.select(fn.Min(Employee.salary), fn.Max(Employee.salary) ).scalar(as_tuple=True)
# You can retrieve multiple scalar values by passing as_tuple=True


# scalar 表示结果的第一行第一列
scalar

 

 

 

注:原生sql,推荐 https://pypi.python.org/pypi/records/