MySQL Python 教程 · ZetCode 中文系列教程

# MySQL Python 教程 > 原文： [http://zetcode.com/db/mysqlpython/](http://zetcode.com/db/mysqlpython/) 這是 MySQL Python 編程教程。它涵蓋了使用 Python 進行 MySQL 編程的基礎。它使用`MySQLdb`模塊。這些示例是在 Ubuntu Linux 上創建和測試的。 MySQLdb 是 MySQL 的 Python 2 舊版數據庫模塊。對于現代 Python 3 MySQL 編程，請使用 PyMySQL 模塊。參見 [PyMySQL 教程](/python/pymysql/)。 [Tweet](https://twitter.com/share) ## 關于 MySQL 數據庫 MySQL 是領先的開源數據庫管理系統。它是一個多用戶，多線程的數據庫管理系統。 MySQL 在網絡上特別流行。它是非常流行的 _LAMP_ 平臺的一部分，該平臺由 Linux，Apache，MySQL 和 PHP 組成。目前，MySQL 由 Oracle 擁有。 MySQL 數據庫在最重要的 OS 平臺上可用。它可以在 BSD Unix，Linux，Windows 或 Mac OS 上運行。維基百科和 YouTube 使用 MySQL。這些站點每天管理數百萬個查詢。 MySQL 有兩個版本：MySQL 服務器系統和 MySQL 嵌入式系統。 ## MySQLdb 安裝 ```py $ apt-cache search MySQLdb python-mysqldb - A Python interface to MySQL python-mysqldb-dbg - A Python interface to MySQL (debug extension) bibus - bibliographic database eikazo - graphical frontend for SANE designed for mass-scanning ``` 我們在包名稱中搜索`MySQLdb`模塊。我們使用`apt-cache`命令找出答案。 ```py $ sudo apt-get install python-mysqldb ``` 在這里，我們將 Python 接口安裝到 MySQL 數據庫。 `_mysql`和`mysql`模塊。 ## MySQL 安裝如果您尚未安裝 MySQL，則必須安裝它。 ```py $ sudo apt-get install mysql-server ``` 此命令將安裝 MySQL 服務器和其他各種包。在安裝包時，提示我們輸入 MySQL 根帳戶的密碼。接下來，我們將創建一個新的數據庫用戶和一個新的數據庫。我們使用`mysql`客戶端。 ```py $ mysql -u root -p Enter password: mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) ``` 我們使用根帳戶連接到數據庫。我們用`SHOW DATABASES`語句顯示所有可用的數據庫。 ```py mysql> CREATE DATABASE testdb; Query OK, 1 row affected (0.02 sec) ``` 我們創建一個新的`testdb`數據庫。在整個教程中，我們將使用此數據庫。 ```py mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623'; Query OK, 0 rows affected (0.00 sec) mysql> USE testdb; Database changed mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye ``` 我們創建一個新的數據庫用戶。我們授予該用戶`testdb`數據庫所有表的所有特權。 ## `_mysql`模塊 `_mysql`模塊直接實現 MySQL C API。它與 Python DB API 接口不兼容。通常，程序員更喜歡面向對象的`MySQLdb`模塊。我們將關注后一個模塊。在這里，我們僅介紹`_mysql`模塊的一個小示例。 `version_capi.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import _mysql import sys try: con = _mysql.connect('localhost', 'testuser', 'test623', 'testdb') con.query("SELECT VERSION()") result = con.use_result() print "MySQL version: %s" % \ result.fetch_row()[0] except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit(1) finally: if con: con.close() ``` 該示例將獲取并打印 MySQL 數據庫的版本。為此，我們使用`SELECT VERSION()` SQL 語句。 ## `MySQLdb`模塊 `MySQLdb`是圍繞`_mysql`的薄型 Python 包裝器。它與 Python DB API 兼容，這使代碼更易于移植。使用此模型是使用 MySQL 的首選方式。 ## MySQLdb 版本示例在第一個示例中，我們將獲取 MySQL 數據庫的版本。 `version.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb import sys try: con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') cur = con.cursor() cur.execute("SELECT VERSION()") ver = cur.fetchone() print "Database version : %s " % ver except mdb.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) finally: if con: con.close() ``` 在此腳本中，我們連接到`testdb`數據庫并執行`SELECT VERSION()`語句。這將返回 MySQL 數據庫的當前版本。我們將其打印到控制臺。 ```py import MySQLdb as mdb ``` 我們導入`MySQLdb`模塊。 ```py con = mdb.connect('localhost', 'testuser', 'test623', 'testdb'); ``` 我們使用`connect()`方法連接到數據庫。我們傳遞四個參數：主機名，數據庫用戶名，密碼和數據庫名。 ```py cur = con.cursor() cur.execute("SELECT VERSION()") ``` 從連接中，我們得到游標對象。游標用于遍歷結果集中的記錄。我們調用游標的`execute()`方法并執行 SQL 語句。 ```py ver = cur.fetchone() ``` 我們獲取數據。由于只檢索一條記錄，因此我們稱為`fetchone()`方法。 ```py print "Database version : %s " % ver ``` 我們將檢索到的數據打印到控制臺。 ```py except mdb.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) ``` 我們檢查錯誤。 ```py finally: if con: con.close() ``` 在最后一步，我們釋放資源。 ```py $ ./version.py Database version : 5.7.23-0ubuntu0.16.04.1 ``` 這是一個示例輸出。 ## MySQLdb 創建并填充表我們創建一個表，并用一些數據填充它。 `create_table.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("DROP TABLE IF EXISTS Writers") cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \ Name VARCHAR(25))") cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')") cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')") cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')") ``` 我們創建一個`Writers`表，并向其中添加五個作者。 ```py with con: ``` 使用`with`關鍵字，Python 解釋器會自動釋放資源。它還提供錯誤處理。 ```py cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \ Name VARCHAR(25))") ``` 該 SQL 語句創建一個名為`Writers`的新數據庫表。它具有兩列：`Id`和`Name`。 ```py cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") ... ``` 我們使用`INSERT`語句在表中插入作者。在這里，我們添加兩行。 ```py mysql> SELECT * FROM Writers; +----+-------------------+ | Id | Name | +----+-------------------+ | 1 | Jack London | | 2 | Honore de Balzac | | 3 | Lion Feuchtwanger | | 4 | Emile Zola | | 5 | Truman Capote | +----+-------------------+ 5 rows in set (0.00 sec) ``` 執行腳本后，我們使用`mysql`客戶端工具從`Writers`表中選擇所有數據。 ## `MySQLdb` `fetchall` `fetchall()`方法獲取查詢結果集的所有（或所有剩余）行，并返回一個元組列表。 `fetch_all.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("SELECT * FROM Writers") rows = cur.fetchall() for row in rows: print row ``` 在此示例中，我們從`Writers`表中檢索所有數據。 ```py cur.execute("SELECT * FROM Writers") ``` 該 SQL 語句從`Writers`表中選擇所有數據。 ```py rows = cur.fetchall() ``` `fetchall()`方法獲取所有記錄。它返回一個結果集。從技術上講，它是一個元組的元組。每個內部元組代表表中的一行。 ```py for row in rows: print row ``` 我們將數據逐行打印到控制臺。 ```py $ ./fetch_all.py (1L, 'Jack London') (2L, 'Honore de Balzac') (3L, 'Lion Feuchtwanger') (4L, 'Emile Zola') (5L, 'Truman Capote') ``` 這是示例的輸出。一次返回所有數據可能不可行。我們可以一一讀取行。 `fetch_onebyone.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("SELECT * FROM Writers") for i in range(cur.rowcount): row = cur.fetchone() print row[0], row[1] ``` 我們再次將數據從`Writers`表打印到控制臺。這次，我們一張一行地獲取行。 ```py for i in range(cur.rowcount): row = cur.fetchone() print row[0], row[1] ``` 我們使用`fetchone()`方法一張一行地獲取行。 `rowcount`屬性給出了 SQL 語句返回的行數。 ```py $ ./fetch_onebyone.py 1 Jack London 2 Honore de Balzac 3 Lion Feuchtwanger 4 Emile Zola 5 Truman Capote ``` 這是示例的輸出。 ## MySQLdb 字典游標 `MySQLdb`模塊中有多種游標類型。默認游標以元組的元組返回數據。當我們使用字典游標時，數據以 Python 字典的形式發送。這樣，我們可以通過列名稱來引用數據。 `dictionary_cursor.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor(mdb.cursors.DictCursor) cur.execute("SELECT * FROM Writers LIMIT 4") rows = cur.fetchall() for row in rows: print row["Id"], row["Name"] ``` 在此示例中，我們使用字典游標獲取`Writers`表的前四行。 ```py cur = con.cursor(mdb.cursors.DictCursor) ``` 我們使用`DictCursor`字典游標。 ```py cur.execute("SELECT * FROM Writers LIMIT 4") ``` 我們從`Writers`表中獲取四行。 ```py for row in rows: print row["Id"], row["Name"] ``` 我們通過`Writers`表的列名引用數據。 ```py $ ./dictcur.py 1 Jack London 2 Honore de Balzac 3 Lion Feuchtwanger 4 Emile Zola ``` 示例輸出。 ## MySQLdb 列標題接下來，我們將展示如何使用數據庫表中的數據打印列標題。 `column_headers.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("SELECT * FROM Writers LIMIT 5") rows = cur.fetchall() desc = cur.description print "%s %3s" % (desc[0][0], desc[1][0]) for row in rows: print "%2s %3s" % row ``` 同樣，我們將`Writers`表的內容打印到控制臺。現在，我們也包括列的名稱。列名被認為是“元數據”。它是從游標對象獲得的。 ```py desc = cur.description ``` 游標的描述屬性返回有關查詢的每個結果列的信息。 ```py print "%s %3s" % (desc[0][0], desc[1][0]) ``` 在這里，我們打印并格式化表的列名。 ```py for row in rows: print "%2s %3s" % row ``` 在這里，我們遍歷并打印數據。 ```py $ ./columnheaders.py Id Name 1 Jack London 2 Honore de Balzac 3 Lion Feuchtwanger 4 Emile Zola 5 Truman Capote ``` 這是輸出。 ## MySQLdb 預備語句在編寫預備語句時，我們使用占位符，而不是直接將值寫入語句中。預準備的語句可提高安全性和性能。 Python DB API 規范建議了五種不同的方式來構建預備語句。 `MySQLdb`模塊支持其中之一，即 ANSI `printf`格式代碼。 `prepared_statement.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Guy de Maupasant", "4")) print "Number of rows updated:", cur.rowcount ``` 我們在第四行更改作者的姓名。 ```py cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Guy de Maupasant", "4")) ``` 我們使用由`%s`標記標識的兩個占位符。在執行 SQL 語句之前，將值綁定到它們的占位符。 ```py $ ./prepared.py Number of rows updated: 1 ``` 我們更新了一行。 ```py mysql> SELECT Name FROM Writers WHERE Id=4; +------------------+ | Name | +------------------+ | Guy de Maupasant | +------------------+ 1 row in set (0.00 sec) ``` 第四行的作者已成功更改。 ## MySQLdb 插入圖像人們經常尋找將圖像插入數據庫的方法。我們將展示如何在 SQLite 和 Python 中完成它。請注意，有些人不建議將圖像放入數據庫。圖像是二進制數據。 MySQL 數據庫具有一種特殊的數據類型來存儲稱為`BLOB`（二進制大對象）的二進制數據。 `TINYBLOB`，`BLOB`，`MEDIUMBLOB`和`LONGBLOB`是二進制對象類型的變體。 ```py mysql> CREATE TABLE Images(Id INT PRIMARY KEY, Data MEDIUMBLOB); Query OK, 0 rows affected (0.08 sec) ``` 對于此示例，我們創建一個名為`Images`的新表。 `insert_image.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb def read_image(): with open("sid.jpg") as f: img = f.read() return img con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() data = read_image() cur.execute("INSERT INTO Images VALUES(1, %s)", (data, )) ``` 在上面的腳本中，我們從磁盤讀取 JPG 圖像并將其插入到`Images`表中。 ```py def read_image(): with open("sid.jpg") as f: img = f.read() return img ``` `read_image()`方法從位于當前工作目錄中的 JPG 文件讀取二進制數據。 ```py cur.execute("INSERT INTO Images VALUES(1, %s)", (data, )) ``` 我們將圖像數據插入`Images`表。 ## MySQLdb 讀取圖像在前面的示例中，我們已將圖像插入數據庫表中。現在，我們將從表中讀取圖像。 `read_image.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb def writeImage(data): fout = open('sid2.jpg', 'wb') with fout: fout.write(data) con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("SELECT Data FROM Images WHERE Id=1") data = cur.fetchone()[0] writeImage(data) ``` 我們從`Images`表中讀取了一張圖像。 ```py cur.execute("SELECT Data FROM Images WHERE Id=1") ``` 我們從表中選擇一條記錄。 ```py fout = open('sid2.jpg', 'wb') ``` 我們打開一個可寫的二進制文件。 ```py fout.write(data) ``` 我們將數據寫入磁盤。現在我們在當前目錄中應該有一個名為`woman2.jpg`的映像。我們可以檢查它是否與我們插入表中的圖像相同。 ## MySQLdb 事務支持事務是針對一個或多個數據庫中數據的數據庫操作的基本單位。事務中所有 SQL 語句的影響可以全部提交給數據庫，也可以全部回滾。對于支持事務的數據庫，在創建游標時，Python 接口會靜默啟動事務。 `commit()`方法將提交使用該游標進行的更新，而`rollback()`方法將丟棄這些更新。每種方法都會啟動一個新事務。 MySQL 數據庫具有不同類型的存儲引擎。最常見的是 MyISAM 和 InnoDB 引擎。從 MySQL 5.5 開始，InnoDB 成為默認的存儲引擎。在數據安全性和數據庫速度之間需要權衡。 MyISAM 表的處理速度更快，并且不支持事務。 `commit()`和`rollback()`方法未實現。他們什么都不做。另一方面，InnoDB 表可以更安全地防止數據丟失。他們支持事務。它們處理較慢。 `transaction.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb import sys try: con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') cur = con.cursor() cur.execute("DROP TABLE IF EXISTS Writers") cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \ Name VARCHAR(25)) ENGINE=INNODB") cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')") cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')") cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')") cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')") con.commit() except mdb.MySQLError, e: if con: con.rollback() print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) finally: if con: con.close() ``` 我們重新創建`Writers`表。我們明確地處理事務。 ```py cur = con.cursor() ``` 在 Python DB API 中，我們不調用`BEGIN`語句來啟動事務。創建游標后，將啟動一個事務。 ```py cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \ Name VARCHAR(25)) ENGINE=INNODB") ``` 我們正在處理一個 InnoDB 表類型。對于較早的 MySQL 版本（< 5.5），我們需要使用`ENGINE=INNODB`選項指定引擎類型。 ```py con.commit() ``` 我們必須使用`commit()`或`rollback()`方法結束事務。如果我們在這行中添加注釋，則會創建表，但不會將數據寫入表中。在本教程中，我們一直在處理事務而未明確說明它們。我們使用了上下文管理器。上下文管理器處理所需的運行時上下文的入口和出口，以執行代碼塊。通常使用`with`語句來調用上下文管理器。 `MySQLdb`模塊中的連接對象可用作上下文管理器。他們自動提交或回滾事務。連接上下文管理器通過分解`try`，`except`和`finally`語句來清理代碼。 `transaction2.py` ```py #!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("DROP TABLE IF EXISTS Writers") cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \ Name VARCHAR(25))") cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')") cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')") cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')") cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')") ``` 在我們的代碼示例中，上下文管理器處理錯誤處理所需的所有工作。它會自動提交或回滾事務。這是 MySQL Python 教程。它使用舊版`MySQLdb`模塊與 MySQL 一起使用。請參考 [PyMySQL 教程](/python/pymysql)以使用現代的 PyMySQL 模塊。