Python 和 MySQL 數據庫：實用介紹

目錄

比較 Mysql 與其他 SQL 數據庫

安裝 Mysql 服務器和 MySQL 連接器/Python

安裝 MySQL 服務器

安裝 MySQL 連接器/Python

建立與 MySQL 服務器的連接

建立連接

創建新數據庫

連接到現有數據庫

創建、更改和刪除表

定義數據庫架構

使用 CREATE TABLE 語句創建表

使用 DESCRIBE 語句顯示表模式

使用 ALTER 語句修改表架構

使用 DROP 語句刪除表

在表中插入記錄

使用 .execute()

使用 .executemany()

從數據庫中讀取記錄

使用 SELECT 語句讀取記錄

使用 WHERE 子句過濾結果

使用 JOIN 語句處理多個表

從數據庫更新和刪除記錄

更新命令

刪除命令

連接 Python 和 MySQL 的其他方法

結論

MySQL是當今市場上最流行的數據庫管理系統 (DBMS)之一。它在今年的DB-Engines 排名中僅次于Oracle DBMS。由于大多數軟件應用程序需要以某種形式與數據交互，因此 Python 等編程語言提供了用于存儲和訪問這些數據源的工具。

使用本教程中討論的技術，您將能夠有效地將 MySQL 數據庫與 Python 應用程序集成。您將為電影分級系統開發一個小型 MySQL 數據庫，并學習如何直接從 Python 代碼中查詢它。

在本教程結束時，您將能夠：

識別MySQL 的獨特功能

將您的應用程序連接到 MySQL 數據庫

查詢數據庫以獲取所需數據

處理訪問數據庫時發生的異常

在構建數據庫應用程序時使用最佳實踐

要從本教程中獲得最大收益，您應該具備 Python 概念的應用知識，例如for循環、函數、異常處理以及使用pip.?你也應該有關系數據庫管理系統的一個基本的了解和SQL查詢一樣SELECT，DROP，CREATE，和JOIN。

比較 MySQL 與其他 SQL 數據庫

SQL代表結構化查詢語言，是一種廣泛使用的用于管理關系數據庫的編程語言。您可能聽說過不同風格的基于 SQL 的 DBMS。最流行的包括MySQL、PostgreSQL、SQLite和SQL Server。所有這些數據庫都符合SQL 標準，但符合程度各不相同。

作為開源自1995年成立以來，MySQL的很快成為SQL解決方案中的市場領導者。MySQL 也是 Oracle 生態系統的一部分。雖然其核心功能是完全免費的，但也有一些付費附加組件。目前，所有主要科技公司都在使用 MySQL，包括 Google、LinkedIn、Uber、Netflix、Twitter 等。

除了大型開源社區的支持，MySQL 的成功還有許多其他原因：

易于安裝：?MySQL 被設計為用戶友好的。設置 MySQL 數據庫非常簡單，幾個廣泛使用的第三方工具，如phpMyAdmin，進一步簡化了設置過程。MySQL 可用于所有主要操作系統，包括 Windows、macOS、Linux 和 Solaris。

速度：?MySQL 以極快的數據庫解決方案而著稱。從長遠來看，它的占地面積相對較小，并且具有極強的可擴展性。

用戶權限和安全性：?MySQL 附帶一個腳本，允許您設置密碼安全級別、分配管理員密碼以及添加和刪除用戶帳戶權限。此腳本簡化了 Web 托管用戶管理門戶的管理過程。其他 DBMS，如 PostgreSQL，使用更復雜的配置文件。

雖然 MySQL 以其速度和易用性而聞名，但您可以使用 PostgreSQL獲得更多高級功能。此外，MySQL 并不完全符合 SQL 標準，并且具有某些功能限制，例如不支持FULL JOIN子句。

您可能還面臨一些在 MySQL 中并發讀寫的問題。如果您的軟件有很多用戶同時向其寫入數據，那么 PostgreSQL 可能是更合適的選擇。

注意：要在實際環境中對 MySQL 和 PostgreSQL 進行更深入的比較，請查看為什么優步工程從 Postgres 切換到 MySQL。

SQL Server 也是一種非常流行的 DBMS，以其可靠性、效率和安全性而聞名。它是公司的首選，尤其是在銀行領域，他們經常處理大流量工作負載。它是一種商業解決方案，是與 Windows 服務最兼容的系統之一。

2010 年，當甲骨文收購Sun Microsystems和 MySQL 時，很多人都擔心 MySQL 的未來。當時，甲骨文是 MySQL 最大的競爭對手。開發人員擔心這是甲骨文的敵意收購，目的是摧毀 MySQL。

迄今為止，MariaDB 仍然完全獲得 GPL 許可，將其完全保留在公共領域中。另一方面，MySQL 的某些功能僅適用于付費許可證。此外，MariaDB 提供了幾個 MySQL 服務器不支持的非常有用的功能，例如分布式 SQL和列式存儲。您可以在MariaDB 的網站上找到 MySQL 和 MariaDB 之間的更多差異。

MySQL 使用與標準 SQL 非常相似的語法。但是，官方文檔中提到了一些顯著差異。

安裝 MySQL 服務器和 MySQL 連接器/Python

現在，要開始學習本教程，您需要設置兩件事：MySQL 服務器和MySQL 連接器。MySQL 服務器將提供處理數據庫所需的所有服務。服務器啟動并運行后，您可以使用 MySQL Connector/Python 將您的 Python 應用程序與其連接。

安裝 MySQL 服務器

在官方文檔詳細介紹了推薦的方法來下載和安裝MySQL服務器。您會找到所有流行操作系統的說明，包括Windows、macOS、Solaris、Linux等等。

對于 Windows，最好的方法是下載MySQL Installer并讓它負責整個過程。安裝管理器還可以幫助您配置 MySQL 服務器的安全設置。在 Accounts and Roles 頁面上，您需要輸入root?(admin) 帳戶的密碼，還可以選擇添加其他具有不同權限的用戶：

