farmer

介绍

farmer，是一个极简的 MySQL Python ORM，极简的 CURD 映射方法，支持连接池，事务等特性，以及常见的增删改查操作

开始使用

farmer 并不提供建表建库的功能，所有 Model 操作都建立在已经存在对应数据库，已经存在对应表的前提下，建表建库改表等推荐使用 pt-online

假设已经在本机 3306 端口启用 MySQL 服务，其中一个 database 为 chalet，改库用户名密码分别为 chalet, chalet@pswd 并在 chalet 里有一张表，结构如下：

mysql chalet@localhost:chalet> show create table post;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| post  | CREATE TABLE `post` (                                                                     |
|       |   `id` int(8) unsigned NOT NULL AUTO_INCREMENT,                                           |
|       |   `author_id` int(8) unsigned NOT NULL,                                                   |
|       |   `content` longblob,                                                                     |
|       |   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,                             |
|       |   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
|       |   `status` tinyint(4) NOT NULL,                                                           |
|       |   PRIMARY KEY (`id`),                                                                     |
|       |   KEY `idx_author_id_status` (`author_id`,`status`)                                       |
|       | ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8                                    |
+-------+-------------------------------------------------------------------------------------------+
1 row in set
Time: 0.020s

安装

$ pip install farmer-orm

创建数据库连接池

# coding: utf-8

from farmer import SQLBase


db = SQLBase('localhost', 'chalet', 3306, 'chalet', 'chalet@pswd')
# SQLBase.__init__ 函数签名为 def __init__(self, host, database, port, user, passwd, **kw)，分别对应数据库初始化几个必填参数

定义 Model

import datetime

from farmer import Model


class BaseModel(Model):
    __database__ = db


class Post(BaseModel):
    __table__ = 'post'

    class Fields:
        id = None
        author_id = None
        content = ''
        status = None
        create_time = datetime.datetime.now
        update_time = datetime.datetime.now


class User(BaseModel):
    __table__ = 'user'

    class Fields:
        pass

Query

使用 farmer 查询，提供了两个基本方法，Model.get，Model.where，get 返回满足条件的第一个，where 返回满足条件的所有记录的迭代器, .count() 可以返回记录的条数，等价于 SQL 的 COUNT(*)，where 可自由组合，get 和 where 的函数签名一致，如 where id > 10 AND status = 1 AND author_id = 100, 对应到 farmer 等价于如下几种方式：

Model.where('id > 10', 'status = 1', 'author_id = 100')
Model.where('id > 10').where('status = 1').where('author_id = 100')
Model.where('id > 10', status=1, author_id=100)
Model.where('id > 10').where(status=1).where(author_id=100)

举个例子🌰

>>> ret = Post.get(id=2)
>>> ret
Post(id=2, author_id=100, content={"content": "你好世界😶"}, create_time=2017-11-26 20:18:50, update_time=2017-11-27 08:26:45, status=1)
>>> ret.id, ret.author_id, ret.content
(2, 100, '{"content": "你好世界😶"}')
>>>
>>> ret = Post.where('id < 4')
>>> ret
[Post(id=1, author_id=100, content={"content": "hello world"}, create_time=2017-11-26 18:53:21, update_time=2017-11-26 18:53:21, status=1), Post(id=2, author_id=100, content={"content": "你好世界😶"}, create_time=2017-11-26 20:18:50, update_time=2017-11-27 08:26:45, status=1), Post(id=3, author_id=100, content=test, create_time=2017-12-02 15:22:46, update_time=2017-12-02 15:22:46, status=1)]
>>> ret.count()
3
>>> ret_first = ret[0]
>>> ret_first
Post(id=1, author_id=100, content={"content": "hello world"}, create_time=2017-11-26 18:53:21, update_time=2017-11-26 18:53:21, status=1)
>>>
>>> ret = Post.where('id > 8', status=1)
>>> ret
[Post(id=11, author_id=100, content=test, create_time=2017-12-09 05:47:41, update_time=2017-12-09 05:47:41, status=1), Post(id=13, author_id=100, content=test, create_time=2017-12-09 05:48:14, update_time=2017-12-09 05:48:14, status=1), Post(id=15, author_id=3200, content={aaaa}, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=1), Post(id=16, author_id=3200, content={aaaa}, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=1), Post(id=17, author_id=3200, content={aaaa}, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=1), Post(id=18, author_id=300, content={}, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=1)]
>>> ret = Post.where(status=1).where('id < 4')
>>> ret
[Post(id=1, author_id=100, content={"content": "hello world"}, create_time=2017-11-26 18:53:21, update_time=2017-11-26 18:53:21, status=1), Post(id=2, author_id=100, content={"content": "你好世界😶"}, create_time=2017-11-26 20:18:50, update_time=2017-11-27 08:26:45, status=1), Post(id=3, author_id=100, content=test, create_time=2017-12-02 15:22:46, update_time=2017-12-02 15:22:46, status=1)]

同时实现了一些其他操作，如 GROUP BY, DISTINCT, LIMIT, IN, ORDER BY

GROUP BY 实现了最简单的 GROUP BY 操作，返回的值为 group_by 传入的字段值，使用如下:

>>> ret = Post.where().group_by('status')
>>> [status for status, in ret]
[0, 1, 2]

DISTINCT 类似 group_by，返回的值为 distinct_by 传入的字段值，使用如下:

>>> ret = Post.where().distinct_by('author_id')
>>> [author_id for author_id, in ret]
[10, 100, 123, 234, 3200, 1000000]

LIMIT limits 接口为 LIMIT 关键字的实现，使用如下：

>>> ret = Post.where().limits(3)
>>> [p.id for p in ret]
[1, 2, 3]
>>>
>>> ret = Post.where().limits(3, 3)
>>> [p.id for p in ret]
[5, 11, 13]

IN IN 关键字的实现接口为 value_in(field, values)，避免使用了 Python 关键字 in，使用如下：

>>> ret = Post.where().value_in('author_id', (100, 101, 102, 200)) # the same as '`author_id` in (100, 101, 102, 200)'
>>> [p.author_id for p in ret]
[100, 100, 100, 100, 100]
>>> ret.count()
5

ORDER BY 使用 order_by:

>>> ret = Post.where().order_by('id desc').limits(3)
>>> [p.id for p in ret]
[28, 27, 26]
>>> ret = Post.where().order_by('id').limits(3)
>>> [p.id for p in ret]
[1, 2, 3]

Delete

farmer 提供 Model.delete() 来删除记录，delete() 可直接接在 Model.where()/Model.get() 后, 删除条件语句于上面 Query 用法相同

举个例子🌰

>>> ret = Post.get(id=25)
>>> ret
Post(id=25, author_id=13, content=13, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=3)
>>> ret.delete()
1
>>> ret = Post.get(id=25)
>>> ret
>>>
>>> Post.where('id > 20')
[Post(id=21, author_id=10, content=, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=10), Post(id=22, author_id=111, content=111, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=111), Post(id=23, author_id=222, content=222, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=22), Post(id=24, author_id=12, content=12, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=12)]
>>> Post.where('id > 20').delete()
4

Update

farmer 提供了 Model.update()、Model.save() 等方法来更新记录，同上面的 delete，.update() 可以直接接在 Model.get()/Model.where() 语句后，进行单条、批量更新，若更新时未指明字段值，其值默认为在Model.Fields 内定义的默认值。

举个例子🌰

>>> ret = Post.where(id=18)[0]
>>> ret
Post(id=18, author_id=300, content={}, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=1)
>>> ret.content
'{}'
>>> import json
>>> ret.content = json.dumps({'content': 'hello farmer'})
>>> ret.content
'{"content": "hello farmer"}'
>>> ret.save()
Post(id=0, author_id=300, content={"content": "hello farmer"}, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11, status=1)
>>> ret = Post.where(id=18)[0]
>>> ret.content
'{"content": "hello farmer"}'
>>>
>>> ret = Post.where(id=18)[0]
>>> ret.update(author_id=1000000)
1
>>> ret = Post.where(id=18)[0]
>>> ret.author_id
1000000
>>>
>>> [(p.id, p.status) for p in Post.where('id > 15')]
[(16, 1), (17, 1), (18, 1), (19, 2), (20, 4)]
>>> Post.where('id > 15').update(status=1)
4
>>> [(p.id, p.status) for p in Post.where('id > 15')]
[(16, 1), (17, 1), (18, 1), (19, 1), (20, 1)]

Insert

插入记录与更新记录大体相同

举个例子🌰

>>> ret = Post(author_id=123, content='{}', status=0)
>>> ret.save()
Post(author_id=123, content={}, status=0, id=26, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11)
>>>
>>>
>>> ret = Post(author_id=123, content='{}', status=0).save()
>>> ret
Post(author_id=123, content={}, status=0, id=27, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11)
>>>
>>> ret = Post()
>>> ret.author_id = 234
>>> ret.status = 1
>>> ret.save()
Post(author_id=234, status=1, id=28, content=, create_time=2017-11-11 11:11:11, update_time=2017-11-11 11:11:11)

事务

farmer 封装了事务方法，可以更为方便的套上事务，提供了 db.with_transaction() 装饰器，db.transaction_context() 上下文管理器

举个例子🌰

>>> @db.with_transaction
    def update_status(id):
        ret = Post.where('id > %s' % id).update(status=1)

>>> with db.transaction_context():
        Post.where('id > 10').update(status=1)

其他

raw

model.raw 的用法与 db.execute() 类似，只不过 raw 内包含了 fetch 这一步骤，并返回 generator，使用如下

>>> ret = Post.raw("select id from post")
>>> type(ret)
<class 'generator'>
>>> list(ret)
[(5,), (1,), (2,), (3,), (11,), (13,), (29,), (26,), (27,), (28,), (15,), (16,), (17,), (18,), (19,), (20,)]
>>> ret = Post.raw("insert into post (author_id, content, status) values(%s, %s, %s)", 321, 'test raw', 2)
>>> Post.get(author_id=321)
Post(id=31, author_id=321, content=test raw, create_time=2017-12-23 13:08:04, update_time=2017-12-23 13:08:04, status=2)

Tips

farmer 处于开发完善阶段，谨慎用于生产环境

farmer-orm
Release 0.4.4

Release 0.4.4

0.4.4

0.4.3

0.4.2

0.4.1

0.3.3

0.3.2

0.3.1

0.3.0

0.2.3

0.2.1

Documentation

farmer

介绍

开始使用

安装

创建数据库连接池

定义 Model

Query

举个例子🌰

Delete

举个例子🌰

Update

举个例子🌰

Insert

举个例子🌰

事务

举个例子🌰

其他

raw

Tips

Stats

Releases

Contributors

farmer-orm Release 0.4.4

Release 0.4.4 Toggle Dropdown 0.4.4 0.4.3 0.4.2 0.4.1 0.3.3 0.3.2 0.3.1 0.3.0 0.2.3 0.2.1

Documentation

farmer

介绍

开始使用

安装

创建数据库连接池

定义 Model

Query

举个例子🌰

Delete

举个例子🌰

Update

举个例子🌰

Insert

举个例子🌰

事务

举个例子🌰

其他

raw

Tips

Stats

Releases

Contributors

farmer-orm
Release 0.4.4

Release 0.4.4

0.4.4

0.4.3

0.4.2

0.4.1

0.3.3

0.3.2

0.3.1

0.3.0

0.2.3

0.2.1