pip install mysqlclient(MySQLdb 分支,支援 Python 3).whl(pip install mysqlclient‑1.4.6‑cp38‑cp38‑win32.whl)import MySQLdb
import time
conn = MySQLdb.connect(
host="localhost", user="root", passwd="", db="test",
charset="utf8", use_unicode=True
)
cursor = conn.cursor()
# 新增
cursor.execute(
"INSERT INTO user(name, created) VALUES(%s, %s)",
("aaa", int(time.time()))
)
# 修改
cursor.execute("UPDATE user SET name=%s WHERE id=3", ("bbb",))
# 查詢
cursor.execute("SELECT * FROM user")
for row in cursor.fetchall():
print(row)
# 刪除
cursor.execute("DELETE FROM user WHERE name=%s", ("aaa",))
conn.commit()
cursor.close()
conn.close()
executemany
data = [
("title1", "singer1", "url1"),
("title2", "singer2", "url2"),
]
cursor.executemany(
"INSERT INTO cdinfo(title, singer, url) VALUES(%s, %s, %s)",
data
)
try:
cursor.execute("INSERT ...")
cursor.execute("UPDATE ...")
conn.commit()
except Exception:
conn.rollback()
raise
# -*- coding: utf-8 -*-charset="utf8mb4"(支援 Emoji)sys.setdefaultencoding('utf-8')(Python 3 不需要)my.cnf
[client]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
| 錯誤 | 原因 | 解決 |
|---|---|---|
DeprecationWarning: the sets module is deprecated | Python 2.6+ 棄用 sets 模組 | 升級 mysqlclient 或修改原始碼將 sets.ImmutableSet 改為 frozenset |
InterfaceError: (0, '') | 連線逾時/斷線 | 啟用 ping(True) 或連線池 |
MySQLdb / mysqlclient 是 Python 存取 MySQL 的經典選擇,掌握參數化查詢、批次操作、編碼設定三大核心,即能安全高效地處理資料庫互動。