--
前言
相較於 PHP，Python 因為可以自由選擇模組，所以就算是連接 MySQL 資料庫也有很多種方式，在此使用 MySQLdb, pymysql, mysql.connector 三種模組來比較程式以及效能的差異。
--
需求
- 因為沒有交易需求所以設定 autocommit 直接將寫入生效
- 為了程式方便維護，因此 SQL 都使用 named placeholders
- 使用迴圈新增 10000 筆資料再更新資料後計算所需時間
--
MySQLdb
安裝
|
1 |
pip3 install mysqlclient |
Python 程式碼
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
import MySQLdb import time db = MySQLdb.connect(host="192.168.6.121", user="user", password="password", database="database", autocommit=True) cursor = db.cursor(MySQLdb.cursors.DictCursor) start_time = time.time() for i in range(0, 10000): sql = " INSERT INTO Room (Game) VALUES ('') " cursor.execute(sql) sql = " UPDATE Room SET Game=Game+1 WHERE id=%(id)s " cursor.execute(sql, { 'id': i }) print(time.time() - start_time) # 28.631089210510254 sql = " SELECT * FROM Room WHERE id=%(id)s " cursor.execute(sql, { 'id': 1 }) print(cursor.rowcount) a = cursor.fetchone() print(a) |
--
PyMySQL
安裝
|
1 |
pip3 install PyMySQL |
Python 程式碼
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
import pymysql import time db = pymysql.connect(host="192.168.6.121", user="user", password="password", database="database", autocommit=True) cursor = db.cursor(pymysql.cursors.DictCursor) start_time = time.time() for i in range(0, 10000): sql = " INSERT INTO Room (Game) VALUES ('') " cursor.execute(sql) sql = " UPDATE Room SET Game=Game+1 WHERE id=%(id)s " cursor.execute(sql, { 'id': i }) print(time.time() - start_time) # 31.950004816055298 sql = " SELECT * FROM Room WHERE id=%(id)s " cursor.execute(sql, { 'id': 1 }) print(cursor.rowcount) a = cursor.fetchone() print(a) |
--
MySQL Connectors
安裝
|
1 |
pip3 install mysql-connector |
Python 程式碼
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
import mysql.connector import time cnx = mysql.connector.connect(user='user', password='password', host='192.168.6.121', port=3306, database='database', autocommit=True) cursor = cnx.cursor(dictionary=True, buffered=True) start_time = time.time() for i in range(0, 10000): sql = " INSERT INTO Room (Game) VALUES ('" + str(i) + "') " cursor.execute(sql) sql = " UPDATE Room SET Game=Game+1 WHERE id=%(id)s " cursor.execute(sql, { 'id': i }) print(time.time() - start_time) # 28.920193672180176 sql = " SELECT * FROM Room WHERE id=%(id)s " cursor.execute(sql, { 'id': 1 }) print(cursor.rowcount) a = cursor.fetchone() print(a) |
--
結論
可能多年前不同模組有效能或功能上的差異，可是現在 2024 年差異已經很小，所以在選擇上可以根據當時模組維護的狀況即可
--
1,815 total views, 5 views today