雖然您必須在安裝過程中為 root 帳戶指定憑據，但您可以稍后修改這些設置。

注意：記住主機名、用戶名和密碼，因為這些將在稍后與 MySQL 服務器建立連接時需要。

雖然本教程只需要 MySQL 服務器，但您也可以使用這些安裝程序設置其他有用的工具，如MySQL Workbench。如果您不想直接在操作系統中安裝 MySQL，那么使用 Docker 在 Linux 上部署 MySQL是一個方便的選擇。

安裝 MySQL 連接器/Python

一個數據庫驅動程序是一個軟件，它允許連接的應用程序和交互與數據庫系統。像 Python 這樣的編程語言需要一個特殊的驅動程序才能與來自特定供應商的數據庫對話。

這些驅動程序通常作為第三方模塊獲得。在Python數據庫API（DB-API）定義了標準接口與所有的Python數據庫驅動程序必須遵守。這些細節記錄在PEP 249 中。所有 Python 數據庫驅動程序，例如SQLite 的sqlite3、PostgreSQL 的psycopg和MySQL 的 MySQL Connector/Python，都遵循這些實現規則。

注意：?MySQL 的官方文檔使用術語連接器而不是驅動程序。從技術上講，連接器僅與連接到數據庫相關聯，而不與數據庫交互。但是，該術語通常用于包含連接器和驅動程序的整個數據庫訪問模塊。

為了與文檔保持一致，每當提到 MySQL 時，您都會看到術語連接器。

許多流行的編程語言都有自己的數據庫 API。例如，Java 具有Java 數據庫連接 (JDBC)?API。如果需要將 Java 應用程序連接到 MySQL 數據庫，則需要使用MySQL JDBC 連接器，它遵循 JDBC API。

同樣，在 Python 中，您需要安裝 Python MySQL 連接器才能與 MySQL 數據庫交互。許多包都遵循 DB-API 標準，但其中最受歡迎的是MySQL Connector/Python。您可以通過以下方式獲得它pip：

$ pip install mysql-connector-python

pip將連接器作為第三方模塊安裝在當前活動的虛擬環境中。建議您為項目以及所有依賴項設置一個隔離的虛擬環境。

要測試安裝是否成功，請在 Python 終端上鍵入以下命令：

>>>

>>> import mysql.connector

如果上面的代碼執行沒有錯誤，那么mysql.connector就安裝好了，可以使用了。如果您遇到任何錯誤，請確保您處于正確的虛擬環境中并且您使用的是正確的 Python 解釋器。

確保您安裝了正確的mysql-connector-python包，這是一個純 Python 實現。謹防類似名稱但現在已貶值的連接器，如mysql-connector.

建立與 MySQL 服務器的連接

MySQL 是一個基于服務器的數據庫管理系統。一臺服務器可能包含多個數據庫。要與數據庫交互，您必須首先與服務器建立連接。一個 Python 程序與基于 MySQL 的數據庫交互的一般工作流程如下：

連接到 MySQL 服務器。

創建一個新的數據庫。

連接到新創建的或現有的數據庫。

執行 SQL 查詢并獲取結果。

如果對表進行了任何更改，請通知數據庫。

關閉與 MySQL 服務器的連接。

這是一個通用工作流程，可能因個別應用程序而異。但無論應用程序是什么，第一步都是將數據庫與應用程序連接起來。

建立連接

與 MySQL 服務器交互的第一步是建立連接。為此，您需要connect()從mysql.connector模塊。這個函數像參數host，user以及password并返回一個MySQLConnection對象。您可以從用戶那里接收這些憑據作為輸入并將它們傳遞給connect()：

from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), ) as connection: print(connection) except Error as e: print(e)

上面的代碼使用輸入的登錄憑據與您的 MySQL 服務器建立連接。作為回報，您會得到一個MySQLConnection對象，該對象存儲在connection變量中。從現在開始，您將使用這個變量來訪問您的 MySQL 服務器。

在上面的代碼中有幾個重要的事情需要注意：

您應該始終處理在與MySQL 服務器建立連接時可能引發的異常。這就是為什么您使用try...except塊來捕獲和打印您可能遇到的任何異常的原因。

訪問完數據庫后，您應該始終關閉連接。保留未使用的打開連接可能會導致一些意外錯誤和性能問題。上面的代碼利用了上下文管理器 usingwith，它抽象了連接清理過程。

您永遠不應該直接在 Python 腳本中硬編碼您的登錄憑據，即您的用戶名和密碼。這對于部署來說是一種糟糕的做法，并且會帶來嚴重的安全威脅。上面的代碼提示用戶輸入登錄憑據。它使用內置getpass模塊隱藏密碼。雖然這比硬編碼要好，但還有其他更安全的方式來存儲敏感信息，比如使用環境變量。

您現在已經在程序和 MySQL 服務器之間建立了連接，但您仍然需要創建一個新數據庫或連接到服務器內的現有數據庫。

創建新數據庫

在上一節中，您建立了與 MySQL 服務器的連接。要創建一個新的數據庫，您需要執行一條 SQL 語句：

CREATE DATABASE books_db;

上面的語句將創建一個名為 的新數據庫books_db。

注意：在 MySQL 中，必須;在語句的末尾放置一個分號 (?)，它表示查詢的終止。但是，MySQL Connector/Python 會自動在您的查詢末尾附加一個分號，因此無需在您的 Python 代碼中使用它。

要在 Python 中執行 SQL 查詢，您需要使用cursor，它抽象了對數據庫記錄的訪問。MySQL Connector/Python 為您提供了MySQLCursor類，該類實例化了可以在 Python 中執行 MySQL 查詢的對象。MySQLCursor類的實例也稱為 a?cursor。

cursor對象使用MySQLConnection對象與您的 MySQL 服務器進行交互。要創建cursor，請使用變量的.cursor()方法connection：

