# ThinkSQL 类似 ThinkPHP 的数据库引擎
## Drivers
- [x] MySQL
- [x] 达梦(DM8)
## Install
- use mysql
```
pip install think_sql[mysql]
```
- use 达梦(DM8)
```
pip install think_sql[dm]
```
## How to use
### 1. simple demo
> Database: `test` Table: `user`
- example dict params
```python
from think_sql import DB
config = {
'type': 'mysql',
'host': '127.0.0.1',
'port': 3306,
'user': 'root',
'password': 'root',
'database': 'test',
}
with DB(config) as db:
data = db.table('user').where('id',1).find()
print(data)
```
- example dsn str
```python
from think_sql import DB
with DB("root:'root'@127.0.0.1:3306/test") as db:
data = db.table('user').where('id',1).find()
print(data)
```
- example DBConfig
```python
from think_sql import DB
from think_sql.tool.util import DBConfig
config = DBConfig(
type='mysql',
host='127.0.0.1',
port=3306,
user='root',
password='root',
database='test',
)
with DB(config) as db:
data = db.table('user').where('id',1).find()
print(data)
```
result
```json
{
"id": 1,
"username": "hbh112233abc",
"age": "36",
"address": "FUJIAN.XIAMEN"
}
```
- use `db` function
```python
from think_sql import db
config = {
'type': 'mysql',
'host': '127.0.0.1',
'port': 3306,
'user': 'root',
'password': 'root',
'database': 'test',
}
conn = db(config)
data = conn.table('user').where('id',1).find()
print(data)
conn.close()
```
### 2. Introduction
#### DB
- **init**(config:Union[str,dict,DBConfig],params={})
init database, return DB instance
- config:Union[str,dict,DBConfig]
- str: `type://user:'password'@host:port/database`
- dict: `{'type':'mysql','host':'127.0.0.1','port':3306,'user':'root','password':'root','database':'test'}`
- DBConfig: `DBConfig(type='mysql',host='127.0.0.1',port=3306,user='root',password='root',database='test')`
- params:dict pymysql connect other params
- connect()
connect database use **init** params
- table(table_name):Table
return class Table <think_sql.tool.interface.TableInterface>
- check_connected():bool
check connected, try reconnect database
- query(sql,params=())
query sql return cursor.fetchall List[dict]
- execute(sql,params=())
execute sql write operate(ex:insert,update,delete,...)
#### Table
- **init**(connector: Connection,cursor: Cursor,table_name: str,debug: bool = True)
- init()
initialize query condition
- debug(flag=True)
set debug flag
- set_cache_storage(storage: CacheStorage)
set cache storage ex: Redis
- cache(key: str = None, expire: int = 3600)
use cache at query
- cursor(sql: str, params: list = []) -> Cursor
return cursor object
- get_last_sql() -> str
return last sql string
- get_lastid() -> str
return last row id
- get_rowcount() -> int
return affect rows count
- fetch_sql(flag: bool = True)
set fetch sql flag,if flag = True then `query` and `execute` will only return sql
- build_sql(operation: str, params: list = []) -> str
return build sql
- query(sql: str, params: list = []) -> list
execute read operation sql and return cursor.fetchall()
when `fetch_sql`=True then return sql and not execute the sql
- execute(sql: str, params: list = []) -> int
execute write operation sql and return affect rows count
when `fetch_sql`=True then return sql and not execute the sql
- where(field: Union[str, list, tuple], symbol: str = '', value: Any = None)
set query conditions, support multipe use
> where(field,value)
```sql
where field = value
```
> where(field,symbol,value)
```sql
where field symbol value
```
```python
where(
[
[field1,symbol1,value1],
[field2,symbol2,value2]
]
)
```
```sql
where field1 symbol1 value1 and field2 symbol2 value2
```
> where(field1,symbol1,value1).where(field2,symbol2,value2)
```sql
where field1 symbol1 value1 and field2 symbol2 value2
```
- symbol
| symbol | another | demo |
| ---------- | ------------------------ | --------------------------------------------------------------------------------------- |
| `=` | `eq`,`=` | where('id','=',1) |
| `<>` | `neq`, `!=`, `<>` | where('id','<>',1) |
| `>` | `gt`,`>` | where('id','>',1) |
| `>=` | `egt`,`>=` | where('id','>=',1) |
| `<` | `lt`, `<` | where('id','<',1) |
| `<=` | `elt`,`<=` | where('id','<=',1) |
| `in` | `in`,`not in` | where('id','in',[1,2,3]) |
| `between` | `between`,`not between` | where('id','between',[1,5]) where('id','between','1,5') where('id','between','1 and 5') |
| `like` | `like`, `not like` | where('name','like','%hbh%') |
| `null` | `is null`,`null` | where('remark','is null') |
| `not null` | `is not null`,`not null` | where('remark','is not null') |
| `exists` | `exists`, `not exists` | where('remark','exists') |
| `exp` | `exp` | where('id','exp','in (1,2,3)') |
- where_or(field: Union[str, list], symbol: str = '', value: Any = None)
> where('id',1).where_or('id',5)
```
where id = 1 or id = 5
```
- limit(start: int, step: int = None)
LIMIT start,step
- page(index: int = 1, size: int = 20)
LIMIT index\*size-1,size
- order(field: str, sort: str = 'asc')
ORDER BY field sort
- group(field:str)
GROUP BY field
- distinct(field:str)
SELECT DISTINCT field
- field(fields: Any, exclude: bool = False)
SELECT fields
if `exclude`=True then select the fields of table (exlude:`fields`)
- select(build_sql: bool = False) -> list
return select query result
if `build_sql`=True then return sql
- find()
return select ... limit 1
- value(field: str)
return the field of first row
- column(field: str,key: str = '')
> column('name')
return ['hbh','mondy']
> column('name,score')
return [{'hbh':80},{'mondy':88}]
> column('score','name')
return {'hbh':80, 'mondy':88}
> column('id,score','name')
return {
'hbh':{'id':1,'score':80},
'mondy':{'id':2,'score':88}
}
- alias(short_name: str = '')
set alias table_name
- join(table_name: str, as_name: str = '', on: str = '', join: str = 'inner', and_str: str = '')
- `table_name` join table_name
- `as_name` alias short_table_name for `table_name`
- `on` join condition
- `join` join type in 'INNER', 'LEFT', 'RIGHT', 'FULL OUTER'
- `and_str` and condition
demo
```python
db.table('table1').alias('a').join(
'table2','b','a.id=b.a_id','left'
).join(
'table3','c','c.a_id=a.id'
).field(
'a.id,a.name,b.id as b_id,b.score,c.id as c_id,c.remark'
).where(
'a.id',1
).find()
```
sql
```sql
SELECT
a.id,
a.name,
b.id AS b_id,
b.score,
c.id AS c_id,
c.remark
FROM
table1 AS a
LEFT JOIN table2 AS b ON a.id = b.a_id
INNER JOIN table3 AS c ON c.a_id = a.id
WHERE
a.id = 1
LIMIT 1
```
- union(sql1: str, sql2: str, union_all: bool = False)
union sql1 and sql2
- union_all if union_all is True then `UNION ALL`
_demo_
```python
sql1 = db.table('table1').field('name,score').where('status',1).select(build_sql=True)
sql2 = db.table('table2').field('name,score').where('status',1).select(build_sql=True)
result = db.table().union(sql1,sql2).where('score','>',60).select()
```
_sql_
```sql
SELECT
*
FROM
( SELECT `name`, `score` FROM table1 WHERE `status` = 1 )
UNION
( SELECT `name`, `score` FROM table2 WHERE `status` = 1 )
WHERE
score > 60
```
- insert(data: Union[dict, List[dict]], replace: bool = False) -> int
insert data to database
- `data` dict: insert one record; list: insert multiple records
- `replace` bool if `replace` is True then use `REPLACE INTO`
_demo_
insert one record
```python
db.table('table1').insert({'name':'test','score':100})
```
```sql
INSERT INTO table1 (`name`, `score`) VALUES ('test', 100)
```
insert multiple records
```python
db.table('table1').insert([{'name':'test','score':100},{'name':'test2','score':101}])
```
```sql
INSERT INTO table1 (`name`, `score`) VALUES ('test', 100), ('test2', 101)
```
replace mode
```python
db.table('table1').insert({'id':1,'name':'test','score':100},replace=True)
```
```sql
REPLACE INTO table1 (`id`, `name`, `score`) VALUES (1,'test', 100)
```
- update(data: dict, all_record: bool = False) -> int
update data
- `data` dict you want update data
- `all_record` bool if `all_record` is False then you must set update condition; if you want to update all records then you need set `all_record` = True
- delete(all_record: bool = False) -> int
delete record
- `all_record` bool if `all_record` is False then you must set delete condition; if you want to delete all records then you need set `all_record` = True
- inc(field: str, step: Union[str, int, float] = 1) -> int
increase `field` +`step`
- dec(field: str, step: int = 1) -> int
decrease `field` -`step`
- max(field: str) -> Union[int, float]
get the max value of `field`
- sum(field: str) -> Union[int, float, Decimal]
get the sum value of `field`
- avg(field: str) -> Union[int, float, Decimal]
get the avg value of `field`
- count(field: str = '\*') -> int
get the count of records
- copy_to(new_table: str = None, create_blank_table: bool = False) -> int
copy data to `new_table`
- `new_table` if `new_table` is None then `new_table` will auto set like `{table_name}_copy`
- `create_blank_table` bool if `create_blank_table` is True then only create a blank table like current table.
_demo_
```sql
db.table('user').field(
'name,score'
).where(
'score','>',60
).copy_to('good_boy')
```
_sql_
```sql
SELECT
`name`,
`score`
INTO `good_boy`
FROM
`user`
WHERE
score > 60
```
- insert_to(new_table: str, fields: Union[str, list, tuple] = None) -> int
```sql
INSERT INTO {new_table} SELECT {select_fields} FROM {table} {join} WHERE {where}{group}{order}{limit}
```
- exists(self) -> bool
check record exists with some query conditions, it use `SELECT 1 FROM {table} {join} WHERE {where} LIMIT 1`
- batch_update(data:List[dict],key:str) -> int
batch update multiple records
_demo_
```python
data = [
{'id':1,'score':66},
{'id':2,'score':59},
{'id':3,'score':86},
{'id':4,'score':90},
]
db.table('user').batch(data,key='id')
```
_sql_
```sql
update `user` set score = 66 where id = 1;
update `user` set score = 59 where id = 2;
update `user` set score = 86 where id = 3;
update `user` set score = 90 where id = 4;
```
#### support transaction
```python
from think_sql import DB
db_dsn = "root:'password'@127.0.0.1:3306/database"
with DB(db_dsn) as db:
# result: insert two records into database
with db.start_trans():
db.table('user').insert({'name':'think_sql1','score':98})
db.table('user').insert({'name':'think_sql2','score':99})
# result: nothing inserted
with db.start_trans():
db.table('user').insert({'name':'think_sql1','score':98})
db.table('user').insert({'name':'think_sql2','score':99})
raise Exception('error')
# The above operation does not affect subsequent operations.
db.table('user').insert({'name':'think_sql3','score':100})
```
#### sql_helper for mysql
> [Ref:hcymysql/sql_helper](https://github.com/hcymysql/sql_helper)
```python
from think_sql import DB
from think_sql.mysql.sql_helper import help
db_dsn = "root:'password'@127.0.0.1:3306/database"
with DB(db_dsn) as db:
sql = "slow query sql"
help(db, sql)
```
> result
```shell
1) 输入的SQL语句是：
----------------------------------------------------------------------------------------------------
SELECT *
FROM hy_cabrecs
WHERE finished_count > 0
----------------------------------------------------------------------------------------------------
2) EXPLAIN执行计划:
+------+---------------+------------+--------------+--------+-----------------+-------+-----------+-------+--------+------------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+======+===============+============+==============+========+=================+=======+===========+=======+========+============+=============+
| 1 | SIMPLE | hy_cabrecs | None | ALL | None | None | None | None | 14422 | 33.33 | Using where |
+------+---------------+------------+--------------+--------+-----------------+-------+-----------+-------+--------+------------+-------------+
3) 索引优化建议：
----------------------------------------------------------------------------------------------------
取出表 【hy_cabrecs】 where条件字段 【finished_count】 100000 条记录，重复的数据有：【16093】 条，没有必要为该字段创建索引。
【hy_cabrecs】 表，无需添加任何索引。
```
#### parse for mysql
- parse alter sql
```python
from think_sql.mysql import parse
sql = """
alter table
slow_log_test
add
iii int not null default 0 comment 'a';
"""
print(parse.alter_sql(sql))
```
result:
```json
{
"table": "slow_log_test",
"field": "iii",
"field_type": "int",
"is_null": "NOT NULL",
"default": "0",
"comment": "'a'",
"after": ""
}
```
## Development
### poetry 包管理器
[官网](https://python-poetry.org/)
[Python 包管理之 poetry 的使用](https://blog.csdn.net/zhoubihui0000/article/details/104937285)
[Python 包管理之 poetry 基本使用](https://zhuanlan.zhihu.com/p/110721747)
```
# 配置虚拟环境在项目目录下
poetry config virtualenvs.path true
# 安装依赖
poetry install
# 进入虚拟环境
poetry shell
```
### poetry command
| 名称 | 功能 |
| ------- | ---------------------------------------------------------- |
| new | 创建一个项目脚手架，包含基本结构、pyproject.toml 文件 |
| init | 基于已有的项目代码创建 pyproject.toml 文件，支持交互式填写 |
| install | 安装依赖库 |
| update | 更新依赖库 |
| add | 添加依赖库 |
| remove | 移除依赖库 |
| show | 查看具体依赖库信息，支持显示树形依赖链 |
| build | 构建 tar.gz 或 wheel 包 |
| publish | 发布到 PyPI |
| run | 运行脚本和代码 |
## unit test
```
pytest --cov --cov-report=html
```
## publish
```
poetry build
poetry config pypi-token.pypi "your pypi.org api token"
poetry publish -n
```
Raw data
{
"_id": null,
"home_page": "https://github.com/hbh112233abc/think-sql",
"name": "think-sql",
"maintainer": null,
"docs_url": null,
"requires_python": "<4.0,>=3.8",
"maintainer_email": null,
"keywords": "sql, think-sql, DB, Table, mysql, \u8fbe\u68a6",
"author": "hbh112233abc",
"author_email": "hbh112233abc@163.com",
"download_url": "https://files.pythonhosted.org/packages/82/e3/a649af6f09b52c73ebc382efb66ed722c5aba1f51606972b17749dcd2d17/think-sql-0.7.8.tar.gz",
"platform": null,
"description": "# ThinkSQL \u7c7b\u4f3c ThinkPHP \u7684\u6570\u636e\u5e93\u5f15\u64ce\n\n## Drivers\n\n- [x] MySQL\n- [x] \u8fbe\u68a6(DM8)\n\n## Install\n\n- use mysql\n\n```\npip install think_sql[mysql]\n```\n\n- use \u8fbe\u68a6(DM8)\n\n```\npip install think_sql[dm]\n```\n\n## How to use\n\n### 1. simple demo\n\n> Database: `test` Table: `user`\n\n- example dict params\n\n```python\nfrom think_sql import DB\n\nconfig = {\n 'type': 'mysql',\n 'host': '127.0.0.1',\n 'port': 3306,\n 'user': 'root',\n 'password': 'root',\n 'database': 'test',\n}\n\nwith DB(config) as db:\n data = db.table('user').where('id',1).find()\n print(data)\n\n```\n\n- example dsn str\n\n```python\nfrom think_sql import DB\n\nwith DB(\"root:'root'@127.0.0.1:3306/test\") as db:\n data = db.table('user').where('id',1).find()\n print(data)\n\n```\n\n- example DBConfig\n\n```python\nfrom think_sql import DB\nfrom think_sql.tool.util import DBConfig\nconfig = DBConfig(\n type='mysql',\n host='127.0.0.1',\n port=3306,\n user='root',\n password='root',\n database='test',\n)\nwith DB(config) as db:\n data = db.table('user').where('id',1).find()\n print(data)\n\n```\n\nresult\n\n```json\n{\n \"id\": 1,\n \"username\": \"hbh112233abc\",\n \"age\": \"36\",\n \"address\": \"FUJIAN.XIAMEN\"\n}\n```\n\n- use `db` function\n\n```python\nfrom think_sql import db\n\nconfig = {\n 'type': 'mysql',\n 'host': '127.0.0.1',\n 'port': 3306,\n 'user': 'root',\n 'password': 'root',\n 'database': 'test',\n}\n\nconn = db(config)\ndata = conn.table('user').where('id',1).find()\nprint(data)\nconn.close()\n```\n\n### 2. Introduction\n\n#### DB\n\n- **init**(config:Union[str,dict,DBConfig],params={})\n\n init database, return DB instance\n\n - config:Union[str,dict,DBConfig]\n - str: `type://user:'password'@host:port/database`\n - dict: `{'type':'mysql','host':'127.0.0.1','port':3306,'user':'root','password':'root','database':'test'}`\n - DBConfig: `DBConfig(type='mysql',host='127.0.0.1',port=3306,user='root',password='root',database='test')`\n - params:dict pymysql connect other params\n\n- connect()\n connect database use **init** params\n\n- table(table_name):Table\n return class Table <think_sql.tool.interface.TableInterface>\n\n- check_connected():bool\n check connected, try reconnect database\n\n- query(sql,params=())\n query sql return cursor.fetchall List[dict]\n\n- execute(sql,params=())\n execute sql write operate(ex:insert,update,delete,...)\n\n#### Table\n\n- **init**(connector: Connection,cursor: Cursor,table_name: str,debug: bool = True)\n\n- init()\n initialize query condition\n\n- debug(flag=True)\n set debug flag\n\n- set_cache_storage(storage: CacheStorage)\n set cache storage ex: Redis\n\n- cache(key: str = None, expire: int = 3600)\n use cache at query\n\n- cursor(sql: str, params: list = []) -> Cursor\n return cursor object\n\n- get_last_sql() -> str\n return last sql string\n\n- get_lastid() -> str\n return last row id\n\n- get_rowcount() -> int\n return affect rows count\n\n- fetch_sql(flag: bool = True)\n set fetch sql flag,if flag = True then `query` and `execute` will only return sql\n\n- build_sql(operation: str, params: list = []) -> str\n return build sql\n\n- query(sql: str, params: list = []) -> list\n execute read operation sql and return cursor.fetchall()\n when `fetch_sql`=True then return sql and not execute the sql\n\n- execute(sql: str, params: list = []) -> int\n execute write operation sql and return affect rows count\n when `fetch_sql`=True then return sql and not execute the sql\n\n- where(field: Union[str, list, tuple], symbol: str = '', value: Any = None)\n set query conditions, support multipe use\n\n > where(field,value)\n\n ```sql\n where field = value\n ```\n\n > where(field,symbol,value)\n\n ```sql\n where field symbol value\n ```\n\n ```python\n where(\n [\n [field1,symbol1,value1],\n [field2,symbol2,value2]\n ]\n )\n ```\n\n ```sql\n where field1 symbol1 value1 and field2 symbol2 value2\n ```\n\n > where(field1,symbol1,value1).where(field2,symbol2,value2)\n\n ```sql\n where field1 symbol1 value1 and field2 symbol2 value2\n ```\n\n - symbol\n\n | symbol | another | demo |\n | ---------- | ------------------------ | --------------------------------------------------------------------------------------- |\n | `=` | `eq`,`=` | where('id','=',1) |\n | `<>` | `neq`, `!=`, `<>` | where('id','<>',1) |\n | `>` | `gt`,`>` | where('id','>',1) |\n | `>=` | `egt`,`>=` | where('id','>=',1) |\n | `<` | `lt`, `<` | where('id','<',1) |\n | `<=` | `elt`,`<=` | where('id','<=',1) |\n | `in` | `in`,`not in` | where('id','in',[1,2,3]) |\n | `between` | `between`,`not between` | where('id','between',[1,5]) where('id','between','1,5') where('id','between','1 and 5') |\n | `like` | `like`, `not like` | where('name','like','%hbh%') |\n | `null` | `is null`,`null` | where('remark','is null') |\n | `not null` | `is not null`,`not null` | where('remark','is not null') |\n | `exists` | `exists`, `not exists` | where('remark','exists') |\n | `exp` | `exp` | where('id','exp','in (1,2,3)') |\n\n- where_or(field: Union[str, list], symbol: str = '', value: Any = None)\n\n > where('id',1).where_or('id',5)\n\n ```\n where id = 1 or id = 5\n ```\n\n- limit(start: int, step: int = None)\n LIMIT start,step\n\n- page(index: int = 1, size: int = 20)\n LIMIT index\\*size-1,size\n\n- order(field: str, sort: str = 'asc')\n ORDER BY field sort\n\n- group(field:str)\n GROUP BY field\n\n- distinct(field:str)\n SELECT DISTINCT field\n\n- field(fields: Any, exclude: bool = False)\n SELECT fields\n if `exclude`=True then select the fields of table (exlude:`fields`)\n\n- select(build_sql: bool = False) -> list\n return select query result\n if `build_sql`=True then return sql\n\n- find()\n return select ... limit 1\n\n- value(field: str)\n return the field of first row\n\n- column(field: str,key: str = '')\n\n > column('name')\n\n return ['hbh','mondy']\n\n > column('name,score')\n\n return [{'hbh':80},{'mondy':88}]\n\n > column('score','name')\n\n return {'hbh':80, 'mondy':88}\n\n > column('id,score','name')\n\n return {\n 'hbh':{'id':1,'score':80},\n 'mondy':{'id':2,'score':88}\n }\n\n- alias(short_name: str = '')\n set alias table_name\n\n- join(table_name: str, as_name: str = '', on: str = '', join: str = 'inner', and_str: str = '')\n - `table_name` join table_name\n - `as_name` alias short_table_name for `table_name`\n - `on` join condition\n - `join` join type in 'INNER', 'LEFT', 'RIGHT', 'FULL OUTER'\n - `and_str` and condition\n demo\n ```python\n db.table('table1').alias('a').join(\n 'table2','b','a.id=b.a_id','left'\n ).join(\n 'table3','c','c.a_id=a.id'\n ).field(\n 'a.id,a.name,b.id as b_id,b.score,c.id as c_id,c.remark'\n ).where(\n 'a.id',1\n ).find()\n ```\n sql\n ```sql\n SELECT\n a.id,\n a.name,\n b.id AS b_id,\n b.score,\n c.id AS c_id,\n c.remark\n FROM\n table1 AS a\n LEFT JOIN table2 AS b ON a.id = b.a_id\n INNER JOIN table3 AS c ON c.a_id = a.id\n WHERE\n a.id = 1\n LIMIT 1\n ```\n- union(sql1: str, sql2: str, union_all: bool = False)\n union sql1 and sql2\n\n - union_all if union_all is True then `UNION ALL`\n\n _demo_\n\n ```python\n sql1 = db.table('table1').field('name,score').where('status',1).select(build_sql=True)\n sql2 = db.table('table2').field('name,score').where('status',1).select(build_sql=True)\n\n result = db.table().union(sql1,sql2).where('score','>',60).select()\n ```\n\n _sql_\n\n ```sql\n SELECT\n *\n FROM\n ( SELECT `name`, `score` FROM table1 WHERE `status` = 1 )\n UNION\n ( SELECT `name`, `score` FROM table2 WHERE `status` = 1 )\n WHERE\n score > 60\n ```\n\n- insert(data: Union[dict, List[dict]], replace: bool = False) -> int\n insert data to database\n\n - `data` dict: insert one record; list: insert multiple records\n - `replace` bool if `replace` is True then use `REPLACE INTO`\n\n _demo_\n insert one record\n\n ```python\n db.table('table1').insert({'name':'test','score':100})\n ```\n\n ```sql\n INSERT INTO table1 (`name`, `score`) VALUES ('test', 100)\n ```\n\n insert multiple records\n\n ```python\n db.table('table1').insert([{'name':'test','score':100},{'name':'test2','score':101}])\n ```\n\n ```sql\n INSERT INTO table1 (`name`, `score`) VALUES ('test', 100), ('test2', 101)\n ```\n\n replace mode\n\n ```python\n db.table('table1').insert({'id':1,'name':'test','score':100},replace=True)\n ```\n\n ```sql\n REPLACE INTO table1 (`id`, `name`, `score`) VALUES (1,'test', 100)\n ```\n\n- update(data: dict, all_record: bool = False) -> int\n update data\n\n - `data` dict you want update data\n - `all_record` bool if `all_record` is False then you must set update condition; if you want to update all records then you need set `all_record` = True\n\n- delete(all_record: bool = False) -> int\n delete record\n\n - `all_record` bool if `all_record` is False then you must set delete condition; if you want to delete all records then you need set `all_record` = True\n\n- inc(field: str, step: Union[str, int, float] = 1) -> int\n\n increase `field` +`step`\n\n- dec(field: str, step: int = 1) -> int\n\n decrease `field` -`step`\n\n- max(field: str) -> Union[int, float]\n\n get the max value of `field`\n\n- sum(field: str) -> Union[int, float, Decimal]\n\n get the sum value of `field`\n\n- avg(field: str) -> Union[int, float, Decimal]\n\n get the avg value of `field`\n\n- count(field: str = '\\*') -> int\n\n get the count of records\n\n- copy_to(new_table: str = None, create_blank_table: bool = False) -> int\n\n copy data to `new_table`\n\n - `new_table` if `new_table` is None then `new_table` will auto set like `{table_name}_copy`\n - `create_blank_table` bool if `create_blank_table` is True then only create a blank table like current table.\n\n _demo_\n\n ```sql\n db.table('user').field(\n 'name,score'\n ).where(\n 'score','>',60\n ).copy_to('good_boy')\n ```\n\n _sql_\n\n ```sql\n SELECT\n `name`,\n `score`\n INTO `good_boy`\n FROM\n `user`\n WHERE\n score > 60\n ```\n\n- insert_to(new_table: str, fields: Union[str, list, tuple] = None) -> int\n\n ```sql\n INSERT INTO {new_table} SELECT {select_fields} FROM {table} {join} WHERE {where}{group}{order}{limit}\n ```\n\n- exists(self) -> bool\n\n check record exists with some query conditions, it use `SELECT 1 FROM {table} {join} WHERE {where} LIMIT 1`\n\n- batch_update(data:List[dict],key:str) -> int\n\n batch update multiple records\n\n _demo_\n\n ```python\n data = [\n {'id':1,'score':66},\n {'id':2,'score':59},\n {'id':3,'score':86},\n {'id':4,'score':90},\n ]\n db.table('user').batch(data,key='id')\n ```\n\n _sql_\n\n ```sql\n update `user` set score = 66 where id = 1;\n update `user` set score = 59 where id = 2;\n update `user` set score = 86 where id = 3;\n update `user` set score = 90 where id = 4;\n ```\n\n#### support transaction\n\n```python\nfrom think_sql import DB\ndb_dsn = \"root:'password'@127.0.0.1:3306/database\"\nwith DB(db_dsn) as db:\n # result: insert two records into database\n with db.start_trans():\n db.table('user').insert({'name':'think_sql1','score':98})\n db.table('user').insert({'name':'think_sql2','score':99})\n # result: nothing inserted\n with db.start_trans():\n db.table('user').insert({'name':'think_sql1','score':98})\n db.table('user').insert({'name':'think_sql2','score':99})\n raise Exception('error')\n\n # The above operation does not affect subsequent operations.\n db.table('user').insert({'name':'think_sql3','score':100})\n```\n\n#### sql_helper for mysql\n\n> [Ref:hcymysql/sql_helper](https://github.com/hcymysql/sql_helper)\n\n```python\nfrom think_sql import DB\nfrom think_sql.mysql.sql_helper import help\n\ndb_dsn = \"root:'password'@127.0.0.1:3306/database\"\nwith DB(db_dsn) as db:\n sql = \"slow query sql\"\n help(db, sql)\n```\n\n> result\n\n```shell\n1) \u8f93\u5165\u7684SQL\u8bed\u53e5\u662f\uff1a\n----------------------------------------------------------------------------------------------------\nSELECT *\nFROM hy_cabrecs\nWHERE finished_count > 0\n----------------------------------------------------------------------------------------------------\n2) EXPLAIN\u6267\u884c\u8ba1\u5212:\n+------+---------------+------------+--------------+--------+-----------------+-------+-----------+-------+--------+------------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+======+===============+============+==============+========+=================+=======+===========+=======+========+============+=============+\n| 1 | SIMPLE | hy_cabrecs | None | ALL | None | None | None | None | 14422 | 33.33 | Using where |\n+------+---------------+------------+--------------+--------+-----------------+-------+-----------+-------+--------+------------+-------------+\n3) \u7d22\u5f15\u4f18\u5316\u5efa\u8bae\uff1a\n----------------------------------------------------------------------------------------------------\n\u53d6\u51fa\u8868 \u3010hy_cabrecs\u3011 where\u6761\u4ef6\u5b57\u6bb5 \u3010finished_count\u3011 100000 \u6761\u8bb0\u5f55\uff0c\u91cd\u590d\u7684\u6570\u636e\u6709\uff1a\u301016093\u3011 \u6761\uff0c\u6ca1\u6709\u5fc5\u8981\u4e3a\u8be5\u5b57\u6bb5\u521b\u5efa\u7d22\u5f15\u3002\n \u3010hy_cabrecs\u3011 \u8868\uff0c\u65e0\u9700\u6dfb\u52a0\u4efb\u4f55\u7d22\u5f15\u3002\n```\n\n#### parse for mysql\n\n- parse alter sql\n\n ```python\n from think_sql.mysql import parse\n sql = \"\"\"\n alter table\n slow_log_test\n add\n iii int not null default 0 comment 'a';\n \"\"\"\n\n print(parse.alter_sql(sql))\n ```\n\n result:\n\n ```json\n {\n \"table\": \"slow_log_test\",\n \"field\": \"iii\",\n \"field_type\": \"int\",\n \"is_null\": \"NOT NULL\",\n \"default\": \"0\",\n \"comment\": \"'a'\",\n \"after\": \"\"\n }\n ```\n\n## Development\n\n### poetry \u5305\u7ba1\u7406\u5668\n\n[\u5b98\u7f51](https://python-poetry.org/)\n\n[Python \u5305\u7ba1\u7406\u4e4b poetry \u7684\u4f7f\u7528](https://blog.csdn.net/zhoubihui0000/article/details/104937285)\n\n[Python \u5305\u7ba1\u7406\u4e4b poetry \u57fa\u672c\u4f7f\u7528](https://zhuanlan.zhihu.com/p/110721747)\n\n```\n# \u914d\u7f6e\u865a\u62df\u73af\u5883\u5728\u9879\u76ee\u76ee\u5f55\u4e0b\npoetry config virtualenvs.path true\n# \u5b89\u88c5\u4f9d\u8d56\npoetry install\n# \u8fdb\u5165\u865a\u62df\u73af\u5883\npoetry shell\n```\n\n### poetry command\n\n| \u540d\u79f0 | \u529f\u80fd |\n| ------- | ---------------------------------------------------------- |\n| new | \u521b\u5efa\u4e00\u4e2a\u9879\u76ee\u811a\u624b\u67b6\uff0c\u5305\u542b\u57fa\u672c\u7ed3\u6784\u3001pyproject.toml \u6587\u4ef6 |\n| init | \u57fa\u4e8e\u5df2\u6709\u7684\u9879\u76ee\u4ee3\u7801\u521b\u5efa pyproject.toml \u6587\u4ef6\uff0c\u652f\u6301\u4ea4\u4e92\u5f0f\u586b\u5199 |\n| install | \u5b89\u88c5\u4f9d\u8d56\u5e93 |\n| update | \u66f4\u65b0\u4f9d\u8d56\u5e93 |\n| add | \u6dfb\u52a0\u4f9d\u8d56\u5e93 |\n| remove | \u79fb\u9664\u4f9d\u8d56\u5e93 |\n| show | \u67e5\u770b\u5177\u4f53\u4f9d\u8d56\u5e93\u4fe1\u606f\uff0c\u652f\u6301\u663e\u793a\u6811\u5f62\u4f9d\u8d56\u94fe |\n| build | \u6784\u5efa tar.gz \u6216 wheel \u5305 |\n| publish | \u53d1\u5e03\u5230 PyPI |\n| run | \u8fd0\u884c\u811a\u672c\u548c\u4ee3\u7801 |\n\n## unit test\n\n```\npytest --cov --cov-report=html\n```\n\n## publish\n\n```\npoetry build\npoetry config pypi-token.pypi \"your pypi.org api token\"\npoetry publish -n\n```\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "ThinkSQL link think-orm(ThinkPHP)",
"version": "0.7.8",
"project_urls": {
"Documentation": "https://github.com/hbh112233abc/think-sql",
"Homepage": "https://github.com/hbh112233abc/think-sql",
"Repository": "https://github.com/hbh112233abc/think-sql"
},
"split_keywords": [
"sql",
" think-sql",
" db",
" table",
" mysql",
" \u8fbe\u68a6"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "392fe22b90d1d72bc1a431fe2db3d80aba49fd4628efa6954aa34b47774054dd",
"md5": "f7babc7b27711cb41c1f2c9a46316c73",
"sha256": "369d7fdb283cbaef9709ef3339fc601088dd49e1e403b2fc04918d87cfed4049"
},
"downloads": -1,
"filename": "think_sql-0.7.8-py3-none-any.whl",
"has_sig": false,
"md5_digest": "f7babc7b27711cb41c1f2c9a46316c73",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": "<4.0,>=3.8",
"size": 38565,
"upload_time": "2024-08-01T11:36:07",
"upload_time_iso_8601": "2024-08-01T11:36:07.707236Z",
"url": "https://files.pythonhosted.org/packages/39/2f/e22b90d1d72bc1a431fe2db3d80aba49fd4628efa6954aa34b47774054dd/think_sql-0.7.8-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "82e3a649af6f09b52c73ebc382efb66ed722c5aba1f51606972b17749dcd2d17",
"md5": "71705e35b90df8cc6b4b2490989e6999",
"sha256": "929b5697fb8c3a3726be2b8acbba99cb0bd384ca028d703116982f001d9e1700"
},
"downloads": -1,
"filename": "think-sql-0.7.8.tar.gz",
"has_sig": false,
"md5_digest": "71705e35b90df8cc6b4b2490989e6999",
"packagetype": "sdist",
"python_version": "source",
"requires_python": "<4.0,>=3.8",
"size": 39276,
"upload_time": "2024-08-01T11:36:04",
"upload_time_iso_8601": "2024-08-01T11:36:04.404086Z",
"url": "https://files.pythonhosted.org/packages/82/e3/a649af6f09b52c73ebc382efb66ed722c5aba1f51606972b17749dcd2d17/think-sql-0.7.8.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-08-01 11:36:04",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "hbh112233abc",
"github_project": "think-sql",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "think-sql"
}
JSON
