Python MySQL - mysql-connector 模組

MySQL 是最流行的關係型資料庫管理系統,如果你不不熟悉 MySQL,可以閱讀我們的 MySQL 教學。

本章節我們為大家介紹使用 mysql-connector 來連線使用 MySQL, mysql-connectorMySQL 官方提供的模組。

我們可以使用 pip 指令來安裝 mysql-connector

python -m pip install mysql-connector

使用以下程式碼測試 mysql-connector 是否安裝成功:

demo_mysql_test.py:

import mysql.connector

執行以上程式碼,如果沒有產生錯誤,表明安裝成功。

意:如果你的 MySQL 是 8.0 版本,密碼外掛驗證方式發生了變化,早期版本為 mysql_native_password,8.0 版本為 caching_sha2_password,所以需要做些改變:

先修改 my.ini 配置:

[mysqld]
default_authentication_plugin=mysql_native_password

然後在 mysql 下執行以下指令來修改密碼:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密碼';

更多內容可以參考:Python MySQL8.0 連結問題


建立資料庫連線

可以使用以下程式碼來連線資料庫:

demo_mysql_test.py:

import mysql.connector mydb = mysql.connector.connect( host="localhost", # 資料庫主機地址 user="yourusername", # 資料庫使用者名 passwd="yourpassword" # 資料庫密碼 ) print(mydb)

建立資料庫

建立資料庫使用 "CREATE DATABASE" 敘述,以下建立一個名為 twcode01_db 的資料庫:

demo_mysql_test.py:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456" ) mycursor = mydb.cursor() mycursor.execute("CREATE DATABASE twcode01_db")

建立資料庫前我們也可以使用 "SHOW DATABASES" 敘述來檢視資料庫是否存在:

demo_mysql_test.py:

輸出所有資料庫串列:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456" ) mycursor = mydb.cursor() mycursor.execute("SHOW DATABASES") for x in mycursor: print(x)

或者我們可以直接連線資料庫,如果資料庫不存在,會輸出錯誤訊息:

demo_mysql_test.py:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" )

建立資料表

建立資料表使用 "CREATE TABLE" 敘述,建立資料表前,需要確保資料庫已存在,以下建立一個名為 sites 的資料表:

demo_mysql_test.py:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")
執行成功後,我們可以看到資料庫建立的資料表 sites,欄位為 name 和 url。

我們也可以使用 "SHOW TABLES" 敘述來檢視資料表是否已存在:

demo_mysql_test.py:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() mycursor.execute("SHOW TABLES") for x in mycursor: print(x)

主鍵設定

建立表的時候我們一般都會設定一個主鍵(PRIMARY KEY),我們可以使用 "INT AUTO_INCREMENT PRIMARY KEY" 敘述來建立一個主鍵,主鍵起始值為 1,逐步遞增。

如果我們的表已經建立,我們需要使用 ALTER TABLE 來給表新增主鍵:

demo_mysql_test.py:

給 sites 表新增主鍵。

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

如果你還未建立 sites 表,可以直接使用以下程式碼建立。

demo_mysql_test.py:

給表建立主鍵。

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")

寫入資料

寫入資料使用 "INSERT INTO" 敘述:

demo_mysql_test.py:

向 sites 表寫入一筆資料。

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" val = ("TWCODE01", "https://www.twcode01.com") mycursor.execute(sql, val) mydb.commit() # 資料表內容有更新,必須使用到該敘述 print(mycursor.rowcount, "紀錄寫入成功。")

執行程式碼,輸出結果為:

1 紀錄寫入成功

批量寫入

批量寫入使用 executemany() 方法,該方法的第二個引數是一個元組串列,包含了我們要寫入的資料:

demo_mysql_test.py:

向 sites 表寫入多條紀錄。

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" val = [ ('Google', 'https://www.google.com'), ('Github', 'https://www.github.com'), ('Taobao', 'https://www.taobao.com'), ('stackoverflow', 'https://www.stackoverflow.com/') ] mycursor.executemany(sql, val) mydb.commit() # 資料表內容有更新,必須使用到該敘述 print(mycursor.rowcount, "紀錄寫入成功。")

執行程式碼,輸出結果為:

4 紀錄寫入成功。

執行以上程式碼後,我們可以看看資料表的紀錄:

如果我們想在資料紀錄寫入後,取得該紀錄的 ID ,可以使用以下程式碼:

demo_mysql_test.py:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" val = ("Zhihu", "https://www.zhihu.com") mycursor.execute(sql, val) mydb.commit() print("1 條紀錄已寫入, ID:", mycursor.lastrowid)

執行程式碼,輸出結果為:

1 條紀錄已寫入, ID: 6

搜尋資料

搜尋資料使用 SELECT 敘述:

demo_mysql_test.py:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM sites") myresult = mycursor.fetchall() # fetchall() 取得所有紀錄 for x in myresult: print(x)

執行程式碼,輸出結果為:

(1, 'TWCODE01', 'https://www.twcode01.com')
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
(4, 'Taobao', 'https://www.taobao.com')
(5, 'stackoverflow', 'https://www.stackoverflow.com/')
(6, 'Zhihu', 'https://www.zhihu.com')

也可以讀取指定的欄位資料:

demo_mysql_test.py:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() mycursor.execute("SELECT name, url FROM sites") myresult = mycursor.fetchall() for x in myresult: print(x)

執行程式碼,輸出結果為:

('TWCODE01', 'https://www.twcode01.com')
('Google', 'https://www.google.com')
('Github', 'https://www.github.com')
('Taobao', 'https://www.taobao.com')
('stackoverflow', 'https://www.stackoverflow.com/')
('Zhihu', 'https://www.zhihu.com')

如果我們只想讀取一條資料,可以使用 fetchone() 方法:

demo_mysql_test.py:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM sites") myresult = mycursor.fetchone() print(myresult)

執行程式碼,輸出結果為:

(1, 'TWCODE01', 'https://www.twcode01.com')

where 條件敘述

如果我們要讀取指定條件的資料,可以使用 where 敘述:

demo_mysql_test.py

讀取 name 欄位為 TWCODE01 的紀錄:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "SELECT * FROM sites WHERE name ='TWCODE01'" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)