cursor = connection.cursor()

上面的代碼為您提供了一個MySQLCursor類的實例。

需要被執行的查詢發送到cursor.execute()的字符串格式。在這種特殊情況下，您將CREATE DATABASE查詢發送至cursor.execute()：

from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), ) as connection: create_db_query = "CREATE DATABASE online_movie_rating" with connection.cursor() as cursor: cursor.execute(create_db_query) except Error as e: print(e)

執行上述代碼后，您將online_movie_rating在 MySQL 服務器中調用一個新數據庫。

該CREATE DATABASE查詢被存儲為一個字符串create_db_query變量，然后傳遞到cursor.execute()用于執行。該代碼使用帶有cursor對象的上下文管理器來處理清理過程。

如果您的服務器中已存在同名數據庫，您可能會在此處收到錯誤消息。為了確認這一點，您可以顯示服務器中所有數據庫的名稱。使用之前的相同MySQLConnection對象，執行SHOW DATABASES語句：

>>>

>>> show_db_query = "SHOW DATABASES" >>> with connection.cursor() as cursor: ... cursor.execute(show_db_query) ... for db in cursor: ... print(db) ... ('information_schema',) ('mysql',) ('online_movie_rating',) ('performance_schema',) ('sys',)

上面的代碼打印當前在您的 MySQL 服務器中的所有數據庫的名稱。該SHOW DATABASES命令還會輸出一些您未在服務器中創建的數據庫，例如information_schema、performance_schema等。這些數據庫由 MySQL 服務器自動生成，并提供對各種數據庫元數據和 MySQL 服務器設置的訪問。

您在本節中通過執行CREATE DATABASE語句創建了一個新數據庫。在下一節中，您將看到如何連接到已經存在的數據庫。

連接到現有數據庫

在上一節中，您創建了一個名為 的新數據庫online_movie_rating。但是，您仍然沒有連接到它。在許多情況下，您已經擁有一個想要與 Python 應用程序連接的 MySQL 數據庫。

您可以connect()使用之前使用的相同函數發送一個名為 的附加參數來執行此操作database：

from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), database="online_movie_rating", ) as connection: print(connection) except Error as e: print(e)

上面的代碼與您之前使用的連接腳本非常相似。這里唯一的變化是一個附加database參數，您的數據庫名稱將傳遞給connect().?執行此腳本后，您將連接到online_movie_rating數據庫。

創建、更改和刪除表

在本節中，您將學習如何執行一些基本的DDL查詢，如CREATE，DROP和ALTER與Python。您將快速了解將在本教程的其余部分中使用的 MySQL 數據庫。您還將創建數據庫所需的所有表，并在稍后了解如何對這些表進行修改。

定義數據庫架構

您可以從為在線電影評級系統創建數據庫模式開始。數據庫將由三個表組成：

movies?包含有關電影的一般信息并具有以下屬性：

id

title

release_year

genre

collection_in_mil

reviewers?包含有關發表評論或評級的人的信息，并具有以下屬性：

id

first_name

last_name

ratings?包含有關已發布評級的信息，并具有以下屬性：

movie_id?（外鍵）

reviewer_id?（外鍵）

rating

現實世界的電影分級系統，如IMDb，需要存儲一堆其他屬性，如電子郵件、電影演員列表等。如果需要，您可以向該數據庫添加更多表和屬性。但這三個表足以滿足本教程的目的。

下圖描述了數據庫架構：

該數據庫中的表相互關聯。movies并且reviewers會有多對多的關系，因為一部電影可以被多個評論者評論，一個評論者可以評論多部電影。該ratings表的連接movies表與reviewers表。

使用CREATE TABLE語句創建表

現在，要在 MySQL 中創建一個新表，您需要使用CREATE TABLE語句。以下 MySQL 查詢將為movies您的online_movie_rating數據庫創建表：

CREATE TABLE movies( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100), collection_in_mil INT );

如果您之前看過 SQL 語句，那么上面的大部分查詢可能都有意義。但是您應該注意 MySQL 語法中的一些差異。

例如，MySQL 有多種數據類型供您閱讀，包括YEAR、INT、BIGINT等。此外，AUTO_INCREMENT當插入新記錄時必須自動增加列值時，MySQL 使用關鍵字。

要創建新表，您需要將此查詢傳遞給cursor.execute()，它接受 MySQL 查詢并在連接的 MySQL 數據庫上執行查詢：

create_movies_table_query = """ CREATE TABLE movies( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100), collection_in_mil INT ) """ with connection.cursor() as cursor: cursor.execute(create_movies_table_query) connection.commit()

現在您movies的數據庫中有該表。您傳遞create_movies_table_query到cursor.execute()，它執行所需的執行。

注意：該connection變量指的MySQLConnection是連接到數據庫時返回的對象。

另外，請注意connection.commit()代碼末尾的語句。默認情況下，您的 MySQL 連接器不會自動提交事務。在 MySQL 中，事務中提到的修改只有在您COMMIT最終使用命令時才會發生。每次事務后始終調用此方法以在實際表中執行更改。

與對movies表所做的一樣，執行以下腳本來創建reviewers表：

create_reviewers_table_query = """ CREATE TABLE reviewers ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100) ) """ with connection.cursor() as cursor: cursor.execute(create_reviewers_table_query) connection.commit()

如果需要，您可以添加有關審閱者的更多信息，例如他們的電子郵件 ID 或人口統計信息。但是first_name，last_name現在將滿足您的目的。

最后，您可以ratings使用以下腳本創建表：

create_ratings_table_query = """ CREATE TABLE ratings ( movie_id INT, reviewer_id INT, rating DECIMAL(2,1), FOREIGN KEY(movie_id) REFERENCES movies(id), FOREIGN KEY(reviewer_id) REFERENCES reviewers(id), PRIMARY KEY(movie_id, reviewer_id) ) """ with connection.cursor() as cursor: cursor.execute(create_ratings_table_query) connection.commit()

