python pymysql 链式操作 mysql 数据库

背景

---

PyMySql 是使用 python 来操作 MySql 的常用库，但 pymysql 只是实现了基础功能，CURD 都需求自己拼接 sql 来执行，相当不方便。 PyMySql 官方文档：https://pymysql.readthedocs.io/en/latest/user/examples.html#crud

Chain-PyMySQL

---

一个基于 PyMySQL 重新封装的 MySql 操作库，增加链式操作，方便快捷进行 CURD 操作

官方文档

---

https://github.com/Tiacx/chain-pymysql

注：可忽略下述内容，直接跳转查看官方文档，官方文档更详细，功能更全！！

安装Chain-PyMySql

---

pip install chain-pymysql

导包

---

from chain_pymysql import imysql

连接数据库

---

imysql.connect({
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'root',
    'database': 'test'
})

主要功能

---

插入一行记录

insert_id = imysql.table('table1').insert_one({'id': 1, 'name': '张三'})

插入多行记录

effected_rows = imysql.table('table1').insert_many([
    {'id': 2, 'name': '李四'},
    {'id': 3, 'name': '王五'},
    {'id': 4, 'name': '赵六'}
])

删除记录

effected_rows = imysql.table('table1').delete({'id': 1})

更新一条记录

effected_rows = imysql.table('table1').update_one({'id': 3}, {'name': '王六'})

更新多条记录

effected_rows = imysql.table('table1').update_many('id IN (3,4)', {'name': '匿名'})

查询数据

注：fetch=True 返回 list，fetch=False（默认）返回 cursor，可用于迭代

# 普通查询
results = imysql.table('table1').where({'id': 3}).all(fetch=True)
# IN 查询
results = imysql.table('table1').where({'id': ['in', (3, 4)]}).all(fetch=True)
# LIKE 模糊查询
results = imysql.table('table1').where({'name': ('like', '张%')}).all(fetch=True)
# BETWEEN 查询
results = imysql.table('table1').where({'id': ['between', (3, 4)]}).all(fetch=True)

# 大于等于
results = imysql.table('table1').where({'id': ['>=', 3]}).all(fetch=True)
# 小于
results = imysql.table('table1').where({'id': ['<', 4]}).all(fetch=True)
# 不等于
results = imysql.table('table1').where({'id': ['<>', 3]}).all(fetch=True)
# 不为空
results = imysql.table('table1').where({'id': ['<>', '']}).all(fetch=True)
# NULL
results = imysql.table('table1').where({'name': ['is', None]}).all(fetch=True)
# NOT NULL
results = imysql.table('table1').where({'name': ['is not', None]}).all(fetch=True)
# NOT IN
results = imysql.table('table1').where({'id': ['not in', (3, 4)]}).all(fetch=True)
# NOT LIKE
results = imysql.table('table1').where({'name': ['not like', '张%']}).all(fetch=True)

查询构建器

---

选择字段

one = imysql.table('table1').select('id').where('id=3').one() 

联表查询 join

# 默认 LEFT JOIN
results = (
    imysql.table('table1', alias='t1')
    .select('t1.id,t1.name,t2.age')
    .join('table2', alias='t2', on='t1.id=t2.id')
    .all(fetch=True)
)

# RIGHT JOIN
results = (
    imysql.table('table1 t1')
    .select(['t1.id', 't1.name', 't2.age'])
    .join('table2 t2', on='t1.id=t2.id', how='right')
    .all(fetch=True)
)

# INNER JOIN
results = (
    imysql.table('table1 t1')
    .join('table2 t2', on='t1.id=t2.id', how='inner')
    .all(fetch=True)
)

分组及排序 group_by order_by

results = (
    imysql.table('table2')
    .select('age, count(*) as num')

    # 可以使用字符串或list
    .group_by('age')
    # .group_by(['age'])

    # 可以使用字符串或list
    # .order_by('age asc, num desc')
    # .order_by(['age asc', 'num desc'])
    # .order_by(['age', 'num'], ascending=True)
    .order_by(['age', 'num'], ascending=[True, False])

    .all(fetch=True)
)

结果筛选 having

results = (
    imysql.table('table2')
    .select('age, count(*) as num')
    .group_by('age')
    .having('num > 1')
    .order_by('num desc')
    .all(fetch=True)
)

分页查询 skip limit

results = imysql.table('table1').order_by('id asc').skip(1).limit(3).all(fetch=True)

执行原生SQL

sql = 'SELECT * FROM table1 WHERE id=%s'
# 解析多行
results = imysql.execute(sql, (3,)).all(fetch=True)
# 解析单行
one = imysql.execute(sql, (3,)).one()
# 其他操作（详情请看 github）
names = imysql.execute(sql, (3,)).index('id', 'name')

使用助手函数来拼接SQL

gen_condition、gen_order_by、gen_limit 等

condition = dict()
for k, v in request.GET.items():
    if k in ['id', 'name']:
        condition[f't1.{k}'] = v
    elif k in ['age']:
        condition[f't2.{k}'] = v

where = imysql.gen_condition(condition)
order_by = imysql.gen_order_by(request.GET.get('order'), request.GET.get('asc') == '1')
page = int(request.GET.get('page', 1))
size = int(request.GET.get('size', 10))
limit = imysql.gen_limit(skip=(page-1)*size, limit=size)

sql = f'SELECT t1.`name`, avg(t2.age) AS age FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE {where} GROUP BY t1.`name` {order_by}{limit}'
results = imysql.execute(sql, fetch=True)
for item in results:
    print(item)

更多功能

---

更多功能，包括但不限于：多数据切换、事务支持、获取单行单值单列、防注入及异常处理等等功能

请参考官方文档： https://github.com/Tiacx/chain-pymysql

---

完。