建站
咨詢
售后
建站咨詢
新聞中心
Python MySQL - mysql-connector 驅(qū)動(dòng)
MySQL 是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，如果你不熟悉 MySQL，可以閱讀我們的
創(chuàng)新互聯(lián)建站基于成都重慶香港及美國等地區(qū)分布式IDC機(jī)房數(shù)據(jù)中心構(gòu)建的電信大帶寬，聯(lián)通大帶寬，移動(dòng)大帶寬，多線BGP大帶寬租用,是為眾多客戶提供專業(yè)服務(wù)器托管報(bào)價(jià)，主機(jī)托管價(jià)格性價(jià)比高，為金融證券行業(yè)德陽電信服務(wù)器托管，ai人工智能服務(wù)器托管提供bgp線路100M獨(dú)享，G口帶寬及機(jī)柜租用的專業(yè)成都idc公司。
本章節(jié)我們?yōu)榇蠹医榻B使用 mysql-connector 來連接使用 MySQL， mysql-connector 是 MySQL 官方提供的驅(qū)動(dòng)器。
我們可以使用 pip 命令來安裝 mysql-connector：
python -m pip install mysql-connector
使用以下代碼測(cè)試 mysql-connector 是否安裝成功：
demo_mysql_test.py:
import
mysql
.
connector
執(zhí)行以上代碼，如果沒有產(chǎn)生錯(cuò)誤，表明安裝成功。
注意：如果你的 MySQL 是 8.0 版本，密碼插件驗(yàn)證方式發(fā)生了變化，早期版本為 mysql_native_password，8.0 版本為 caching_sha2_password，所以需要做些改變：
先修改 my.ini 配置：
[mysqld] default_authentication_plugin=mysql_native_password然后在 mysql 下執(zhí)行以下命令來修改密碼：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密碼';更多內(nèi)容可以參考：。
創(chuàng)建數(shù)據(jù)庫連接
可以使用以下代碼來連接數(shù)據(jù)庫：
demo_mysql_test.py:
import
mysql
.
connector
mydb
=
mysql
.
connector
.
connect
(
host
=
"
localhost
"
,
# 數(shù)據(jù)庫主機(jī)地址
user
=
"
yourusername
"
,
# 數(shù)據(jù)庫用戶名
passwd
=
"
yourpassword
"
# 數(shù)據(jù)庫密碼
)
print
(
mydb
)
創(chuàng)建數(shù)據(jù)庫
創(chuàng)建數(shù)據(jù)庫使用 "CREATE DATABASE" 語句，以下創(chuàng)建一個(gè)名為 runoob_db 的數(shù)據(jù)庫：
demo_mysql_test.py:
import
mysql
.
connector
mydb
=
mysql
.
connector
.
connect
(
host
=
"
localhost
"
,
user
=
"
root
"
,
passwd
=
"
123456
"
)
mycursor
=
mydb
.
cursor
(
)
mycursor
.
execute
(
"
CREATE DATABASE runoob_db
"
)
創(chuàng)建數(shù)據(jù)庫前我們也可以使用 "SHOW DATABASES" 語句來查看數(shù)據(jù)庫是否存在：
demo_mysql_test.py:
輸出所有數(shù)據(jù)庫列表：
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
)
或者我們可以直接連接數(shù)據(jù)庫，如果數(shù)據(jù)庫不存在，會(huì)輸出錯(cuò)誤信息：
demo_mysql_test.py:
import
mysql
.
connector
mydb
=
mysql
.
connector
.
connect
(
host
=
"
localhost
"
,
user
=
"
root
"
,
passwd
=
"
123456
"
,
database
=
"
runoob_db
"
)
創(chuàng)建數(shù)據(jù)表
創(chuàng)建數(shù)據(jù)表使用 "CREATE TABLE" 語句，創(chuàng)建數(shù)據(jù)表前，需要確保數(shù)據(jù)庫已存在，以下創(chuàng)建一個(gè)名為 sites 的數(shù)據(jù)表：
demo_mysql_test.py:
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))
"
) 執(zhí)行成功后，我們可以看到數(shù)據(jù)庫創(chuàng)建的數(shù)據(jù)表 sites，字段為 name 和 url。
我們也可以使用 "SHOW TABLES" 語句來查看數(shù)據(jù)表是否已存在：
demo_mysql_test.py:
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
)
主鍵設(shè)置
創(chuàng)建表的時(shí)候我們一般都會(huì)設(shè)置一個(gè)主鍵（PRIMARY KEY），我們可以使用 "INT AUTO_INCREMENT PRIMARY KEY" 語句來創(chuàng)建一個(gè)主鍵，主鍵起始值為 1，逐步遞增。
如果我們的表已經(jīng)創(chuàng)建，我們需要使用 ALTER TABLE 來給表添加主鍵：
demo_mysql_test.py:
給 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
"
)
如果你還未創(chuàng)建 sites 表，可以直接使用以下代碼創(chuàng)建。
demo_mysql_test.py:
給表創(chuàng)建主鍵。
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))
"
)
插入數(shù)據(jù)
插入數(shù)據(jù)使用 "INSERT INTO" 語句：
demo_mysql_test.py:
向 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
(
)
# 數(shù)據(jù)表內(nèi)容有更新，必須使用到該語句
print
(
mycursor
.
rowcount
,
"
記錄插入成功。
"
)
執(zhí)行代碼，輸出結(jié)果為：
1 記錄插入成功
批量插入
批量插入使用 executemany() 方法，該方法的第二個(gè)參數(shù)是一個(gè)元組列表，包含了我們要插入的數(shù)據(jù)：
demo_mysql_test.py:
向 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
(
)
# 數(shù)據(jù)表內(nèi)容有更新，必須使用到該語句
print
(
mycursor
.
rowcount
,
"
記錄插入成功。
"
)
執(zhí)行代碼，輸出結(jié)果為：
4 記錄插入成功。
執(zhí)行以上代碼后，我們可以看看數(shù)據(jù)表的記錄：
如果我們想在數(shù)據(jù)記錄插入后，獲取該記錄的 ID ，可以使用以下代碼：
demo_mysql_test.py:
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
)
執(zhí)行代碼，輸出結(jié)果為：
1 條記錄已插入, ID: 6
查詢數(shù)據(jù)
查詢數(shù)據(jù)使用 SELECT 語句：
demo_mysql_test.py:
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
)
執(zhí)行代碼，輸出結(jié)果為：
(1, 'RUNOOB', 'https://www.runoob.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')
也可以讀取指定的字段數(shù)據(jù)：
demo_mysql_test.py:
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
)
執(zhí)行代碼，輸出結(jié)果為：
('RUNOOB', 'https://www.runoob.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')
如果我們只想讀取一條數(shù)據(jù)，可以使用 fetchone() 方法：
demo_mysql_test.py:
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
)
執(zhí)行代碼，輸出結(jié)果為：
(1, 'RUNOOB', 'https://www.runoob.com')
where 條件語句
如果我們要讀取指定條件的數(shù)據(jù)，可以使用 where 語句：
demo_mysql_test.py
讀取 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 name ='RUNOOB'
"
mycursor
.
execute
(
sql
)
myresult
=
mycursor
.
fetchall
(
)
for
x
in
myresult
:
print
(
x
)
執(zhí)行代碼，輸出結(jié)果為：
(1, 'RUNOOB', 'https://www.runoob.com')
也可以使用通配符 %：
demo_mysql_test.py
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
)
執(zhí)行代碼，輸出結(jié)果為：
(1, 'RUNOOB', 'https://www.runoob.com') (2, 'Google', 'https://www.google.com')
為了防止數(shù)據(jù)庫查詢發(fā)生 SQL 注入的攻擊，我們可以使用 %s 占位符來轉(zhuǎn)義查詢的條件：
demo_mysql_test.py
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
)
排序
查詢結(jié)果排序可以使用 ORDER BY 語句，默認(rèn)的排序方式為升序，關(guān)鍵字為 ASC，如果要設(shè)置降序排序，可以設(shè)置關(guān)鍵字 DESC。
demo_mysql_test.py
按 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
)
執(zhí)行代碼，輸出結(jié)果為：
(3, 'Github', 'https://www.github.com') (2, 'Google', 'https://www.google.com') (1, 'RUNOOB', 'https://www.runoob.com') (5, 'stackoverflow', 'https://www.stackoverflow.com/') (4, 'Taobao', 'https://www.taobao.com') (6, 'Zhihu', 'https://www.zhihu.com')
降序排序?qū)嵗?/p>
demo_mysql_test.py
按 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
)
執(zhí)行代碼，輸出結(jié)果為：
(6, 'Zhihu', 'https://www.zhihu.com') (4, 'Taobao', 'https://www.taobao.com') (5, 'stackoverflow', 'https://www.stackoverflow.com/') (1, 'RUNOOB', 'https://www.runoob.com') (2, 'Google', 'https://www.google.com') (3, 'Github', 'https://www.github.com')
Limit
如果我們要設(shè)置查詢的數(shù)據(jù)量，可以通過 "LIMIT" 語句來指定
demo_mysql_test.py
讀取前 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
)
執(zhí)行代碼，輸出結(jié)果為：
(1, 'RUNOOB', 'https://www.runoob.com') (2, 'Google', 'https://www.google.com') (3, 'Github', 'https://www.github.com')
也可以指定起始位置，使用的關(guān)鍵字是 OFFSET：
demo_mysql_test.py
從第二條開始讀取前 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
)
執(zhí)行代碼，輸出結(jié)果為：
(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
=
"
runoob_db
"
)
mycursor
=
mydb
.
cursor
(
)
sql
=
"
DELETE FROM sites WHERE name = 'stackoverflow'
"
mycursor
.
execute
(
sql
)
mydb
.
commit
(
)
print
(
mycursor
.
rowcount
,
"
條記錄刪除
"
)
執(zhí)行代碼，輸出結(jié)果為：
1 條記錄刪除
注意：要慎重使用刪除語句，刪除語句要確保指定了 WHERE 條件語句，否則會(huì)導(dǎo)致整表數(shù)據(jù)被刪除。
為了防止數(shù)據(jù)庫查詢發(fā)生 SQL 注入的攻擊，我們可以使用 %s 占位符來轉(zhuǎn)義刪除語句的條件：
demo_mysql_test.py
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
,
"
條記錄刪除
"
)
執(zhí)行代碼，輸出結(jié)果為：
1 條記錄刪除
更新表數(shù)據(jù)
數(shù)據(jù)表更新使用 "UPDATE" 語句：
demo_mysql_test.py
將 name 為 Zhihu 的字段數(shù)據(jù)改為 ZH：
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
,
"
條記錄被修改
"
)
執(zhí)行代碼，輸出結(jié)果為：
1 條記錄被修改
注意：UPDATE 語句要確保指定了 WHERE 條件語句，否則會(huì)導(dǎo)致整表數(shù)據(jù)被更新。
為了防止數(shù)據(jù)庫查詢發(fā)生 SQL 注入的攻擊，我們可以使用 %s 占位符來轉(zhuǎn)義更新語句的條件：
demo_mysql_test.py
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
,
"
條記錄被修改
"
)
執(zhí)行代碼，輸出結(jié)果為：
1 條記錄被修改
刪除表
刪除表使用 "DROP TABLE" 語句， IF EXISTS 關(guān)鍵字是用于判斷表是否存在，只有在存在的情況才刪除：
demo_mysql_test.py
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
"
# 刪除數(shù)據(jù)表 sites
mycursor
.
execute
(
sql
)
文章名稱：PythonMySQL-mysql-connector驅(qū)動(dòng)
分享路徑：http://www.5511xx.com/article/djhgcje.html
