# 更新历史
- v2版本
# 说明
- 从此告别SQL语句，直接调用方法就完事
- python3.10+
- 持续更新中...
# 如何安装？
- `pip install sqlman`
# 拿什么吸引你这个靓仔？
- 使用方式简单暴力
- 不用写SQL就能进行增删改查
### 连接方式是如此简易
- 一个字典参数即可
### 插入数据是如此贴心
- 自动推导
- 传入dict是插入一条数据，传入list是插入多条数据
- 多种插入模式
- 模式1，插入时，数据冲突则报错
- 模式2，插入时，数据冲突则忽略
- 模式3，插入时，数据发生冲突，把数据进行更新操作
- 模式4，插入时，自动过滤掉冲突的数据，只插入不冲突的数据
### 等等等等...
# 操练起来
### 连接mysql
- 数据库对象
```python
from sqlman import Connector
# 方式1
db = Connector(host="localhost", port=3306, username="root", password="root@0", db="test") # 数据库对象
# 方式2
MYSQL_CONF = {
'host': 'localhost',
'port': 3306,
'username': 'root',
'password': 'root@0',
'db': 'test'
}
db = Connector(**MYSQL_CONF) # 数据库对象
# 方式3
MYSQL_URL = "mysql://root:root@0@localhost:3306/test"
db = Connector.from_url(MYSQL_URL) # 数据库对象
```
- 表格对象
```python
student = db['student']
student = db.pick_table('student')
```
### 准备测试数据
```python
# 一条龙服务，创建people表并插入测试数据，每次插入一千条，累计插入一万条
db.gen_test_table('people', once=1000, total=10000)
people = db['people']
```
### 插入数据
#### 单条插入
```python
data = {'id': 10001, 'name': '小明', 'age': 10, 'gender': '男'}
# 插入一条数据
people.insert_data(data)
# 当插入的数据与表中的数据存在冲突时，直接插入会报错，如果补充<unique>参数，则不报错
people.insert_data(data, unique='id')
```
#### 批量插入
```python
data = [
{'id': 10002, 'name': '小红', 'age': 12, 'gender': '女'},
{'id': 10003, 'name': '小强', 'age': 13, 'gender': '男'},
{'id': 10004, 'name': '小白', 'age': 14, 'gender': '男'}
]
# 插入多条数据
people.insert_data(data)
```
#### 插入数据时，如果数据冲突则进行更新
```python
data = {'id': 10001, 'name': '小明', 'age': 10, 'gender': '男'}
# 当数据冲突时，也可以直接进行更新操作，下面是把age更新为11
people.insert_data(data, update='age=age+1')
```
### 删除数据
```python
# delete from people where id=1
people.delete(id=1)
# delete from people where id in (1, 2, 3)
people.delete(id=[1, 2, 3])
# delete from people where age=18 limit 100
people.delete(age=18, limit=100)
```
### 更新数据
```python
# update people set name='tony', job='理发师' where id=1
people.update(new={'name': 'tony', 'job': '理发师'}, id=1)
# update people set job='程序员' where name='thomas' and phone='18959176772'
people.update(new={'job': '程序员'}, name='thomas', phone='18959176772')
```
### 查询数据
```python
# select * from people where id=1
people.query(id=1)
# select name, age from people where id=2
people.query(pick='name, age', id=2)
# select * from people where age=18 and gender in ('男', '女')
people.query(age=18, gender=['男', '女'])
# select name from people where age=18 and gender in ('男', '女') limit 5
people.query(pick='name', age=18, gender=['男', '女'], limit=5)
```
### 随机数据
```python
# 随机返回1条数据<dict>
print(people.random())
# 随机返回5条数据<list>
print(people.random(limit=5))
```
### 遍历表
```python
# 遍历整张表，默认每轮扫描1000条，默认只打印数据
people.scan()
def show(lines):
for some in enumerate(lines, start=1):
print('第{}条 {}'.format(*some))
# 限制id范围为101~222，每轮扫描100条，每轮的回调函数为show
people.scan('people', sort_field='id', start=101, end=222, once=100, dealer=show)
# 限制id范围的基础上，限制age=18
people.scan('people', sort_field='id', start=101, end=222, once=100, dealer=show, add_cond='age=18')
```
Raw data
{
"_id": null,
"home_page": "https://github.com/markadc/sqlman",
"name": "sqlman",
"maintainer": null,
"docs_url": null,
"requires_python": null,
"maintainer_email": null,
"keywords": "Python, MySQL, Database",
"author": "WangTuo",
"author_email": "markadc@126.com",
"download_url": "https://files.pythonhosted.org/packages/8b/ab/a97ea32a217a26c28023f89ae888d0fa91e2e7ff99ed88458dd5212af796/sqlman-0.4.5.tar.gz",
"platform": null,
"description": "# \u66f4\u65b0\u5386\u53f2\n\n- v2\u7248\u672c\n\n# \u8bf4\u660e\n\n- \u4ece\u6b64\u544a\u522bSQL\u8bed\u53e5\uff0c\u76f4\u63a5\u8c03\u7528\u65b9\u6cd5\u5c31\u5b8c\u4e8b\n- python3.10+\n- \u6301\u7eed\u66f4\u65b0\u4e2d...\n\n# \u5982\u4f55\u5b89\u88c5\uff1f\n\n- `pip install sqlman`\n\n# \u62ff\u4ec0\u4e48\u5438\u5f15\u4f60\u8fd9\u4e2a\u9753\u4ed4\uff1f\n\n- \u4f7f\u7528\u65b9\u5f0f\u7b80\u5355\u66b4\u529b\n\n- \u4e0d\u7528\u5199SQL\u5c31\u80fd\u8fdb\u884c\u589e\u5220\u6539\u67e5\n\n### \u8fde\u63a5\u65b9\u5f0f\u662f\u5982\u6b64\u7b80\u6613\n\n- \u4e00\u4e2a\u5b57\u5178\u53c2\u6570\u5373\u53ef\n\n### \u63d2\u5165\u6570\u636e\u662f\u5982\u6b64\u8d34\u5fc3\n\n- \u81ea\u52a8\u63a8\u5bfc\n - \u4f20\u5165dict\u662f\u63d2\u5165\u4e00\u6761\u6570\u636e\uff0c\u4f20\u5165list\u662f\u63d2\u5165\u591a\u6761\u6570\u636e\n\n- \u591a\u79cd\u63d2\u5165\u6a21\u5f0f\n - \u6a21\u5f0f1\uff0c\u63d2\u5165\u65f6\uff0c\u6570\u636e\u51b2\u7a81\u5219\u62a5\u9519\n - \u6a21\u5f0f2\uff0c\u63d2\u5165\u65f6\uff0c\u6570\u636e\u51b2\u7a81\u5219\u5ffd\u7565\n - \u6a21\u5f0f3\uff0c\u63d2\u5165\u65f6\uff0c\u6570\u636e\u53d1\u751f\u51b2\u7a81\uff0c\u628a\u6570\u636e\u8fdb\u884c\u66f4\u65b0\u64cd\u4f5c\n - \u6a21\u5f0f4\uff0c\u63d2\u5165\u65f6\uff0c\u81ea\u52a8\u8fc7\u6ee4\u6389\u51b2\u7a81\u7684\u6570\u636e\uff0c\u53ea\u63d2\u5165\u4e0d\u51b2\u7a81\u7684\u6570\u636e\n\n### \u7b49\u7b49\u7b49\u7b49...\n\n# \u64cd\u7ec3\u8d77\u6765\n\n### \u8fde\u63a5mysql\n\n- \u6570\u636e\u5e93\u5bf9\u8c61\n\n```python\nfrom sqlman import Connector\n\n# \u65b9\u5f0f1\ndb = Connector(host=\"localhost\", port=3306, username=\"root\", password=\"root@0\", db=\"test\") # \u6570\u636e\u5e93\u5bf9\u8c61\n\n# \u65b9\u5f0f2\nMYSQL_CONF = {\n 'host': 'localhost',\n 'port': 3306,\n 'username': 'root',\n 'password': 'root@0',\n 'db': 'test'\n}\ndb = Connector(**MYSQL_CONF) # \u6570\u636e\u5e93\u5bf9\u8c61\n\n# \u65b9\u5f0f3\nMYSQL_URL = \"mysql://root:root@0@localhost:3306/test\"\ndb = Connector.from_url(MYSQL_URL) # \u6570\u636e\u5e93\u5bf9\u8c61\n```\n\n- \u8868\u683c\u5bf9\u8c61\n\n```python\nstudent = db['student']\nstudent = db.pick_table('student')\n```\n\n### \u51c6\u5907\u6d4b\u8bd5\u6570\u636e\n\n```python\n# \u4e00\u6761\u9f99\u670d\u52a1\uff0c\u521b\u5efapeople\u8868\u5e76\u63d2\u5165\u6d4b\u8bd5\u6570\u636e\uff0c\u6bcf\u6b21\u63d2\u5165\u4e00\u5343\u6761\uff0c\u7d2f\u8ba1\u63d2\u5165\u4e00\u4e07\u6761\ndb.gen_test_table('people', once=1000, total=10000)\npeople = db['people']\n```\n\n### \u63d2\u5165\u6570\u636e\n\n#### \u5355\u6761\u63d2\u5165\n\n```python\ndata = {'id': 10001, 'name': '\u5c0f\u660e', 'age': 10, 'gender': '\u7537'}\n\n# \u63d2\u5165\u4e00\u6761\u6570\u636e\npeople.insert_data(data)\n\n# \u5f53\u63d2\u5165\u7684\u6570\u636e\u4e0e\u8868\u4e2d\u7684\u6570\u636e\u5b58\u5728\u51b2\u7a81\u65f6\uff0c\u76f4\u63a5\u63d2\u5165\u4f1a\u62a5\u9519\uff0c\u5982\u679c\u8865\u5145<unique>\u53c2\u6570\uff0c\u5219\u4e0d\u62a5\u9519\npeople.insert_data(data, unique='id')\n\n```\n\n#### \u6279\u91cf\u63d2\u5165\n\n```python\ndata = [\n {'id': 10002, 'name': '\u5c0f\u7ea2', 'age': 12, 'gender': '\u5973'},\n {'id': 10003, 'name': '\u5c0f\u5f3a', 'age': 13, 'gender': '\u7537'},\n {'id': 10004, 'name': '\u5c0f\u767d', 'age': 14, 'gender': '\u7537'}\n]\n\n# \u63d2\u5165\u591a\u6761\u6570\u636e\npeople.insert_data(data)\n```\n\n#### \u63d2\u5165\u6570\u636e\u65f6\uff0c\u5982\u679c\u6570\u636e\u51b2\u7a81\u5219\u8fdb\u884c\u66f4\u65b0\n\n```python\ndata = {'id': 10001, 'name': '\u5c0f\u660e', 'age': 10, 'gender': '\u7537'}\n\n# \u5f53\u6570\u636e\u51b2\u7a81\u65f6\uff0c\u4e5f\u53ef\u4ee5\u76f4\u63a5\u8fdb\u884c\u66f4\u65b0\u64cd\u4f5c\uff0c\u4e0b\u9762\u662f\u628aage\u66f4\u65b0\u4e3a11\npeople.insert_data(data, update='age=age+1')\n```\n\n### \u5220\u9664\u6570\u636e\n\n```python\n# delete from people where id=1\npeople.delete(id=1)\n\n# delete from people where id in (1, 2, 3)\npeople.delete(id=[1, 2, 3])\n\n# delete from people where age=18 limit 100\npeople.delete(age=18, limit=100)\n```\n\n### \u66f4\u65b0\u6570\u636e\n\n```python\n# update people set name='tony', job='\u7406\u53d1\u5e08' where id=1\npeople.update(new={'name': 'tony', 'job': '\u7406\u53d1\u5e08'}, id=1)\n\n# update people set job='\u7a0b\u5e8f\u5458' where name='thomas' and phone='18959176772'\npeople.update(new={'job': '\u7a0b\u5e8f\u5458'}, name='thomas', phone='18959176772')\n```\n\n### \u67e5\u8be2\u6570\u636e\n\n```python\n# select * from people where id=1\npeople.query(id=1)\n\n# select name, age from people where id=2\npeople.query(pick='name, age', id=2)\n\n# select * from people where age=18 and gender in ('\u7537', '\u5973')\npeople.query(age=18, gender=['\u7537', '\u5973'])\n\n# select name from people where age=18 and gender in ('\u7537', '\u5973') limit 5\npeople.query(pick='name', age=18, gender=['\u7537', '\u5973'], limit=5)\n```\n\n### \u968f\u673a\u6570\u636e\n\n```python\n# \u968f\u673a\u8fd4\u56de1\u6761\u6570\u636e<dict>\nprint(people.random())\n\n# \u968f\u673a\u8fd4\u56de5\u6761\u6570\u636e<list>\nprint(people.random(limit=5))\n```\n\n### \u904d\u5386\u8868\n\n```python\n\n\n\n# \u904d\u5386\u6574\u5f20\u8868\uff0c\u9ed8\u8ba4\u6bcf\u8f6e\u626b\u63cf1000\u6761\uff0c\u9ed8\u8ba4\u53ea\u6253\u5370\u6570\u636e\npeople.scan()\n\n\ndef show(lines):\n for some in enumerate(lines, start=1):\n print('\u7b2c{}\u6761 {}'.format(*some))\n\n\n# \u9650\u5236id\u8303\u56f4\u4e3a101~222\uff0c\u6bcf\u8f6e\u626b\u63cf100\u6761\uff0c\u6bcf\u8f6e\u7684\u56de\u8c03\u51fd\u6570\u4e3ashow\npeople.scan('people', sort_field='id', start=101, end=222, once=100, dealer=show)\n\n# \u9650\u5236id\u8303\u56f4\u7684\u57fa\u7840\u4e0a\uff0c\u9650\u5236age=18\npeople.scan('people', sort_field='id', start=101, end=222, once=100, dealer=show, add_cond='age=18')\n```\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "\u544a\u522bSQL\u8bed\u53e5\uff0cpython\u64cd\u4f5cmysql\u7684\u8d34\u5fc3\u52a9\u624b",
"version": "0.4.5",
"project_urls": {
"Homepage": "https://github.com/markadc/sqlman"
},
"split_keywords": [
"python",
" mysql",
" database"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "8baba97ea32a217a26c28023f89ae888d0fa91e2e7ff99ed88458dd5212af796",
"md5": "f92728b68abbad91fc12c46d8aeb6b2e",
"sha256": "c1a624f8d4cf3cf03027d86cd1e184dde175c348a8aaac748d85f052b2590a4c"
},
"downloads": -1,
"filename": "sqlman-0.4.5.tar.gz",
"has_sig": false,
"md5_digest": "f92728b68abbad91fc12c46d8aeb6b2e",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 12994,
"upload_time": "2024-08-01T03:54:51",
"upload_time_iso_8601": "2024-08-01T03:54:51.288499Z",
"url": "https://files.pythonhosted.org/packages/8b/ab/a97ea32a217a26c28023f89ae888d0fa91e2e7ff99ed88458dd5212af796/sqlman-0.4.5.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-08-01 03:54:51",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "markadc",
"github_project": "sqlman",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [
{
"name": "DBUtils",
"specs": [
[
"==",
"2.0.1"
]
]
},
{
"name": "Faker",
"specs": [
[
"==",
"18.6.0"
]
]
},
{
"name": "loguru",
"specs": [
[
"==",
"0.5.3"
]
]
},
{
"name": "PyMySQL",
"specs": [
[
"==",
"1.0.2"
]
]
}
],
"lcname": "sqlman"
}
JSON
