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

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

# insert_from 是指从一个表查数据快速差到另一个表

query = (TweetArchive

         .insert_from(

             Tweet.select(Tweet.user, Tweet.message),

             fields=[Tweet.user, Tweet.message])

         .execute())

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

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.

query = User.select().dicts()

for row in query:

    print(row)

# row 是什么字典

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

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

class Category(Model):

    name = CharField()

    parent = ForeignKeyField('self', null=True, backref='children')

   # 注意自关联永远是null = True

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

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

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)

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)

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

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

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

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)

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 表示结果的第一行第一列