摘要：是一個(gè)純實(shí)現(xiàn)的客戶端操作庫，支持事務(wù)存儲過程批量執(zhí)行等。插入數(shù)據(jù)元組或列表插入數(shù)據(jù)字典批量插入?yún)⒖假Y料中操作的模塊詳解之的使用原文地址更多文章請?jiān)L問我的個(gè)人博客

PyMySQL 是一個(gè)純 Python 實(shí)現(xiàn)的 MySQL 客戶端操作庫，支持事務(wù)、存儲過程、批量執(zhí)行等。

PyMySQL 遵循 Python 數(shù)據(jù)庫 API v2.0 規(guī)范，并包含了 pure-Python MySQL 客戶端庫。

pip install PyMySQL

import pymysql

connection = pymysql.connect(host="localhost",
                             port=3306,
                             user="root",
                             password="root",
                             db="demo",
                             charset="utf8")

參數(shù)列表：

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

# 獲取游標(biāo)
cursor = connection.cursor()

# 創(chuàng)建數(shù)據(jù)表
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
""")

# 插入數(shù)據(jù)(元組或列表)
effect_row = cursor.execute("INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)", ("mary", 18))

# 插入數(shù)據(jù)(字典)
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) 批量執(zhí)行 SQL

# 獲取游標(biāo)
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 等修改數(shù)據(jù)的語句需手動(dòng)執(zhí)行connection.commit()完成對數(shù)據(jù)修改的提交。

cursor.lastrowid

# 執(zhí)行查詢 SQL
cursor.execute("SELECT * FROM `users`")

# 獲取單條數(shù)據(jù)
cursor.fetchone()

# 獲取前N條數(shù)據(jù)
cursor.fetchmany(3)

# 獲取所有數(shù)據(jù)
cursor.fetchall()

所有的數(shù)據(jù)查詢操作均基于游標(biāo)，我們可以通過cursor.scroll(num, mode)控制游標(biāo)的位置。

cursor.scroll(1, mode="relative") # 相對當(dāng)前位置移動(dòng)
cursor.scroll(2, mode="absolute") # 相對絕對位置移動(dòng)

查詢時(shí)，默認(rèn)返回的數(shù)據(jù)類型為元組，可以自定義設(shè)置返回類型。支持5種游標(biāo)類型：

Cursor: 默認(rèn)，元組類型

DictCursor: 字典類型

DictCursorMixin: 支持自定義的游標(biāo)類型，需先自定義才可使用

SSCursor: 無緩沖元組類型

SSDictCursor: 無緩沖字典類型

無緩沖游標(biāo)類型，適用于數(shù)據(jù)量很大，一次性返回太慢，或者服務(wù)端帶寬較小時(shí)。源碼注釋：

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.

創(chuàng)建連接時(shí)，通過 cursorclass 參數(shù)指定類型：

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

也可以在創(chuàng)建游標(biāo)時(shí)指定類型：

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

開啟事務(wù)

connection.begin()

提交修改

connection.commit()

回滾事務(wù)

connection.rollback()

轉(zhuǎn)義特殊字符
connection.escape_string(str)

參數(shù)化語句
支持傳入?yún)?shù)進(jìn)行自動(dòng)轉(zhuǎn)義、格式化 SQL 語句，以避免 SQL 注入等安全問題。

# 插入數(shù)據(jù)(元組或列表)
effect_row = cursor.execute("INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)", ("mary", 18))

# 插入數(shù)據(jù)(字典)
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/py...
更多文章請?jiān)L問我的個(gè)人博客: https://shockerli.net