1. python操作資料庫
pymysql
PyMSQL是一個純的python的MySQL客戶端，
pip install PyMySQL使用步驟
- 創建連接(修高速公路)
- 創建游標(運輸車)
- 執行sql(提貨單)
- 獲取結果(卸貨)
- 關閉游標(車退掉)
- 關閉連接(路也斷掉)
import pymysql
from 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 pymysql
db_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()
本文來自博客園，作者：奈非天，轉載請注明原文鏈接：https://www.cnblogs.com/Nephalem-262667641/p/17310424.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/549893.html
標籤：其他