與標準 SQL 相比，MySQL 中外鍵關系的實現略有不同且受到限制。在 MySQL 中，外鍵約束中的 parent 和 child 必須使用相同的存儲引擎。

一個存儲引擎是基礎軟件組件，數據庫管理系統用于執行SQL操作。在 MySQL 中，存儲引擎有兩種不同的風格：

事務存儲引擎是事務安全的，并允許您使用簡單的命令回滾事務，例如rollback.?許多流行的 MySQL 引擎，包括InnoDB和NDB，都屬于這一類。

非事務性存儲引擎依賴于復雜的手動代碼來撤消在數據庫上提交的語句。MyISAM、MEMORY和許多其他 MySQL 引擎是非事務性的。

InnoDB 是默認且最受歡迎的存儲引擎。它通過支持外鍵約束來幫助維護數據完整性。這意味著檢查外鍵上的任何CRUD操作以確保它不會導致不同表之間的不一致。

另外，請注意該ratings表使用列movie_id和reviewer_id，這兩個外鍵，共同作為主鍵。此步驟可確保審閱者不能對同一部電影進行兩次評分。

您可以選擇對多次執行重復使用同一個游標。在這種情況下，所有執行都將成為一個原子事務，而不是多個單獨的事務。例如，您可以CREATE TABLE使用一個游標執行所有語句，然后只提交一次事務：

with connection.cursor() as cursor: cursor.execute(create_movies_table_query) cursor.execute(create_reviewers_table_query) cursor.execute(create_ratings_table_query) connection.commit()

上面的代碼將首先執行所有三個CREATE語句。然后它會向COMMITMySQL 服務器發送一個命令來提交你的事務。您還可以使用.rollback()向ROLLBACKMySQL 服務器發送命令并從事務中刪除所有數據更改。

使用DESCRIBE語句顯示表模式

現在，您已經創建了所有三個表，您可以使用以下 SQL 語句查看它們的架構：

DESCRIBE ;

要從cursor對象返回一些結果，您需要使用cursor.fetchall().?此方法從上次執行的語句中獲取所有行。假設您已經MySQLConnection在connection變量中擁有該對象，您可以打印出通過cursor.fetchall()以下方式獲取的所有結果：

>>>

>>> show_table_query = "DESCRIBE movies" >>> with connection.cursor() as cursor: ... cursor.execute(show_table_query) ... # Fetch rows from last executed query ... result = cursor.fetchall() ... for row in result: ... print(row) ... ('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment') ('title', 'varchar(100)', 'YES', '', None, '') ('release_year', 'year(4)', 'YES', '', None, '') ('genre', 'varchar(100)', 'YES', '', None, '') ('collection_in_mil', 'int(11)', 'YES', '', None, '')

執行上述代碼后，您應該會收到一個表格，其中包含有關表格中所有列的信息movies。對于每一列，您將收到列的數據類型、列是否為主鍵等詳細信息。

使用ALTER語句修改表架構

在movies表中，您有一個名為 的列collection_in_mil，其中包含以百萬美元為單位的電影票房收入。您可以編寫以下 MySQL 語句將collection_in_mil屬性的數據類型從修改INT為DECIMAL：

ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1);

DECIMAL(4,1)表示可以有最多4位數的十進制數，其中1為十進制，如120.1、3.4、38.0等。執行ALTER TABLE語句后，您可以使用DESCRIBE以下命令顯示更新的表架構：

>>>

>>> alter_table_query = """ ... ALTER TABLE movies ... MODIFY COLUMN collection_in_mil DECIMAL(4,1) ... """ >>> show_table_query = "DESCRIBE movies" >>> with connection.cursor() as cursor: ... cursor.execute(alter_table_query) ... cursor.execute(show_table_query) ... # Fetch rows from last executed query ... result = cursor.fetchall() ... print("Movie Table Schema after alteration:") ... for row in result: ... print(row) ... Movie Table Schema after alteration ('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment') ('title', 'varchar(100)', 'YES', '', None, '') ('release_year', 'year(4)', 'YES', '', None, '') ('genre', 'varchar(100)', 'YES', '', None, '') ('collection_in_mil', 'decimal(4,1)', 'YES', '', None, '')

如輸出所示，該collection_in_mil屬性現在的類型為DECIMAL(4,1)。另請注意，在上面的代碼中，您調用了cursor.execute()兩次。但是cursor.fetchall()只從最后執行的查詢中獲取行，即show_table_query.

使用DROP語句刪除表

要刪除表，需要在 MySQL 中執行該DROP TABLE語句。刪除表是一個不可逆的過程。如果您執行下面的代碼，那么您將需要CREATE TABLE再次調用查詢以ratings在接下來的部分中使用該表。

要刪除ratings表，請發送drop_table_query至cursor.execute()：

drop_table_query = "DROP TABLE ratings" with connection.cursor() as cursor: cursor.execute(drop_table_query)

如果你執行上面的代碼，你就成功地刪除了ratings表。

在表中插入記錄

在最后一節，您創建數據庫中的三個表：movies，reviewers，和ratings。現在您需要用數據填充這些表。本節將介紹在 MySQL Connector for Python 中插入記錄的兩種不同方法。

第一種方法 ，.execute()當記錄數量很少并且可以硬編碼記錄時效果很好。第二種方法.executemany()更受歡迎，更適合現實世界的場景。

使用?.execute()

第一種方法使用的cursor.execute()方法與您迄今為止一直使用的方法相同。您將INSERT INTO查詢寫入字符串并將其傳遞給cursor.execute().?您可以使用此方法向movies表中插入數據。

作為參考，該movies表有五個屬性：

id

title

release_year

genre

collection_in_mil

您不需要添加數據，id因為它會AUTO_INCREMENT自動id為您計算。以下腳本將記錄插入movies表中：

