Python資料庫學習筆記(四):使用PyMySQL模組

安裝PyMySQL

pip install PyMySQL

創建名為user的表格

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) COLLATE utf8_bin NOT NULL,
`password` varchar(255) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;

利用Python進行數據庫操作

連接MySQL數據庫

import pymysql.cursors
#使用pymysql指令來連接數據庫
connection=pymysql.connect(host=’’,user=’’,password=’’,db=’’,charset=’’,cursorclass=pymysql.cursors.DictCursor
)
-----------------------------------------------------------------
host:要連接的數據庫的IP地址
user:登錄的賬戶名,如果登錄的是最高權限賬戶則為root
password:對應的密碼
db:要連接的數據庫,如需要訪問上節課存儲的IRIS數據庫,則輸入’IRIS’
charset:設置編碼格式,如utf8mb4就是一個編碼格式
cursorclass:返回到Python的結果,以什麼方式存儲,如Dict.Cursor是以字典的方式存儲

創建新的數據

try:
#從數據庫鏈接中得到cursor的數據結構
with connection.cursor() as cursor:
#在之前建立的user表格基礎上,插入新數據,這裡使用了一個預編譯的小技巧,避免每次都要重複寫sql的語句
sql=”INSERT INTO `USERS`(`email`,`password`) VALUES (%s,%s)”
cursor.execute(sql,(‘webmaster@python.org’,’very_secret’))
#執行到這一行指令時才是真正改變了數據庫,之前只是緩存在內存中
connection.commit()

查詢webmaster@python.org郵箱的密碼

with connection.cursor() as cursor:
sql = “SELECT `id`,`password` FROM `user` WHERE `email`=%s”
cursor.execute(sql,(‘webmaster@python.org’,))
#只取出一條結果
result=cursor.fetchone()
print(result)
#關閉連接
finally:
connection.close()

輸出結果為:

{‘password’: ‘very-secret’, ‘id’: 1}COMMIT:
1.注意過於頻繁的commit會降低數據插入的效率,可以在多行insert之後一次性commit;
2.autocommit選項:默認每一個insert操作都會觸發commit操作方式,是在pymysql.connect的db參數後面,加一個autocommit=True參數

利用Python對iris數據集進行查詢

查詢id為3的行

import pymysql.cursors
connection=pymysql.connect(host=’___’,user=’___’,password=’___’,db=’iris’,charset=’utf8mb4',cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
sql= “ SELECT * FROM `iris_with_id` WHERE `id`=%s”
cursor.execute(sql,(‘3’,))
result=cursor.fetchone()
print(result)
print(result[‘id’])
finally:
connection.close()

選擇petal_width>0.5的所有行:

import pymysql.cursors
connection=pymysql.connect(host=’___’,user=’___’,password=’___’,db=’iris’,charset=’utf8mb4',cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
SELECT * FROM `iris_with_id` WHERE `petal_width`>0.5
result=cursor.fetchall()
print(result)
finally:
connection.close()

選擇petal_width>0.5的所有行的id

import pymysql.cursors
connection=pymysql.connect(host=’___’,user=’___’,password=’___’,db=’iris’,charset=’utf8mb4',cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
SELECT * FROM `iris_with_id` WHERE `petal_width` >0.5
result=cursor.fetchall()
print(len(result))
for each_r in result:
print(each_r[‘id’])
finally:
connection.close()

Written by

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store