(资料图片仅供参考)

1. python操作数据库

pymysql

PyMSQL是一个纯的python的MySQL客户端。

pip install PyMySQL

使用步骤

1. 创建连接(修高速公路)
2. 创建游标(运输车)
3. 执行sql(提货单)
4. 获取结果(卸货)
5. 关闭游标(车退掉)
6. 关闭连接(路也断掉)

import pymysqlfrom pymysql.cursors import DictCursor# 1.创建连接(修高速公路)conn = pymysql.connect(    host="127.0.0.1",    user="admin",    password="12345",    port=3306,    db="mysql",    charset="utf8")# 2. 创建游标(运输车)# cursor = conn.cursor()  # 返回元组cursor = conn.cursor(DictCursor)  # 返回字典# 3. 执行sql(提货单)sql ="select count(1) total, (case when t1.status = 1 then "待整改" when t1.status = 2 then "待复查" when t1.status = 3 then "整改完成" else "未知类型" end) orderStatus from ibs_ai_iot.ai_rectification_main t1 left join ibs_ai_iot.work_order t3 on t1.id = t3.rectification_id where t1.project_id = 103672 and t1.delete_flag = 0 and t3.is_delete = 0 group by t1.status order by orderStatus desc;",res = cursor.execute(sql)print(res)# 4. 获取结果(卸货)res1 = cursor.fetchone() # 一次卸载一条res2 = cursor.fetchmany(3) # 一次卸载指定的条数res3 = cursor.fetchall() # 一次获取所有# 5. 关闭游标(车退掉)cursor.close()# 6. 关闭连接(路也断掉)conn.close()

[{"total": 30, "orderStatus": "整改完成"}, {"total": 5, "orderStatus": "待整改"}]

更新数据

import pymysqldb_config = {    "host": "127.0.0.1",    "user": "admin",    "password": "123456",    "port": 3306,    "db": "mysql",    "charset": "utf8"}conn = pymysql.connect(**db_config)try:    with conn.cursor() as cursor: # 上下文管理--自动关闭游标        sql1 = "UPDATE table t1  set t1.user_name="勇哥" where t1.username="h1" "        sql2 = "UPDATE table t1  set t1.user_name="勇哥2" where t1.username="h2" "        cursor.execute(sql1)        cursor.execute(sql2)        # pymysql默认开启事务        conn.commit()except Exception as e:    # 回滚    conn.rollback()finally:    conn.close()