insert_movies_query = """ INSERT INTO movies (title, release_year, genre, collection_in_mil) VALUES ("Forrest Gump", 1994, "Drama", 330.2), ("3 Idiots", 2009, "Drama", 2.4), ("Eternal Sunshine of the Spotless Mind", 2004, "Drama", 34.5), ("Good Will Hunting", 1997, "Drama", 138.1), ("Skyfall", 2012, "Action", 304.6), ("Gladiator", 2000, "Action", 188.7), ("Black", 2005, "Drama", 3.0), ("Titanic", 1997, "Romance", 659.2), ("The Shawshank Redemption", 1994, "Drama",28.4), ("Udaan", 2010, "Drama", 1.5), ("Home Alone", 1990, "Comedy", 286.9), ("Casablanca", 1942, "Romance", 1.0), ("Avengers: Endgame", 2019, "Action", 858.8), ("Night of the Living Dead", 1968, "Horror", 2.5), ("The Godfather", 1972, "Crime", 135.6), ("Haider", 2014, "Action", 4.2), ("Inception", 2010, "Adventure", 293.7), ("Evil", 2003, "Horror", 1.3), ("Toy Story 4", 2019, "Animation", 434.9), ("Air Force One", 1997, "Drama", 138.1), ("The Dark Knight", 2008, "Action",535.4), ("Bhaag Milkha Bhaag", 2013, "Sport", 4.1), ("The Lion King", 1994, "Animation", 423.6), ("Pulp Fiction", 1994, "Crime", 108.8), ("Kai Po Che", 2013, "Sport", 6.0), ("Beasts of No Nation", 2015, "War", 1.4), ("Andadhun", 2018, "Thriller", 2.9), ("The Silence of the Lambs", 1991, "Crime", 68.2), ("Deadpool", 2016, "Action", 363.6), ("Drishyam", 2015, "Mystery", 3.0) """ with connection.cursor() as cursor: cursor.execute(insert_movies_query) connection.commit()

該movies表現在加載了 30 條記錄。代碼connection.commit()在最后調用。.commit()在對表進行任何修改后調用是至關重要的。

使用?.executemany()

前一種方法更適用于記錄數量相當少并且您可以將這些記錄直接寫入代碼的情況。但這很少是真的。您通常會將這些數據存儲在某個其他文件中，或者這些數據將由不同的腳本生成并需要添加到 MySQL 數據庫中。

這就是.executemany()派上用場的地方。它接受兩個參數：

包含需要插入的記錄的占位符的查詢

一個列表，其中包含的所有記錄，你要插入

以下示例為reviewers表插入記錄：

insert_reviewers_query = """ INSERT INTO reviewers (first_name, last_name) VALUES ( %s, %s ) """ reviewers_records = [ ("Chaitanya", "Baweja"), ("Mary", "Cooper"), ("John", "Wayne"), ("Thomas", "Stoneman"), ("Penny", "Hofstadter"), ("Mitchell", "Marsh"), ("Wyatt", "Skaggs"), ("Andre", "Veiga"), ("Sheldon", "Cooper"), ("Kimbra", "Masters"), ("Kat", "Dennings"), ("Bruce", "Wayne"), ("Domingo", "Cortes"), ("Rajesh", "Koothrappali"), ("Ben", "Glocker"), ("Mahinder", "Dhoni"), ("Akbar", "Khan"), ("Howard", "Wolowitz"), ("Pinkie", "Petit"), ("Gurkaran", "Singh"), ("Amy", "Farah Fowler"), ("Marlon", "Crafford"), ] with connection.cursor() as cursor: cursor.executemany(insert_reviewers_query, reviewers_records) connection.commit()

在上面的腳本中，您將查詢和記錄列表作為參數傳遞給.executemany()。這些記錄可能是從文件或用戶那里獲取的，并存儲在reviewers_records列表中。

該代碼%s用作必須插入到insert_reviewers_query.?占位符充當格式說明符并幫助為字符串中的變量保留一個位置。然后在執行期間將指定的變量添加到該位置。

您可以類似地使用.executemany()在ratings表中插入記錄：

insert_ratings_query = """ INSERT INTO ratings (rating, movie_id, reviewer_id) VALUES ( %s, %s, %s) """ ratings_records = [ (6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17), (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4), (8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10), (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19), (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9), (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19), (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15), (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20), (8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13), (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17), (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4), (9.8, 13, 1) ] with connection.cursor() as cursor: cursor.executemany(insert_ratings_query, ratings_records) connection.commit()

所有三個表現在都填充了數據。您現在擁有一個功能齊全的在線電影評級數據庫。下一步是了解如何與該數據庫進行交互。

從數據庫中讀取記錄

到目前為止，您一直在構建數據庫。現在是時候對其執行一些查詢并從該數據集中找到一些有趣的屬性了。在本節中，您將學習如何使用SELECT語句從數據庫表中讀取記錄。

使用SELECT語句讀取記錄

要檢索記錄，您需要向 發送SELECT查詢cursor.execute()。然后使用cursor.fetchall()以行或記錄列表的形式提取檢索到的表。

嘗試編寫一個 MySQL 查詢以從movies表中選擇所有記錄并將其發送到.execute()：

>>>

>>> select_movies_query = "SELECT * FROM movies LIMIT 5" >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... result = cursor.fetchall() ... for row in result: ... print(row) ... (1, 'Forrest Gump', 1994, 'Drama', Decimal('330.2')) (2, '3 Idiots', 2009, 'Drama', Decimal('2.4')) (3, 'Eternal Sunshine of the Spotless Mind', 2004, 'Drama', Decimal('34.5')) (4, 'Good Will Hunting', 1997, 'Drama', Decimal('138.1')) (5, 'Skyfall', 2012, 'Action', Decimal('304.6'))

該result變量保存使用返回的記錄.fetchall()。它是代表表中單個記錄的元組列表。

