pip install pymysql
測試資料表 student.students:
+----+--------+
| Id | Name |
+----+--------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie|
+----+--------+
#!/usr/bin/python
# coding: utf-8
import pymysql
class MysqlHelper:
def __init__(self, host='localhost', user='root', passwd='password', db='student'):
self.conn = pymysql.connect(host=host, user=user, passwd=passwd, db=db, charset='utf8mb4')
self.cur = self.conn.cursor()
def execute_update(self, sql):
"""執行 INSERT/UPDATE/DELETE"""
try:
affected = self.cur.execute(sql)
self.conn.commit()
return affected
except Exception as e:
self.conn.rollback()
raise e
def execute_delete(self, ids_str):
"""批量刪除,ids_str 如 '1 3 5'"""
affected = 0
for id_str in ids_str.split():
affected += self.cur.execute("DELETE FROM students WHERE Id = %s", (int(id_str),))
self.conn.commit()
return affected
def execute_query(self, sql):
"""執行 SELECT,返回游標可迭代"""
self.cur.execute(sql)
return self.cur
def close(self):
self.cur.close()
self.conn.close()
# 互動式測試主程式
if __name__ == '__main__':
helper = MysqlHelper()
print("選擇操作:1=修改 2=新增 3=查詢 4=刪除 (q=退出)")
while True:
choice = input("請輸入選項: ").strip()
if choice == 'q':
print("結束操作")
break
try:
if choice == '1':
sql = input("輸入 UPDATE 語句: ")
helper.execute_update(sql)
print("更新成功")
elif choice == '2':
sql = input("輸入 INSERT 語句: ")
helper.execute_update(sql)
print("新增成功")
elif choice == '3':
sql = input("輸入 SELECT 語句: ")
cur = helper.execute_query(sql)
for row in cur:
print("Id={} Name={}".format(row[0], row[1]))
elif choice == '4':
ids = input("輸入要刪除的 ID(空格分隔): ")
helper.execute_delete(ids)
print("刪除成功")
else:
print("無效選項")
except Exception as e:
print("操作失敗: {}".format(e))
helper.close()
| 實踐 | 代碼體現 |
|---|---|
| 參數化查詢防注入 | execute("DELETE ... WHERE Id = %s", (id,)) |
| 事務控制 | commit()/rollback() |
| 資源釋放 | close() 在 finally 或上下文管理器 |
| 字符集指定 | charset='utf8mb4' |
| 異常處理 | try/except 捕獲具體異常 |
pymysql.connect(..., max_connections=10) 或使用 DBUtils.PooledDBcursor=pymysql.cursors.DictCursor 返回字典而非元組executemany(sql, params_list)sshtunnel 庫建立隧道連接遠程 MySQLaiomysql 配合 asyncio# 新增
INSERT INTO students (Name) VALUES ('David')
# 查詢顯示 Id=4 Name=David
# 更新
UPDATE students SET Name='David Updated' WHERE Id=4
# 查詢顯示 Name=David Updated
# 刪除
DELETE FROM students WHERE Id=4
# 查詢不再顯示 Id=4