分享一個純 Python 實現的 MySQL 用戶端操作庫

PyMySQL 是一個純 Python 實現的 MySQL 用戶端操作庫，支援事務、預存程序、批量執行等。PyMySQL 遵循 Python 資料庫 API v2.0 規範，並包含了 pure-Python MySQL 用戶端庫。

安裝

pip install PyMySQL

建立資料庫連接

import pymysqlconnection = pymysql.connect(host='localhost',                             port=3306,                             user='root',                             password='root',                             db='demo',                             charset='utf8')

參數列表：

參數	描述
host	資料庫伺服器地址，預設 localhost
user	使用者名稱，預設為當前程式運行使用者
password	登入密碼，預設為空白字串
database	預設操作的資料庫
port	資料庫連接埠，預設為 3306
bind_address	當用戶端有多個網路介面時，指定串連到主機的介面。參數可以是主機名稱或IP地址。
unix_socket	unix 通訊端地址，區別於 host 串連
read_timeout	讀取資料逾時時間，單位秒，預設無限制
write_timeout	寫入資料逾時時間，單位秒，預設無限制
charset	資料庫編碼
sql_mode	指定預設的 SQL_MODE
read_default_file	Specifies my.cnf file to read these parameters from under the [client] section.
conv	Conversion dictionary to use instead of the default one. This is used to provide custom marshalling and unmarshaling of types.
use_unicode	Whether or not to default to unicode strings. This option defaults to true for Py3k.
client_flag	Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass	設定預設的遊標類型
init_command	當串連建立完成之後執行的初始化 SQL 陳述式
connect_timeout	連線逾時時間，預設 10，最小 1，最大 31536000
ssl	A dict of arguments similar to mysql_ssl_set()'s parameters. For now the capath and cipher arguments are not supported.
read_default_group	Group to read from in the configuration file.
compress	Not supported
named_pipe	Not supported
autocommit	是否自動認可，預設不自動認可，參數值為 None 表示以伺服器為準
local_infile	Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
max_allowed_packet	發送給伺服器的最大資料量，預設為 16MB
defer_connect	是否惰性串連，預設為立即串連
auth_plugin_map	A dict of plugin names to a class that processes that plugin. The class will take the Connection object as the argument to the constructor. The class needs an authenticate method taking an authentication packet as an argument. For the dialog plugin, a prompt(echo, prompt) method can be used (if no authenticate method) for returning a string from the user. (experimental)
server_public_key	SHA256 authenticaiton plugin public key value. (default: None)
db	參數 database 的別名
passwd	參數 password 的別名
binary_prefix	Add _binary prefix on bytes and bytearray. (default: False)

執行 SQL

• cursor.execute(sql, args) 執行單條 SQL

  # 擷取遊標cursor = connection.cursor()# 建立資料表effect_row = cursor.execute('''CREATE TABLE `users` (  `name` varchar(32) NOT NULL,  `age` int(10) unsigned NOT NULL DEFAULT '0',  PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8''')# 插入資料(元組或列表)effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)', ('mary', 18))# 插入資料(字典)info = {'name': 'fake', 'age': 15}effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%(name)s, %(age)s)', info)connection.commit()

• executemany(sql, args) 批量執行 SQL

  # 擷取遊標cursor = connection.cursor()# 批量插入effect_row = cursor.executemany(    'INSERT INTO `users` (`name`, `age`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE age=VALUES(age)', [        ('hello', 13),        ('fake', 28),    ])connection.commit()

注意：INSERT、UPDATE、DELETE 等修改資料的語句需手動執行connection.commit()完成對資料修改的提交。

擷取自增 ID

cursor.lastrowid

查詢資料

# 執行查詢 SQLcursor.execute('SELECT * FROM `users`')# 擷取單條資料cursor.fetchone()# 擷取前N條資料cursor.fetchmany(3)# 擷取所有資料cursor.fetchall()

遊標控制

所有的資料查詢操作均基於遊標，我們可以通過cursor.scroll(num, mode)控制遊標的位置。

cursor.scroll(1, mode='relative') # 相對當前位置移動cursor.scroll(2, mode='absolute') # 相對絕對位置移動

設定遊標類型

查詢時，預設返回的資料類型為元組，可以自訂設定傳回型別。支援5種遊標類型：

• Cursor: 預設，元群組類型

• DictCursor: 字典類型

• DictCursorMixin: 支援自訂的遊標類型，需先自訂才可使用

• SSCursor: 無緩衝元群組類型

• SSDictCursor: 無緩衝字典類型

無緩衝遊標類型，適用於資料量很大，一次性返回太慢，或者服務端頻寬較小時。源碼注釋：

Unbuffered Cursor, mainly useful for queries that return a lot of data, or for connections to remote servers over a slow network.

Instead of copying every row of data into a buffer, this will fetch rows as needed. The upside of this is the client uses much less memory, and rows are returned much faster when traveling over a slow network
or if the result set is very big.

There are limitations, though. The MySQL protocol doesn't support returning the total number of rows, so the only way to tell how many rows there are is to iterate over every row returned. Also, it currently isn't possible to scroll backwards, as only the current row is held in memory.

建立串連時，通過 cursorclass 參數指定類型：

connection = pymysql.connect(host='localhost',                             user='root',                             password='root',                             db='demo',                             charset='utf8',                             cursorclass=pymysql.cursors.DictCursor)

也可以在建立遊標時指定類型：

cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

交易處理

• 開啟事務

connection.begin()

• 提交修改

connection.commit()

• 復原事務

connection.rollback()

防 SQL 注入

• 轉義特殊字元
  connection.escape_string(str)

• 參數化語句
  支援傳入參數進行自動轉義、格式化 SQL 陳述式，以避免 SQL 注入等安全問題。

# 插入資料(元組或列表)effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)', ('mary', 18))# 插入資料(字典)info = {'name': 'fake', 'age': 15}effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%(name)s, %(age)s)', info)# 批量插入effect_row = cursor.executemany(    'INSERT INTO `users` (`name`, `age`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE age=VALUES(age)', [        ('hello', 13),        ('fake', 28),    ])

參考資料

• Python中操作mysql的pymysql模組詳解

• Python之pymysql的使用