1,peewee跨表操作
1,查询tweet的所有
tweets = (Tweet .select(Tweet, User) .join(User) .order_by(Tweet.created_date.desc())) for tweet in tweets: print(tweet.user.username, tweet.message)
2,查询倒叙的tweet
tweets = Tweet.select().order_by(Tweet.created_date.desc()) for tweet in tweets: # WARNING: an additional query will be issued for EACH tweet # to fetch the associated User data. print(tweet.user.username, tweet.message)
3,反向调用
class Message(Model):
from_user = ForeignKeyField(User, backref='outbox') to_user = ForeignKeyField(User, backref='inbox') text = TextField() for message in some_user.outbox: # We are iterating over all Messages whose from_user is some_user. print(message) for message in some_user.inbox: # We are iterating over all Messages whose to_user is some_user print(message)
2,字段类型解析
1,datetime,time,date的field关键字
DateField has properties for: year month day TimeField has properties for: hour minute second DateTimeField has all of the above.
关于时间的查询
# Get the current time.
now = datetime.datetime.now() # Get days that have events for the current month. Event.select(Event.event_date.day.alias('day')).where( (Event.event_date.year == now.year) & (Event.event_date.month == now.month))
BitField
class Post(Model):
content i= TextField() flags = BitField() is_favorite = flags.flag(1) is_sticky = flags.flag(2) is_minimized = flags.flag(4) is_deleted = flags.flag(8)
>>> p = Post()
>>> p.is_sticky = True >>> p.is_minimized = True >>> print(p.flags) # Prints 4 | 2 --> "6" 6 >>> p.is_favorite False >>> p.is_sticky True
# Generates a WHERE clause that looks like: # WHERE (post.flags & 1 != 0) favorites = Post.select().where(Post.is_favorite) # Query for sticky + favorite posts: sticky_faves = Post.select().where(Post.is_sticky & Post.is_favorite)
还可以对字段增加参数
class Bitmap(Model):
data = BigBitField() bitmap = Bitmap() # Sets the ith bit, e.g. the 1st bit, the 11th bit, the 63rd, etc. bits_to_set = (1, 11, 63, 31, 55, 48, 100, 99) for bit_idx in bits_to_set: bitmap.data.set_bit(bit_idx) # We can test whether a bit is set using "is_set": assert bitmap.data.is_set(11) assert not bitmap.data.is_set(12)
在Meta中,我们可以通过Meta获取相关的字段信息
from peewee import *
contacts_db = SqliteDatabase('contacts.db') class Person(Model): name = CharField() class Meta: database = contacts_db
>>> Person._meta.fields {'id': <peewee.PrimaryKeyField object at 0x7f51a2e92750>, 'name': <peewee.CharField object at 0x7f51a2f0a510>} >>> Person._meta.primary_key <peewee.PrimaryKeyField object at 0x7f51a2e92750> >>> Person._meta.database <peewee.SqliteDatabase object at 0x7f519bff6dd0>
Meta中的字段
database database for model yes table_name name of the table to store data no table_function function to generate table name dynamically yes indexes a list of fields to index yes primary_key a CompositeKey instance yes constraints a list of table constraints yes schema the database schema for the model yes only_save_dirty when calling model.save(), only save dirty fields yes options dictionary of options for create table extensions yes table_settings list of setting strings to go after close parentheses yes temporary indicate temporary table yes legacy_table_names use legacy table name generation (enabled by default) yes table_alias an alias to use for the table in queries no depends_on indicate this table depends on another for creation no without_rowid indicate table should not have rowid (SQLite only) no
联合唯一
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')
3,字段的约束constraints
class Product(Model): name = CharField(unique=True) price = DecimalField(constraints=[Check('price < 10000')]) created = DateTimeField( constraints=[SQL("DEFAULT (datetime('now'))")])
4,定制唯一索引
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), )
5,定义主键是自己,自关联
class Category(Model): name = CharField() parent = ForeignKeyField('self', null=True, backref='children')
自关联查询,对自己定义别名
Parent = Category.alias() GrandParent = Category.alias() query = (Category .select(Category, Parent) .join(Parent, on=(Category.parent == Parent.id)) .join(GrandParent, on=(Parent.parent == GrandParent.id)) .where(GrandParent.name == 'some category') .order_by(Category.name))
6,互相关联
class User(Model): username = CharField() # Tweet has not been defined yet so use the deferred reference. favorite_tweet = DeferredForeignKey('Tweet', null=True) class Tweet(Model): message = TextField() user = ForeignKeyField(User, backref='tweets') # Now that Tweet is defined, "favorite_tweet" has been converted into # a ForeignKeyField. print(User.favorite_tweet) # <ForeignKeyField: "user"."favorite_tweet">