Python3進階——使用PyMySQL操作MySQL

PyMySQL 是一個純 Python 實現的 MySQL 客戶端操作庫，支援事務、儲存過程、批量執行等。

PyMySQL 遵循 Python 資料庫 API v2.0 規範，幷包含了 pure-Python MySQL 客戶端庫。

安裝

pip install PyMySQL

建立資料庫連線

import pymysql

connection = 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

查詢資料

# 執行查詢 SQL
cursor.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的使用

---

原文地址: https://shockerli.net/post/python3-pymysql/