MySQL 是最流行的關係型數據庫管理系統,如果你不不熟悉 MySQL,可以閱讀我們的
本章節我們為大家介紹使用 mysql-connector 來連接使用 MySQL, mysql-connector 是 MySQL 官方提供的驅動器。
我們可以使用 pip 命令來安裝 mysql-connector:
python -m pip install mysql-connector
使用以下代碼測試 mysql-connector 是否安裝成功:
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 '新密碼';
創建數據庫連接
可以使用以下代碼來連接數據庫:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost", # 數據庫主機地址
user="yourusername", # 數據庫用戶名
passwd="yourpassword" # 數據庫密碼
)
print(mydb)
創建數據庫
創建數據庫使用 "CREATE DATABASE" 語句,以下創建一個名為 runoob_db 的數據庫:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE runoob_db")
創建數據庫前我們也可以使用 "SHOW DATABASES" 語句來查看數據庫是否存在:
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)
或者我們可以直接連接數據庫,如果數據庫不存在,會輸出錯誤信息:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
創建數據表
創建數據表使用 "CREATE TABLE" 語句,創建數據表前,需要確保數據庫已存在,以下創建一個名為 sites 的數據表:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")
我們也可以使用 "SHOW TABLES" 語句來查看數據表是否已存在:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
print(x)
主鍵設置
創建表的時候我們一般都會設置一個主鍵(PRIMARY KEY),我們可以使用 "INT AUTO_INCREMENT PRIMARY KEY" 語句來創建一個主鍵,主鍵起始值為 1,逐步遞增。
如果我們的表已經創建,我們需要使用 ALTER TABLE 來給表添加主鍵:
給 sites 表添加主鍵。
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
如果你還未創建 sites 表,可以直接使用以下代碼創建。
給表創建主鍵。
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
插入數據
插入數據使用 "INSERT INTO" 語句:
向 sites 表插入一條記錄。
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("RUNOOB", "https://www.runoob.com")
mycursor.execute(sql, val)
mydb.commit() # 數據表內容有更新,必須使用到該語句
print(mycursor.rowcount, "記錄插入成功。")
批量插入
批量插入使用 executemany() 方法,該方法的第二個參數是一個元組列表,包含了我們要插入的數據:
向 sites 表插入多條記錄。
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_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, "記錄插入成功。")
如果我們想在數據記錄插入後,獲取該記錄的 ID ,可以使用以下代碼:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_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)
查詢數據
查詢數據使用 SELECT 語句:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchall() # fetchall() 獲取所有記錄
for x in myresult:
print(x)
也可以讀取指定的字段數據:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT name, url FROM sites")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
如果我們只想讀取一條數據,可以使用 fetchone() 方法:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchone()
print(myresult)
where 條件語句
如果我們要讀取指定條件的數據,可以使用 where 語句:
讀取 name 字段為 RUNOOB 的記錄:
也可以使用通配符 %:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE url LIKE '%oo%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
為了防止數據庫查詢發生 SQL 注入的攻擊,我們可以使用 %s 佔位符來轉義查詢的條件:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name = %s"
na = ("RUNOOB", )
mycursor.execute(sql, na)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
排序
查詢結果排序可以使用 ORDER BY 語句,默認的排序方式為升序,關鍵字為 ASC,如果要設置降序排序,可以設置關鍵字 DESC。
按 name 字段字母的升序排序:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
按 name 字段字母的降序排序:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name DESC"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Limit
如果我們要設置查詢的數據量,可以通過 "LIMIT" 語句來指定
讀取前 3 條記錄:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites LIMIT 3")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
也可以指定起始位置,使用的關鍵字是
從第二條開始讀取前 3 條記錄:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1") # 0 為 第一條,1 為第二條,以此類推
myresult = mycursor.fetchall()
for x in myresult:
print(x)
刪除記錄
刪除記錄使用 "DELETE FROM" 語句:
刪除 name 為 stackoverflow 的記錄:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 條記錄刪除")
注意:要慎重使用刪除語句,刪除語句要確保指定了 WHERE 條件語句,否則會導致整表數據被刪除。
為了防止數據庫查詢發生 SQL 注入的攻擊,我們可以使用 %s 佔位符來轉義刪除語句的條件:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "DELETE FROM sites WHERE name = %s"
na = ("stackoverflow", )
mycursor.execute(sql, na)
mydb.commit()
print(mycursor.rowcount, " 條記錄刪除")
更新表數據
數據表更新使用 "UPDATE" 語句:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 條記錄被修改")
注意:UPDATE 語句要確保指定了 WHERE 條件語句,否則會導致整表數據被更新。
為了防止數據庫查詢發生 SQL 注入的攻擊,我們可以使用 %s 佔位符來轉義更新語句的條件:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_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, " 條記錄被修改")
刪除表
刪除表使用 "DROP TABLE" 語句, IF EXISTS 關鍵字是用於判斷表是否存在,只有在存在的情況才刪除:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS sites" # 刪除數據表 sites
mycursor.execute(sql)