在上面的查詢中，您使用LIMIT子句來限制從SELECT語句接收的行數。開發人員經常使用LIMIT進行分頁處理大量數據的時候。

在 MySQL 中，該LIMIT子句采用一或兩個非負數字參數。使用一個參數時，您指定要返回的最大行數。由于您的查詢包括LIMIT 5，因此僅5獲取第一條記錄。使用這兩個參數時，您還可以指定要返回的第一行的偏移量：

SELECT * FROM movies LIMIT 2,5;

第一個參數指定 的偏移量2，第二個參數將返回的行數限制為5。上面的查詢將返回第 3 到 7 行。

您還可以查詢選定的列：

>>>

>>> select_movies_query = "SELECT title, release_year FROM movies LIMIT 5" >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for row in cursor.fetchall(): ... print(row) ... ('Forrest Gump', 1994) ('3 Idiots', 2009) ('Eternal Sunshine of the Spotless Mind', 2004) ('Good Will Hunting', 1997) ('Skyfall', 2012)

現在，代碼僅從兩個指定的列輸出值：title和release_year。

使用WHERE子句過濾結果

您可以使用WHERE子句按特定條件過濾表記錄。例如，要檢索票房收入超過 3 億美元的所有電影，您可以運行以下查詢：

SELECT title, collection_in_mil FROM movies WHERE collection_in_mil > 300;

您還可以在最后一個查詢中使用ORDER BY子句將結果從最高收入者到最低收入者進行排序：

>>>

>>> select_movies_query = """ ... SELECT title, collection_in_mil ... FROM movies ... WHERE collection_in_mil > 300 ... ORDER BY collection_in_mil DESC ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchall(): ... print(movie) ... ('Avengers: Endgame', Decimal('858.8')) ('Titanic', Decimal('659.2')) ('The Dark Knight', Decimal('535.4')) ('Toy Story 4', Decimal('434.9')) ('The Lion King', Decimal('423.6')) ('Deadpool', Decimal('363.6')) ('Forrest Gump', Decimal('330.2')) ('Skyfall', Decimal('304.6'))

MySQL 提供了大量的字符串格式化操作，例如CONCAT連接字符串。通常，網站會顯示電影名稱及其發行年份以避免混淆。要檢索票房收入前五名的電影的片名，并與它們的發行年份相連，您可以編寫以下查詢：

>>>

>>> select_movies_query = """ ... SELECT CONCAT(title, " (", release_year, ")"), ... collection_in_mil ... FROM movies ... ORDER BY collection_in_mil DESC ... LIMIT 5 ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchall(): ... print(movie) ... ('Avengers: Endgame (2019)', Decimal('858.8')) ('Titanic (1997)', Decimal('659.2')) ('The Dark Knight (2008)', Decimal('535.4')) ('Toy Story 4 (2019)', Decimal('434.9')) ('The Lion King (1994)', Decimal('423.6'))

如果您不想使用該LIMIT子句并且不需要獲取所有記錄，則該cursor對象也具有.fetchone()和.fetchmany()方法：

.fetchone()檢索結果的下一行，作為元組，或者None如果沒有更多行可用。

.fetchmany()從結果中檢索下一組行作為元組列表。它有一個size參數，默認為1，您可以使用它來指定需要獲取的行數。如果沒有更多行可用，則該方法返回一個空列表。

嘗試再次檢索與發行年份串聯的五部票房最高的電影的片名，但這次使用.fetchmany()：

>>>

>>> select_movies_query = """ ... SELECT CONCAT(title, " (", release_year, ")"), ... collection_in_mil ... FROM movies ... ORDER BY collection_in_mil DESC ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchmany(size=5): ... print(movie) ... cursor.fetchall() ... ('Avengers: Endgame (2019)', Decimal('858.8')) ('Titanic (1997)', Decimal('659.2')) ('The Dark Knight (2008)', Decimal('535.4')) ('Toy Story 4 (2019)', Decimal('434.9')) ('The Lion King (1994)', Decimal('423.6'))

with 的輸出.fetchmany()類似于您在使用該LIMIT子句時收到的輸出。您可能已經注意到最后的附加cursor.fetchall()調用。您這樣做是為了清除所有未讀取的剩余結果.fetchmany()。

在同一連接上執行任何其他語句之前，有必要清除所有未讀結果。否則，InternalError: Unread result found將引發異常。

使用JOIN語句處理多個表

如果您發現上一節中的查詢非常簡單，請不要擔心。您可以SELECT使用上一節中的相同方法使查詢盡可能復雜。

讓我們看一些稍微復雜的JOIN查詢。如果您想找出數據庫中評分最高的前五部電影的名稱，則可以運行以下查詢：

>>>

>>> select_movies_query = """ ... SELECT title, AVG(rating) as average_rating ... FROM ratings ... INNER JOIN movies ... ON movies.id = ratings.movie_id ... GROUP BY movie_id ... ORDER BY average_rating DESC ... LIMIT 5 ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchall(): ... print(movie) ... ('Night of the Living Dead', Decimal('9.90000')) ('The Godfather', Decimal('9.90000')) ('Avengers: Endgame', Decimal('9.75000')) ('Eternal Sunshine of the Spotless Mind', Decimal('8.90000')) ('Beasts of No Nation', Decimal('8.70000'))

如上所示，《活死人之夜》和《教父》并列為您online_movie_rating數據庫中評分最高的電影。

要查找給出最多評分的評論者的姓名，請編寫以下查詢：

>>>

