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是不太推荐的.因为不支持嵌套事务

### 如何使用 Peewee ORM 执行 SELECT 查询 Peewee 是一个轻量级的 Python ORM 框架,支持多种数据库操作。以下是关于如何使用 Peewee ORM 执行 `SELECT` 查询的详细说明。 #### 基本查询 在 Peewee 中,执行 `SELECT` 查询通常通过模型的 `select()` 方法实现。以下是一个简单的查询示例: ```python from peewee import * # 定义数据库连接 db = SqliteDatabase(':memory:') # 定义模型 class Person(Model): name = CharField() age = IntegerField() class Meta: database = db # 创建表 db.connect() db.create_tables([Person]) # 插入数据 Person.create(name="Alice", age=25) Person.create(name="Bob", age=30) # 执行查询 query = Person.select() # 查询所有记录 for person in query: print(f"Name: {person.name}, Age: {person.age}") # 输出每条记录的信息[^1] ``` #### 条件查询 可以使用 `where()` 方法添加查询条件。例如,查询年龄大于 25 的记录: ```python query = Person.select().where(Person.age > 25) for person in query: print(f"Name: {person.name}, Age: {person.age}") # 输出符合条件的记录[^1] ``` #### 聚合查询 Peewee 支持聚合函数,例如 `COUNT`、`SUM` 等。以下是一个统计用户发布推文数量的示例: ```python query = (Person .select(Person.name, fn.COUNT(Person.id).alias('count')) .group_by(Person.name) .order_by(fn.COUNT(Person.id).desc())) for person in query: print(f"Name: {person.name}, Count: {person.count}") # 输出每个用户的记录数[^2] ``` #### 连接查询 Peewee 支持通过外键进行连接查询。以下是一个连接查询的示例: ```python class Tweet(Model): user = ForeignKeyField(Person, backref='tweets') message = TextField() class Meta: database = db db.create_tables([Tweet]) Tweet.create(user=Person.get(Person.name == "Alice"), message="Hello World") query = (Person .select(Person.name, Tweet.message) .join(Tweet, JOIN.LEFT_OUTER) .where(Person.name == "Alice")) for person in query: print(f"Name: {person.name}, Message: {person.tweets[0].message}") # 输出连接结果[^4] ``` #### 分页查询 可以通过 `limit()` 和 `offset()` 方法实现分页查询。例如,查询前两条记录: ```python query = Person.select().limit(2) for person in query: print(f"Name: {person.name}, Age: {person.age}") # 输出前两条记录[^1] ``` #### 排序查询 可以使用 `order_by()` 方法对查询结果进行排序。例如,按年龄降序排列: ```python query = Person.select().order_by(Person.age.desc()) for person in query: print(f"Name: {person.name}, Age: {person.age}") # 输出按年龄降序排列的结果[^1] ``` ### 注意事项 - 在执行查询之前,确保数据库连接已建立并创建了相关表。 - 使用 `select()` 方法时,可以选择需要的字段以提高查询效率。 - 对于复杂的查询,建议结合 `where()`、`join()`、`group_by()` 等方法实现所需功能[^2]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值