peewee

models and fields

不指定主键自动产生一个主键

meta信息会自动继承到所有子类

Once the class is defined, you should not access ModelClass.Meta, but instead use

The ModelOptions class (meta class) implements several methods which may be of use for retrieving model metadata (such as lists of fields, foreign key relationships, and more).

外键访问

ForeignKeyField allows for a backreferencing property to be bound to the target model. Implicitly, this property will be named classname_set, where classname is the lowercase name of the class, but can be overridden via the parameter related_name:

class Message(Model):
    from_user = ForeignKeyField(User)
    to_user = ForeignKeyField(User, related_name='received_messages')
    text = TextField()

for message in some_user.message_set:
    # We are iterating over all Messages whose from_user is some_user.
    print message

for message in some_user.received_messages:
    # We are iterating over all Messages whose to_user is some_user
    print message

索引

单列索引
unique 字段自动建立索引
index=true 显示建立索引

多列索引
meta信息里面填写

class Transaction(Model):
    from_acct = CharField()
    to_acct = CharField()
    amount = DecimalField()
    date = DateTimeField()

    class Meta:
        indexes = (
            # create a unique on from/to/date
            (('from_acct', 'to_acct', 'date'), True),

            # create a non-unique on from/to
            (('from_acct', 'to_acct'), False),
        )

约束

单列约束

class Product(Model):
    name = CharField(unique=True)
    price = DecimalField(constraints=[Check('price < 10000')])
    created = DateTimeField(
        constraints=[SQL("DEFAULT (datetime('now'))")])

表约束

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)')]

非自增主键

If you would like use a non-integer primary key (which I generally don’t recommend), you can specify primary_key=True when creating a field. When you wish to create a new instance for a model using a non-autoincrementing primary key, you need to be sure you save() specifying force_insert=True.

#

可以在meta里面关闭自增.设置多个列的主键.不设置主键(会导致很多问题),

反向外键建立的时候需要加self

Self-referential foreign-keys should always be null=True.

class Category(Model):
    name = CharField()
    parent = ForeignKeyField('self', null=True, related_name='children')

建立互相引用的外键的技巧

DeferredRelation 这个东西可以帮助

Querying

创建对象

对于外键对象 可以直接复制外键对象的id 也可以是外键对象

1

If you simply wish to insert data and do not need to create a model instance, you can use Model.insert():

User.insert(username=’Mickey’).execute()

Model.insert_many负责批量插入记录

3

If the data you would like to bulk load is stored in another table, you can also create INSERT queries whose source is a SELECT query. Use the Model.insert_from() method:

query = (TweetArchive
.insert_from(
fields=[Tweet.user, Tweet.message],
query=Tweet.select(Tweet.user, Tweet.message))
.execute())

update

>>> today = datetime.today()
>>> query = Tweet.update(is_published=True).where(Tweet.creation_date < today)
>>> query.execute()  # Returns the number of rows that were updated.

update 应该使用原子查询..就是说尽量在一个查询里面进行数据变动.不然在多线程下产生问题

update可以使用子查询来提供数据变更

Deleting records

Model.delete_instance()
Model.delete()
DeleteQuery

get 得到一个记录

Create or get

Peewee has two methods for performing “get/create” type operations:

Model.create_or_get(),
which will attempt to create a new row. If an IntegrityError occurs indicating the violation of a constraint, then Peewee will attempt to get the object instead.

Model.get_or_create(),
which first attempts to retrieve the matching row. Failing that, a new row will be created.

window function

fn.over()

转成tuples / dictionaries

直接使用tuples 和dicts

returning

UpdateQuery.returning()
InsertQuery.returning()
DeleteQuery.returning()

把影响的行返回

operators

Query operators
The following types of comparisons are supported by peewee:

Comparison  Meaning
==  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
~  Negation

method

Because I ran out of operators to override, there are some additional query operations available as methods:

Method  Meaning
.contains(substr)   Wild-card search for substring.
.startswith(prefix) Search for values beginning with prefix.
.endswith(suffix)   Search for values ending with suffix.
.between(low, high) Search for values between low and high.
.regexp(exp)    Regular expression match.
.bin_and(value) Binary AND.
.bin_or(value)  Binary OR.
.in_(value) IN lookup (identical to <<).
.not_in(value)  NOT IN lookup.
.is_null(is_null)   IS NULL or IS NOT NULL. Accepts boolean param.
.concat(other)  Concatenate two strings using ||.

logical 这里要用括号括起来

Operator    Meaning Example
&   AND (User.is_active == True) & (User.is_admin == True)
| (pipe)    OR  (User.is_admin) | (User.is_superuser)
~  NOT (unary negation)    ~(User.username << ['foo', 'bar', 'baz'])

like glob
也是一种匹配

自定义操作符

Here is how you might add support for modulo in SQLite:

from peewee import *
from peewee import Expression # the building block for expressions

OP['MOD'] = 'mod'

def mod(lhs, rhs):
    return Expression(lhs, OP.MOD, rhs)

SqliteDatabase.register_ops({OP.MOD: '%'})
Now you can use these custom operators to build richer queries:

# Users with even ids.
User.select().where(mod(User.id, 2) == 0)

join

By default peewee will use an INNER join, but you can use LEFT OUTER, RIGHT OUTER, FULL, or CROSS joins as well:

如果有多个外键对一个model

class Relationship(BaseModel):
    from_user = ForeignKeyField(User, related_name='relationships')
    to_user = ForeignKeyField(User, related_name='related_to')

    class Meta:
        indexes = (
            # Specify a unique multi-column index on from/to-user.
            (('from_user', 'to_user'), True),
        )
Since there are two foreign keys to User, we should always specify which field we are using in a join.

For example, to determine which users I am following, I would write:

(User
.select()
.join(Relationship, on=Relationship.to_user)
.where(Relationship.from_user == charlie))

任意的join,不存在外键的join

user_log = (User
            .select(User, ActivityLog)
            .join(
                ActivityLog,
                on=(User.id == ActivityLog.object_id).alias('log'))
            .where(
                (ActivityLog.activity_type == 'user_activity') &
                (User.username == 'charlie')))

for user in user_log:
    print user.username, user.log.description

多对多关系

For more examples, see:

ManyToManyField.add()
ManyToManyField.remove()
ManyToManyField.clear()
ManyToManyField.get_through_model()

事务

atomic 一般是推荐的.这里是支持嵌套的.

显示的使用transaction是不太推荐的.因为不支持嵌套事务