>>> select_movies_query = """ ... SELECT CONCAT(first_name, " ", last_name), COUNT(*) as num ... FROM reviewers ... INNER JOIN ratings ... ON reviewers.id = ratings.reviewer_id ... GROUP BY reviewer_id ... ORDER BY num DESC ... LIMIT 1 ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchall(): ... print(movie) ... ('Mary Cooper', 4)

Mary Cooper是這個數據庫中最頻繁的評論者。如上所示，查詢有多復雜并不重要，因為它最終由 MySQL 服務器處理。您執行查詢的過程將始終保持不變：將查詢傳遞給cursor.execute()并使用 獲取結果.fetchall()。

從數據庫更新和刪除記錄

在本節中，您將更新和刪除數據庫中的記錄。這兩種操作都可以對表中的單個記錄或多個記錄執行。您將使用WHERE子句選擇需要修改的行。

UPDATE?命令

您數據庫中的一位審閱者Amy Farah Fowler，現在與Sheldon Cooper.?她的姓氏現在已更改為Cooper，因此您需要相應地更新您的數據庫。對于更新記錄，MySQL 使用以下UPDATE語句：

update_query = """ UPDATE reviewers SET last_name = "Cooper" WHERE first_name = "Amy" """ with connection.cursor() as cursor: cursor.execute(update_query) connection.commit()

代碼將更新查詢傳遞給cursor.execute()，并將.commit()所需的更改帶到reviewers表中。

注意：在UPDATE查詢中，WHERE子句幫助指定需要更新的記錄。如果你不使用WHERE，那么所有的記錄都會被更新！

假設您需要提供一個選項，允許審閱者修改評級。審閱者將提供三個值movie_id、reviewer_id、 和新的rating.?代碼將在執行指定的修改后顯示記錄。

假設movie_id = 18，reviewer_id = 15和新的rating = 5.0，你可以使用下面的MySQL查詢執行所需的修改：

UPDATE ratings SET rating = 5.0 WHERE movie_id = 18 AND reviewer_id = 15; SELECT * FROM ratings WHERE movie_id = 18 AND reviewer_id = 15;

上述查詢首先更新評級，然后顯示它。您可以創建一個完整的 Python 腳本來建立與數據庫的連接并允許審閱者修改評級：

from getpass import getpass from mysql.connector import connect, Error movie_id = input("Enter movie id: ") reviewer_id = input("Enter reviewer id: ") new_rating = input("Enter new rating: ") update_query = """ UPDATE ratings SET rating = "%s" WHERE movie_id = "%s" AND reviewer_id = "%s"; SELECT * FROM ratings WHERE movie_id = "%s" AND reviewer_id = "%s" """ % ( new_rating, movie_id, reviewer_id, movie_id, reviewer_id, ) try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), database="online_movie_rating", ) as connection: with connection.cursor() as cursor: for result in cursor.execute(update_query, multi=True): if result.with_rows: print(result.fetchall()) connection.commit() except Error as e: print(e)

將此代碼保存到名為modify_ratings.py.?上面的代碼使用%s占位符將接收到的輸入插入到update_query字符串中。在本教程中，您第一次在單個字符串中有多個查詢。要將多個查詢傳遞給單個cursor.execute()，您需要將該方法的multi參數設置為True。

如果multi是True，則cursor.execute()返回一個迭代器。迭代器中的每一項都對應一個cursor對象，該對象執行查詢中傳遞的語句。上面的代碼for在這個迭代器上運行一個循環，然后調用.fetchall()每個cursor對象。

注意：.fetchall()在所有游標對象上運行很重要。要在同一個連接上執行新語句，您必須確保沒有來自先前執行的未讀結果。如果有未讀結果，那么您將收到異常。

如果操作中未獲取任何結果集，則.fetchall()引發異常。為避免此錯誤，在上面的代碼中使用了cursor.with_rows屬性，該屬性指示最近執行的操作是否產生了行。

雖然此代碼應該可以解決您的目的，但該WHERE條款是當前狀態下網絡黑客的主要目標。它容易受到所謂的SQL 注入攻擊，這可能允許惡意行為者破壞或濫用您的數據庫。

警告：不要在您的數據庫上嘗試以下輸入！它們會損壞您的表，您需要重新創建它。

例如，如果用戶發送movie_id=18、reviewer_id=15和 newrating=5.0作為輸入，則輸出如下所示：

$ python modify_ratings.py Enter movie id: 18 Enter reviewer id: 15 Enter new rating: 5.0 Enter username: Enter password: [(18, 15, Decimal('5.0'))]

在rating與movie_id=18和reviewer_id=15已更改為5.0。但是如果你是黑客，那么你可能會在你的輸入中發送一個隱藏的命令：

$ python modify_ratings.py Enter movie id: 18 Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A Enter new rating: 5.0 Enter username: Enter password: [(18, 15, Decimal('5.0'))]

同樣，輸出顯示指定rating已更改為5.0。有什么變化？

黑客在進入reviewer_id.?更新查詢將表中所有記錄的update reviewers set last_name = "A更改為。如果您打印出表格，您可以看到此更改：last_namereviewers"A"reviewers

>>>

>>> select_query = """ ... SELECT first_name, last_name ... FROM reviewers ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_query) ... for reviewer in cursor.fetchall(): ... print(reviewer) ... ('Chaitanya', 'A') ('Mary', 'A') ('John', 'A') ('Thomas', 'A') ('Penny', 'A') ('Mitchell', 'A') ('Wyatt', 'A') ('Andre', 'A') ('Sheldon', 'A') ('Kimbra', 'A') ('Kat', 'A') ('Bruce', 'A') ('Domingo', 'A') ('Rajesh', 'A') ('Ben', 'A') ('Mahinder', 'A') ('Akbar', 'A') ('Howard', 'A') ('Pinkie', 'A') ('Gurkaran', 'A') ('Amy', 'A') ('Marlon', 'A')

上面的代碼顯示了表中所有記錄的first_name和。SQL 注入攻擊通過將所有記錄的 更改為來破壞此表。last_namereviewerslast_name"A"

有一個快速修復可以防止此類攻擊。不要將用戶提供的查詢值直接添加到您的查詢字符串中。相反，更新modify_ratings.py腳本以將這些查詢值作為參數發送到.execute()：

