Python：peewee模块ORM基本使用和常用查询示例

Defining models is similar to Django or SQLAlchemy

译文：定义模型类似于Django或SQLAlchemy

相关文档

• github： https://github.com/coleifer/peewee
• 官方文档：http://docs.peewee-orm.com/
• pypi https://pypi.org/project/peewee/

安装

pip install peewee

连接数据库

# sqlite
sqlite:///default.db

# mysql
mysql://user:passwd@ip:port/my_db

示例

from playhouse.db_url import connect

db = connect('sqlite:///default.db')

定义Model表结构

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

from peewee import *
import datetime

# 设置数据库
db = SqliteDatabase("demo.db")


# 基类，设置数据库链接
class BaseModel(Model):
    class Meta:
        database = db


# 定义数据表
class UserModel(BaseModel):
    name = CharField(unique=True)
    created_time = DateTimeField(default=datetime.datetime.now)

    class Meta:
        table_name = 'tb_user'


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

创建数据表

db.connect()

# 创建数据表
# db.create_tables([UserModel], safe=True)
if not UserModel.table_exists():
    UserModel.create_table()


db.close()

查询示例

排序

# 单排序
list(UserModel.select().order_by(UserModel.created_time.asc()))
('SELECT "t1"."id", "t1"."name", "t1"."created_time" FROM "tb_user" AS "t1" 
ORDER BY "t1"."created_time" ASC', [])
    
   
# 多字段排序
list(UserModel.select().order_by(
        UserModel.created_time.asc(),
        UserModel.id.asc()
    ))
('SELECT "t1"."id", "t1"."name", "t1"."created_time" FROM "tb_user" AS "t1" 
ORDER BY "t1"."created_time" ASC, "t1"."id" ASC', [])

逻辑操作符

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

以下代码参考官方示例

示例代码：

from chinesename import chinesename

# 添加数据
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()

其他常用设置

打印日志

import logging

# 打印日志
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)
logger.propagate = False  # 不向上传播

python2解决编码问题

# python2解决编码问题
import sys
reload(sys)
sys.setdefaultencoding("utf-8")