執行程式碼,輸出結果為:

(1, 'TWCODE01', 'https://www.twcode01.com')

也可以使用萬用字元 %

demo_mysql_test.py

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "SELECT * FROM sites WHERE url LIKE '%oo%'" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)

執行程式碼,輸出結果為:

(1, 'TWCODE01', 'https://www.twcode01.com')
(2, 'Google', 'https://www.google.com')

為了防止資料庫搜尋發生 SQL 注入的攻擊,我們可以使用 %s 佔位符來轉義搜尋的條件:

demo_mysql_test.py

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "SELECT * FROM sites WHERE name = %s" na = ("TWCODE01", ) mycursor.execute(sql, na) myresult = mycursor.fetchall() for x in myresult: print(x)

排序

搜尋結果排序可以使用 ORDER BY 敘述,預設的排序方式為升序,關鍵字為 ASC,如果要設定降序排序,可以設定關鍵字 DESC

demo_mysql_test.py

按 name 欄位字母的升序排序:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "SELECT * FROM sites ORDER BY name" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)

執行程式碼,輸出結果為:

(3, 'Github', 'https://www.github.com')
(2, 'Google', 'https://www.google.com')
(1, 'TWCODE01', 'https://www.twcode01.com')
(5, 'stackoverflow', 'https://www.stackoverflow.com/')
(4, 'Taobao', 'https://www.taobao.com')
(6, 'Zhihu', 'https://www.zhihu.com')

降序排序例項:

demo_mysql_test.py

按 name 欄位字母的降序排序:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "SELECT * FROM sites ORDER BY name DESC" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)

執行程式碼,輸出結果為:

(6, 'Zhihu', 'https://www.zhihu.com')
(4, 'Taobao', 'https://www.taobao.com')
(5, 'stackoverflow', 'https://www.stackoverflow.com/')
(1, 'TWCODE01', 'https://www.twcode01.com')
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')

Limit

如果我們要設定搜尋的資料量,可以透過 "LIMIT" 敘述來指定

demo_mysql_test.py

讀取前 3 條紀錄:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM sites LIMIT 3") myresult = mycursor.fetchall() for x in myresult: print(x)

執行程式碼,輸出結果為:

(1, 'TWCODE01', 'https://www.twcode01.com')
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')

也可以指定起始位置,使用的關鍵字是 OFFSET

demo_mysql_test.py

從第二條開始讀取前 3 條紀錄:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1") # 0 為 第一條,1 為第二條,以此類推 myresult = mycursor.fetchall() for x in myresult: print(x)

執行程式碼,輸出結果為:

(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
(4, 'Taobao', 'https://www.taobao.com')

刪除紀錄

刪除紀錄使用 "DELETE FROM" 敘述:

demo_mysql_test.py

刪除 name 為 stackoverflow 的紀錄:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "DELETE FROM sites WHERE name = 'stackoverflow'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, " 條紀錄刪除")

執行程式碼,輸出結果為:

1  條紀錄刪除

注意:要慎重使用刪除敘述,刪除敘述要確保指定了 WHERE 條件敘述,否則會導致整表資料被刪除。

為了防止資料庫搜尋發生 SQL 注入的攻擊,我們可以使用 %s 佔位符來轉義刪除敘述的條件:

demo_mysql_test.py

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "DELETE FROM sites WHERE name = %s" na = ("stackoverflow", ) mycursor.execute(sql, na) mydb.commit() print(mycursor.rowcount, " 條紀錄刪除")

執行程式碼,輸出結果為:

1  條紀錄刪除

更新表資料

資料表更新使用 "UPDATE" 敘述:

demo_mysql_test.py

將 name 為 Zhihu 的欄位資料改為 ZH:

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, " 條紀錄被修改")

執行程式碼,輸出結果為:

1  條紀錄被修改

注意:UPDATE 敘述要確保指定了 WHERE 條件敘述,否則會導致整表資料被更新。

為了防止資料庫搜尋發生 SQL 注入的攻擊,我們可以使用 %s 佔位符來轉義更新敘述的條件:

demo_mysql_test.py

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "UPDATE sites SET name = %s WHERE name = %s" val = ("Zhihu", "ZH") mycursor.execute(sql, val) mydb.commit() print(mycursor.rowcount, " 條紀錄被修改")

執行程式碼,輸出結果為:

1  條紀錄被修改

刪除表

刪除表使用 "DROP TABLE" 敘述, IF EXISTS 關鍵字是用於判斷表是否存在,衹有在存在的情況才刪除:

demo_mysql_test.py

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="twcode01_db" ) mycursor = mydb.cursor() sql = "DROP TABLE IF EXISTS sites" # 刪除資料表 sites mycursor.execute(sql)