from getpass import getpass from mysql.connector import connect, Error movie_id = input("Enter movie id: ") reviewer_id = input("Enter reviewer id: ") new_rating = input("Enter new rating: ") update_query = """ UPDATE ratings SET rating = %s WHERE movie_id = %s AND reviewer_id = %s; SELECT * FROM ratings WHERE movie_id = %s AND reviewer_id = %s """ val_tuple = ( new_rating, movie_id, reviewer_id, movie_id, reviewer_id, ) try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), database="online_movie_rating", ) as connection: with connection.cursor() as cursor: for result in cursor.execute(update_query, val_tuple, multi=True): if result.with_rows: print(result.fetchall()) connection.commit() except Error as e: print(e)

請注意，%s占位符不再位于字符串引號中。傳遞給占位符的字符串可能包含一些特殊字符。如有必要，這些可以由底層庫正確轉義。

cursor.execute()確保作為參數接收的元組中的值是所需的數據類型。如果用戶試圖偷偷輸入一些有問題的字符，那么代碼將引發異常：

$ python modify_ratings.py Enter movie id: 18 Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A Enter new rating: 5.0 Enter username: Enter password: 1292 (22007): Truncated incorrect DOUBLE value: '15"; UPDATE reviewers SET last_name = "A'

cursor.execute()如果在用戶輸入中發現任何不需要的字符，則會引發異常。每當您將用戶輸入合并到查詢中時，您都應該使用這種方法。還有其他方法可以防止 SQL 注入攻擊。

DELETE?命令

刪除記錄的工作方式與更新記錄非常相似。您使用該DELETE語句刪除選定的記錄。

注意：刪除是一個不可逆的過程。如果不使用該WHERE子句，則指定表中的所有記錄都將被刪除。您需要INSERT INTO再次運行查詢以取回已刪除的記錄。

建議您首先SELECT使用相同的過濾器運行查詢，以確保您刪除的是正確的記錄。例如，要刪除由 給出的所有評分reviewer_id = 2，您應該首先運行相應的SELECT查詢：

>>>

>>> select_movies_query = """ ... SELECT reviewer_id, movie_id FROM ratings ... WHERE reviewer_id = 2 ... """ >>> with connection.cursor() as cursor: ... cursor.execute(select_movies_query) ... for movie in cursor.fetchall(): ... print(movie) ... (2, 7) (2, 8) (2, 12) (2, 23)

上面的代碼片段輸出表中的reviewer_id和movie_id記錄，ratings其中reviewer_id = 2.?確認這些是您需要刪除的記錄后，您可以DELETE使用相同的過濾器運行查詢：

delete_query = "DELETE FROM ratings WHERE reviewer_id = 2" with connection.cursor() as cursor: cursor.execute(delete_query) connection.commit()

使用此查詢，您可以reviewer_id = 2從ratings表中刪除審閱者給出的所有評分。

連接 Python 和 MySQL 的其他方法

在本教程中，您看到了 MySQL 連接器/Python，這是官方推薦的從 Python 應用程序與 MySQL 數據庫交互的方法。還有另外兩種流行的連接器：

mysqlclient是一個庫，它是官方連接器的競爭對手，并積極更新新功能。因為它的核心是用C語言編寫的，所以性能比純Python官方連接器更好。一個很大的缺點是設置和安裝相當困難，尤其是在 Windows 上。

MySQLdb是一種遺留軟件，仍在商業應用程序中使用。它是用 C 編寫的，比 MySQL Connector/Python 更快，但僅適用于 Python 2。

這些連接器充當您的程序和 MySQL 數據庫之間的接口，您可以通過它們發送 SQL 查詢。但是許多開發人員更喜歡使用面向對象的范式而不是 SQL 查詢來操作數據。

對象關系映射(ORM) 是一種技術，允許您使用面向對象的語言直接查詢和操作數據庫中的數據。ORM 庫封裝了操作數據所需的代碼，從而無需使用哪怕是一點點 SQL。以下是基于 SQL 的數據庫最流行的 Python ORM：

SQLAlchemy是一種 ORM，可促進 Python 和其他 SQL 數據庫之間的通信。您可以為不同的數據庫（如 MySQL、PostgreSQL、SQLite 等）創建不同的引擎。SQLAlchemy 通常與 Pandas 庫一起使用，以提供完整的數據處理功能。

peewee是一個輕量級、快速的 ORM，可以快速設置。當您與數據庫的交互僅限于提取一些記錄時，這非常有用。例如，如果您需要將 MySQL 數據庫中的選定記錄復制到 CSV 文件中，那么 peewee 可能是您的最佳選擇。

Django的ORM是最強大的功能之一的Django，并沿著Django的web框架提供。它可以與多種數據庫交互，例如 SQLite、PostgreSQL 和 MySQL。許多基于 Django 的應用程序使用 Django ORM 進行數據建模和基本查詢，但通常會切換到 SQLAlchemy來滿足更復雜的需求。

您可能會發現其中一種方法更適合您的應用程序。如果您不確定要使用哪一個，那么最好使用官方推薦的 MySQL 連接器/Python，您在本教程中看到了它的實際應用。

結論

在本教程中，您了解了如何使用 MySQL Connector/Python 將 MySQL 數據庫與 Python 應用程序集成。您還看到了 MySQL 數據庫的一些獨特功能，這些功能將其與其他 SQL 數據庫區分開來。

在此過程中，您學習了一些在建立連接、創建表以及在數據庫應用程序中插入和更新記錄時值得考慮的編程最佳實踐。您還為在線電影評級系統開發了一個示例 MySQL 數據庫，并直接從您的 Python 應用程序與其交互。

在本教程中，您學習了如何：

將您的 Python 應用程序與MySQL 數據庫連接

將 MySQL 數據庫中的數據導入Python進行進一步分析

從 Python 應用程序執行SQL 查詢

訪問數據庫時處理異常

防止對您的應用程序的SQL 注入攻擊

MySQL Python 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。