peewee子產品基本使用-ORM

github：  https://github.com/coleifer/peewee 官方文檔： http://docs.peewee-orm.com/en/latest/index.html# Defining models is similar to  Django  or SQLAlchemy

邏輯操作符

操作符	意思	示例
&	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'])

表達式轉換

Method  Meaning
.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.
.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 (case-sensitive).
.iregexp(exp)   Regular expression match (case-insensitive).
.bin_and(value) Binary AND.
.bin_or(value)  Binary OR.
.concat(other)  Concatenate two strings or objects using ||.
.distinct() Mark column for DISTINCT selection.
.collate(collation) Specify column with the given collation.
.cast(type) Cast the value of the column to the given type.      

in_(): IN
not_in(): NOT IN
regexp(): REGEXP
is_null(True/False): IS NULL or IS NOT NULL
contains(s): LIKE %s%
startswith(s): LIKE s%
endswith(s): LIKE %s
between(low, high): BETWEEN low AND high
concat(): ||      

舉例：

SELECT * FROM user WHERE username not like "%admin%"
# ~(User.username.contains('admin'))

SELECT * FROM user WHERE LENGTH(username)>45
# fn.length(User.username) > 45      

參考： 

http://docs.peewee-orm.com/en/latest/peewee/query_operators.html

調用sql函數

使用fn

query = (User
         .select(User.username, fn.COUNT(Tweet.id).alias('ct'))
         .join(Tweet, JOIN.LEFT_OUTER, on=(User.id == Tweet.user_id))
         .group_by(User.username)
         .order_by(fn.COUNT(Tweet.id).desc()))      

參考：

https://peewee.readthedocs.io/en/latest/peewee/api.html#fn

以下代碼參考官方示例

示例代碼：

# —*— coding: utf-8 —*—

from peewee import *
import datetime
from chinesename import chinesename

# py2解決編碼問題
import sys
reload(sys)
sys.setdefaultencoding("utf-8")

# 設定資料庫
db = SqliteDatabase("demo.db")

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

# 定義資料表
class User(BaseModel):
    name = CharField(unique=True)

    def __str__(self):
        return "[user] id: %d  name: %s"%(self.id, self.name)

class Tweet(BaseModel):
    user = ForeignKeyField(User, related_name ="tweets")
    message = TextField()
    created_date = DateTimeField(default=datetime.datetime.now)
    is_published = BooleanField(default=True)

    def __str__(self):
        return "[tweet] id: %d  name: %s" % (self.id, self.user.name)

# 建立資料表
db.connect()
db.create_tables([User, Tweet], safe=True)
db.close()

# 添加資料
def add_data():
    cn = chinesename.ChineseName()
    for i in range(100):
        user = User(name=cn.getName())
        user.save()

        User.create(name=cn.getName())

        Tweet.create(user=user, message="hello world")

# add_data()

print datetime.datetime.now()
print datetime.date.today()

# 查詢
ret = User.get(User.name=="沈從")
if ret: print ret


usernames = ["馬釀", "沈從"]
users = User.select().where(User.name.in_(usernames))
for user in users:
    print user

tweets = Tweet.select().where(Tweet.user.in_(users))
for tweet in tweets:
    print tweet


tweets = Tweet.select().join(User).where(User.name.in_(usernames))
for tweet in tweets:
    print tweet

count = (Tweet
         .select()
         .where(
            (Tweet.created_date >= datetime.date.today())&
            (Tweet.is_published == True))
         .count())

print count

# 分頁 page 3 (users 41-60)
users = User.select().order_by(User.name).paginate(3, 20)
for user in users:
    print user

# 更新
query = User.update(name="西門吹雪").where(User.id==1)
query.execute()

# 删除
query = User.delete().where(User.id==2)
query.execute()