最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

【天天时快讯】pymysql操作数据库入门

来源：博客园


(资料图片)

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()

关键词：