Python/ target=_blank class=infotextkey>Python操作數(shù)據(jù)庫
pyMySQL
PyMSQL是一個純的python的MySQL客戶端。
pip install PyMySQL
使用步驟
-
創(chuàng)建連接(修高速公路)
-
創(chuàng)建游標(運輸車)
-
執(zhí)行sql(提貨單)
-
獲取結(jié)果(卸貨)
-
關(guān)閉游標(車退掉)
-
關(guān)閉連接(路也斷掉)
import pymysqlfrom pymysql.cursors import DictCursor# 1.創(chuàng)建連接(修高速公路)conn = pymysql.connect(host='127.0.0.1',user='admin',password='12345',port=3306,db='mysql',charset='utf8')# 2. 創(chuàng)建游標(運輸車)# cursor = conn.cursor() # 返回元組cursor = conn.cursor(DictCursor) # 返回字典# 3. 執(zhí)行sql(提貨單)sql ="select count(1) total, (case when t1.status = 1 then '待整改' when t1.status = 2 then '待復(fù)查' 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. 獲取結(jié)果(卸貨)res1 = cursor.fetchone() # 一次卸載一條res2 = cursor.fetchmany(3) # 一次卸載指定的條數(shù)res3 = cursor.fetchall() # 一次獲取所有# 5. 關(guān)閉游標(車退掉)cursor.close()# 6. 關(guān)閉連接(路也斷掉)conn.close()[{'total': 30, 'orderStatus': '整改完成'}, {'total': 5, 'orderStatus': '待整改'}]
更新數(shù)據(jù)
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: # 上下文管理--自動關(guān)閉游標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默認開啟事務(wù)conn.commit()except Exception as e:# 回滾conn.rollback()finally:conn.close()
