# Cator
- Github: [https://github.com/mouday/cator](https://github.com/mouday/cator)
- Pypi: [https://pypi.org/project/cator](https://pypi.org/project/cator)
- gitee: [https://gitee.com/mouday/cator](https://gitee.com/mouday/cator)
## 简介
支持 mysql和sqlite数据库, 在现有连接对象Connection 基础上进行增强
返回数据统一为dict 字典，提高脚本书写速度
## 安装
```bash
pip install cator
```
支持的mysql连接库（任选其一即可）：
- pymysql
- mysql-connector-python
- mysqlclient
## 使用示例
### 1、获取新的连接Database 对象
Database 可以适用各种场景
```python
import cator
# mysql
db_url = "mysql://root:123456@127.0.0.1:3306/data?charset=utf8&autocommit=true"
# open Database
db = cator.connect(db_url)
# close
db.close()
```
支持的连接url，其他参数可参考所使用的链接库的文档
```bash
# mysql autocommit=true参数指定自动提交
mysql://root:123456@127.0.0.1:3306/data?charset=utf8&autocommit=true
# mysql+reconnect 模式可以指定断线重连
mysql+reconnect://root:123456@127.0.0.1:3306/data?autocommit=true
# sqlite
sqlite:///data.db?isolation_level=null
```
### 2、Database对象 CURD使用示例
创建测试表
```sql
create table if not exists person (
id int PRIMARY KEY auto_increment,
name varchar(20),
age int
)
```
CURD
```python
# 执行原样sql 返回cursor对象
cursor = db.execute("show tables")
# insert
sql = "insert into person (`name`, `age`) values (:name, :age)"
data = [{'name': 'Tom', 'age': 23}]
row_count = db.insert(sql, data)
print(row_count) # 1
# insert_one
sql = "insert into person (`name`, `age`) values (:name, :age)"
data = {'name': 'Tom', 'age': 23}
row_id = db.insert_one(sql, data)
print(row_id) # 5
# select
sql = 'select * from person limit :limit'
data = {'limit': 1}
rows = db.select(sql, data)
# [{'id': 2, 'name': 'Tom', 'age': 23}]
# select_one
sql = 'select * from person where id = :id'
data = {'id': 5}
row = db.select_one(sql, data)
print(row)
# {'id': 5, 'name': 'Tom', 'age': 23}
# update
sql = "update person set name = :name where id = :id"
data = {
'name': 'Jack',
'id': 1
}
row_count = db.update(sql, data)
print(row_count) # 1
# delete
sql = "delete from person where id = :id"
data = {
'id': 1
}
row_count = db.delete(sql, data)
print(row_count) # 1
```
### Table操作
Table 类提供了一系列的简化操作
> 注意：使用table操作，仅支持`?`或者`%s`作为占位符
Table 仅适用于单表操作，多表操作可以使用 Database对象
```python
# 获取 Table 对象
table = db.table('person')
# insert_one
data = {'name': 'Tom', 'age': 23}
row_id = table.insert_one(data)
print(row_id) # 6
# INSERT INTO `person` ( `name`, `age` ) VALUES ( %(name)s, %(age)s )
# insert
data = [
{'name': 'Tom', 'age': 23},
{'name': 'Steve', 'age': 25}
]
row_count = table.insert(data)
print(row_count) # 2
# INSERT INTO `person` ( `age`, `name` ) VALUES ( %(age)s, %(name)s )
# update_by_id
data = {'name': 'Jackk'}
row_count = table.update_by_id(uid=1, data=data)
print(row_count) # 1
# UPDATE `person` SET `name` = %(name)s WHERE `id` = %(id)s
# delete_by_id
row_count = table.delete_by_id(uid=6)
print(row_count) # 1
# DELETE FROM `person` WHERE `id` = %(id)s
# where select
rows = (table
.where("id > ?", 1)
.order_by("id desc")
.limit(1)
.select())
# SELECT * FROM `person` WHERE id > %s ORDER BY id desc LIMIT %s
print(rows)
# [{'id': 9, 'name': 'Steve', 'age': 25}]
# select_by_id
row = table.select_by_id(uid=5)
print(row) # {'id': 5, 'name': 'Tom', 'age': 23}
# SELECT * FROM `person` WHERE `id` = %(id)s
# select count
total = table.select_count()
print(total) # 5
# SELECT count(*) as total FROM `person`
# where select_one
ret = (table
.where("id = ?", 2)
.select_one()
)
# SELECT * FROM `person` WHERE id = 2 LIMIT 1
print(ret)
# {'id': 2, 'name': 'Tom', 'age': 23}
# where select count
total = (table
.where("age > ?", 10)
.select_count()
)
print(total) # 7
# SELECT count(*) as total FROM `person` WHERE age > %s
# where delete
row_count = (table
.where("id = ?", 1)
.delete()
)
# DELETE FROM `person` WHERE id = %s
print(row_count) # 0
# where update
row_count = (table
.where("id = ?", 1)
.update({'age': 24})
)
# UPDATE `person` SET `age` = %s WHERE id = %s
print(row_count) # 1
# select page
query = table.where("age > ?", 1)
total = query.select_count('id')
print(total) # 7
# SELECT count(`id`) FROM `person` WHERE age > %s
rows = query.select_page(2, 1)
# SELECT * FROM `person` WHERE age > %s LIMIT %s OFFSET %s
print(rows)
# [{'id': 3, 'name': 'Tom', 'age': 23}]
# increment
row_count = table.where("id = ?", 4).increment('age', 1)
# UPDATE `person` SET `age` = `age` + %s WHERE id = %s
print(row_count)
# decrement
row_count = table.where("id = ?", 4).decrement('age', 1)
# UPDATE `person` SET `age` = `age` - %s WHERE id = %s
print(row_count)
```
## 2、扩展现有连接
`DatabaseProxy`类接收一个`Connection`对象，只需要实现以下4个方法即可
```python
class Connection(ABC):
def close(self):
pass
def commit(self):
pass
def rollback(self):
pass
def cursor(self):
pass
```
#### 2-1、扩展 peewee
通过`DatabaseProxy`类，使得peewee原生sql查询进行增强
```python
from peewee import MySQLDatabase
from cator import DatabaseProxy
config = {
'host': 'localhost',
'port': 3306,
'user': 'root',
'password': '123456',
'database': 'data',
'charset': 'utf8mb4',
}
db = MySQLDatabase(**config)
# use cator database proxy
db_proxy = DatabaseProxy(db)
```
#### 2-2、扩展 pymysql
```python
import pymysql
from cator import DatabaseProxy
config = {
'host': '127.0.0.1',
'user': 'root',
'password': '123456',
'database': 'data',
'port': 3306
}
connection = pymysql.connect(**config)
proxy_db = DatabaseProxy(connection)
rows = proxy_db.select('select * from person where id = :id', {'id': 15})
print(rows)
proxy_db.close()
```
## 支持的占位符
无论使用什么数据库驱动都支持4种占位符：
| paramstyle | support | Meaning | example |
| - | - | - | - |
| qmark | OK | Question mark style | `...WHERE name=?` |
| numeric | - | Numeric, positional style | `...WHERE name=:1` |
| named | OK | Named style | `...WHERE name=:name` |
| format | OK | ANSI C printf format codes | `...WHERE name=%s` |
| pyformat | OK | Python extended format codes | `...WHERE name=%(name)s` |
## 显示sql日志
```python
import logging
logger = logging.getLogger('cator')
logger.setLevel(level=logging.DEBUG)
```
## 注意问题
1. 使用时需注意链接超时问题
2. cator支持了autocommit自动提交，默认关闭，如有需要可以打开，
3. 如果需要执行事务就需要关闭自动提交
cator基于以下模块进行了改进
1. myquery：https://github.com/mouday/myquery
2. aquery：https://github.com/mouday/aquery
3. puremysql https://github.com/mouday/puremysql
4. pythink https://github.com/mouday/pythink
Raw data
{
"_id": null,
"home_page": "https://github.com/mouday/cator",
"name": "cator",
"maintainer": null,
"docs_url": null,
"requires_python": null,
"maintainer_email": null,
"keywords": "cator, mysql, pymysql, sqlite, dict, peewee",
"author": "Peng Shiyu",
"author_email": "pengshiyuyx@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/03/ed/55a1485fedb871115459fc491a7ea26befd9294e22e98fe1c9d87dcc1cef/cator-1.0.11.tar.gz",
"platform": null,
"description": "# Cator\n\n\n\n\n\n\n- Github: [https://github.com/mouday/cator](https://github.com/mouday/cator)\n- Pypi: [https://pypi.org/project/cator](https://pypi.org/project/cator)\n- gitee: [https://gitee.com/mouday/cator](https://gitee.com/mouday/cator)\n\n## \u7b80\u4ecb\n\n\u652f\u6301 mysql\u548csqlite\u6570\u636e\u5e93, \u5728\u73b0\u6709\u8fde\u63a5\u5bf9\u8c61Connection \u57fa\u7840\u4e0a\u8fdb\u884c\u589e\u5f3a\n\n\u8fd4\u56de\u6570\u636e\u7edf\u4e00\u4e3adict \u5b57\u5178\uff0c\u63d0\u9ad8\u811a\u672c\u4e66\u5199\u901f\u5ea6\n\n## \u5b89\u88c5\n\n```bash\npip install cator\n```\n\n\u652f\u6301\u7684mysql\u8fde\u63a5\u5e93\uff08\u4efb\u9009\u5176\u4e00\u5373\u53ef\uff09\uff1a\n- pymysql\n- mysql-connector-python\n- mysqlclient\n\n## \u4f7f\u7528\u793a\u4f8b\n\n### 1\u3001\u83b7\u53d6\u65b0\u7684\u8fde\u63a5Database \u5bf9\u8c61 \n\nDatabase \u53ef\u4ee5\u9002\u7528\u5404\u79cd\u573a\u666f\n\n```python\nimport cator\n\n# mysql \ndb_url = \"mysql://root:123456@127.0.0.1:3306/data?charset=utf8&autocommit=true\"\n\n# open Database\ndb = cator.connect(db_url)\n\n# close\ndb.close()\n```\n\n\u652f\u6301\u7684\u8fde\u63a5url\uff0c\u5176\u4ed6\u53c2\u6570\u53ef\u53c2\u8003\u6240\u4f7f\u7528\u7684\u94fe\u63a5\u5e93\u7684\u6587\u6863\n\n```bash\n# mysql autocommit=true\u53c2\u6570\u6307\u5b9a\u81ea\u52a8\u63d0\u4ea4\nmysql://root:123456@127.0.0.1:3306/data?charset=utf8&autocommit=true\n\n# mysql+reconnect \u6a21\u5f0f\u53ef\u4ee5\u6307\u5b9a\u65ad\u7ebf\u91cd\u8fde\nmysql+reconnect://root:123456@127.0.0.1:3306/data?autocommit=true\n\n# sqlite\nsqlite:///data.db?isolation_level=null\n```\n\n### 2\u3001Database\u5bf9\u8c61 CURD\u4f7f\u7528\u793a\u4f8b\n\n\u521b\u5efa\u6d4b\u8bd5\u8868\n\n```sql\ncreate table if not exists person (\n id int PRIMARY KEY auto_increment,\n name varchar(20),\n age int\n)\n```\n\nCURD\n\n```python\n# \u6267\u884c\u539f\u6837sql \u8fd4\u56decursor\u5bf9\u8c61\ncursor = db.execute(\"show tables\")\n\n\n# insert\nsql = \"insert into person (`name`, `age`) values (:name, :age)\"\ndata = [{'name': 'Tom', 'age': 23}]\nrow_count = db.insert(sql, data)\nprint(row_count) # 1\n\n\n# insert_one\nsql = \"insert into person (`name`, `age`) values (:name, :age)\"\ndata = {'name': 'Tom', 'age': 23}\nrow_id = db.insert_one(sql, data)\nprint(row_id) # 5\n\n\n# select\nsql = 'select * from person limit :limit'\ndata = {'limit': 1}\nrows = db.select(sql, data)\n# [{'id': 2, 'name': 'Tom', 'age': 23}]\n\n\n# select_one\nsql = 'select * from person where id = :id'\ndata = {'id': 5}\nrow = db.select_one(sql, data)\nprint(row)\n# {'id': 5, 'name': 'Tom', 'age': 23}\n\n\n# update\nsql = \"update person set name = :name where id = :id\"\ndata = {\n 'name': 'Jack',\n 'id': 1\n}\nrow_count = db.update(sql, data)\nprint(row_count) # 1\n\n\n# delete\nsql = \"delete from person where id = :id\"\ndata = {\n 'id': 1\n}\nrow_count = db.delete(sql, data)\nprint(row_count) # 1\n```\n\n### Table\u64cd\u4f5c\n\nTable \u7c7b\u63d0\u4f9b\u4e86\u4e00\u7cfb\u5217\u7684\u7b80\u5316\u64cd\u4f5c\n\n> \u6ce8\u610f\uff1a\u4f7f\u7528table\u64cd\u4f5c\uff0c\u4ec5\u652f\u6301`?`\u6216\u8005`%s`\u4f5c\u4e3a\u5360\u4f4d\u7b26\n\nTable \u4ec5\u9002\u7528\u4e8e\u5355\u8868\u64cd\u4f5c\uff0c\u591a\u8868\u64cd\u4f5c\u53ef\u4ee5\u4f7f\u7528 Database\u5bf9\u8c61\n\n```python\n# \u83b7\u53d6 Table \u5bf9\u8c61\ntable = db.table('person')\n\n\n# insert_one\ndata = {'name': 'Tom', 'age': 23}\nrow_id = table.insert_one(data)\nprint(row_id) # 6\n# INSERT INTO `person` ( `name`, `age` ) VALUES ( %(name)s, %(age)s )\n\n\n# insert\ndata = [\n {'name': 'Tom', 'age': 23},\n {'name': 'Steve', 'age': 25}\n]\nrow_count = table.insert(data)\nprint(row_count) # 2\n# INSERT INTO `person` ( `age`, `name` ) VALUES ( %(age)s, %(name)s )\n\n\n# update_by_id\ndata = {'name': 'Jackk'}\nrow_count = table.update_by_id(uid=1, data=data)\nprint(row_count) # 1\n# UPDATE `person` SET `name` = %(name)s WHERE `id` = %(id)s\n\n\n# delete_by_id\nrow_count = table.delete_by_id(uid=6)\nprint(row_count) # 1\n# DELETE FROM `person` WHERE `id` = %(id)s\n\n\n# where select\nrows = (table\n .where(\"id > ?\", 1)\n .order_by(\"id desc\")\n .limit(1)\n .select())\n# SELECT * FROM `person` WHERE id > %s ORDER BY id desc LIMIT %s\nprint(rows)\n# [{'id': 9, 'name': 'Steve', 'age': 25}]\n\n\n# select_by_id\nrow = table.select_by_id(uid=5)\nprint(row) # {'id': 5, 'name': 'Tom', 'age': 23}\n# SELECT * FROM `person` WHERE `id` = %(id)s\n\n\n# select count\ntotal = table.select_count()\nprint(total) # 5\n# SELECT count(*) as total FROM `person`\n\n# where select_one\nret = (table\n .where(\"id = ?\", 2)\n .select_one()\n )\n# SELECT * FROM `person` WHERE id = 2 LIMIT 1\n\nprint(ret)\n# {'id': 2, 'name': 'Tom', 'age': 23}\n\n\n# where select count\ntotal = (table\n .where(\"age > ?\", 10)\n .select_count()\n )\nprint(total) # 7\n# SELECT count(*) as total FROM `person` WHERE age > %s\n\n\n# where delete\nrow_count = (table\n .where(\"id = ?\", 1)\n .delete()\n )\n# DELETE FROM `person` WHERE id = %s\n\nprint(row_count) # 0\n\n# where update\nrow_count = (table\n .where(\"id = ?\", 1)\n .update({'age': 24})\n )\n# UPDATE `person` SET `age` = %s WHERE id = %s\nprint(row_count) # 1\n\n\n# select page\nquery = table.where(\"age > ?\", 1)\n\ntotal = query.select_count('id')\nprint(total) # 7\n# SELECT count(`id`) FROM `person` WHERE age > %s\n\nrows = query.select_page(2, 1)\n# SELECT * FROM `person` WHERE age > %s LIMIT %s OFFSET %s\nprint(rows)\n# [{'id': 3, 'name': 'Tom', 'age': 23}]\n\n\n# increment\nrow_count = table.where(\"id = ?\", 4).increment('age', 1)\n# UPDATE `person` SET `age` = `age` + %s WHERE id = %s\nprint(row_count)\n\n\n# decrement\nrow_count = table.where(\"id = ?\", 4).decrement('age', 1)\n# UPDATE `person` SET `age` = `age` - %s WHERE id = %s\nprint(row_count)\n```\n \n## 2\u3001\u6269\u5c55\u73b0\u6709\u8fde\u63a5\n\n`DatabaseProxy`\u7c7b\u63a5\u6536\u4e00\u4e2a`Connection`\u5bf9\u8c61\uff0c\u53ea\u9700\u8981\u5b9e\u73b0\u4ee5\u4e0b4\u4e2a\u65b9\u6cd5\u5373\u53ef\n\n```python\nclass Connection(ABC):\n def close(self):\n pass\n\n def commit(self):\n pass\n\n def rollback(self):\n pass\n\n def cursor(self):\n pass\n\n```\n\n#### 2-1\u3001\u6269\u5c55 peewee\n\n\u901a\u8fc7`DatabaseProxy`\u7c7b\uff0c\u4f7f\u5f97peewee\u539f\u751fsql\u67e5\u8be2\u8fdb\u884c\u589e\u5f3a\n\n```python\n\nfrom peewee import MySQLDatabase\nfrom cator import DatabaseProxy\n\n\nconfig = {\n 'host': 'localhost',\n 'port': 3306,\n 'user': 'root',\n 'password': '123456',\n 'database': 'data',\n 'charset': 'utf8mb4',\n}\n\ndb = MySQLDatabase(**config)\n\n# use cator database proxy\ndb_proxy = DatabaseProxy(db)\n\n```\n\n#### 2-2\u3001\u6269\u5c55 pymysql\n\n```python\nimport pymysql\nfrom cator import DatabaseProxy\n\nconfig = {\n 'host': '127.0.0.1',\n 'user': 'root',\n 'password': '123456',\n 'database': 'data',\n 'port': 3306\n}\n\nconnection = pymysql.connect(**config)\nproxy_db = DatabaseProxy(connection)\n\nrows = proxy_db.select('select * from person where id = :id', {'id': 15})\nprint(rows)\nproxy_db.close()\n\n```\n\n## \u652f\u6301\u7684\u5360\u4f4d\u7b26\n\n\u65e0\u8bba\u4f7f\u7528\u4ec0\u4e48\u6570\u636e\u5e93\u9a71\u52a8\u90fd\u652f\u63014\u79cd\u5360\u4f4d\u7b26\uff1a\n\n| paramstyle | support | Meaning | example |\n| - | - | - | - |\n| qmark | OK | Question mark style | `...WHERE name=?` |\n| numeric | - | Numeric, positional style | `...WHERE name=:1` |\n| named | OK | Named style | `...WHERE name=:name` |\n| format | OK | ANSI C printf format codes | `...WHERE name=%s` |\n| pyformat | OK | Python extended format codes | `...WHERE name=%(name)s` |\n\n\n## \u663e\u793asql\u65e5\u5fd7\n\n```python\nimport logging\n\nlogger = logging.getLogger('cator')\nlogger.setLevel(level=logging.DEBUG)\n```\n\n## \u6ce8\u610f\u95ee\u9898\n\n1. \u4f7f\u7528\u65f6\u9700\u6ce8\u610f\u94fe\u63a5\u8d85\u65f6\u95ee\u9898\n2. cator\u652f\u6301\u4e86autocommit\u81ea\u52a8\u63d0\u4ea4\uff0c\u9ed8\u8ba4\u5173\u95ed\uff0c\u5982\u6709\u9700\u8981\u53ef\u4ee5\u6253\u5f00\uff0c\n3. \u5982\u679c\u9700\u8981\u6267\u884c\u4e8b\u52a1\u5c31\u9700\u8981\u5173\u95ed\u81ea\u52a8\u63d0\u4ea4\n\ncator\u57fa\u4e8e\u4ee5\u4e0b\u6a21\u5757\u8fdb\u884c\u4e86\u6539\u8fdb\n\n1. myquery\uff1ahttps://github.com/mouday/myquery\n2. aquery\uff1ahttps://github.com/mouday/aquery\n3. puremysql https://github.com/mouday/puremysql\n4. pythink https://github.com/mouday/pythink\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "a quick tool base dict for mysql and sqlite",
"version": "1.0.11",
"project_urls": {
"Homepage": "https://github.com/mouday/cator"
},
"split_keywords": [
"cator",
" mysql",
" pymysql",
" sqlite",
" dict",
" peewee"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "8dadc6bf03adc105f86cb7f79bf9169cc72c16e00f0bcdb18b49460865f61c6d",
"md5": "6f57169fa711e8742f941f7becac80a3",
"sha256": "12cfc4c95f417e815452f8d0d4101284e502e1eedcc675af7ae391e79a727b25"
},
"downloads": -1,
"filename": "cator-1.0.11-py3-none-any.whl",
"has_sig": false,
"md5_digest": "6f57169fa711e8742f941f7becac80a3",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 21086,
"upload_time": "2024-03-29T09:50:02",
"upload_time_iso_8601": "2024-03-29T09:50:02.805725Z",
"url": "https://files.pythonhosted.org/packages/8d/ad/c6bf03adc105f86cb7f79bf9169cc72c16e00f0bcdb18b49460865f61c6d/cator-1.0.11-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "03ed55a1485fedb871115459fc491a7ea26befd9294e22e98fe1c9d87dcc1cef",
"md5": "022de77b9645116403a250f7b8dc7bdf",
"sha256": "4607ab19fc95693e54057134dbb06a01fb3355e4afe3013b2201702c08a88eb3"
},
"downloads": -1,
"filename": "cator-1.0.11.tar.gz",
"has_sig": false,
"md5_digest": "022de77b9645116403a250f7b8dc7bdf",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 17267,
"upload_time": "2024-03-29T09:50:05",
"upload_time_iso_8601": "2024-03-29T09:50:05.191475Z",
"url": "https://files.pythonhosted.org/packages/03/ed/55a1485fedb871115459fc491a7ea26befd9294e22e98fe1c9d87dcc1cef/cator-1.0.11.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-03-29 09:50:05",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "mouday",
"github_project": "cator",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "cator"
}
JSON
