python操作MySQL和實戰

點選上方 " Python人工智慧技術 " 關註， 星標或者置頂

22點24分準時推播，第一時間送達

後台回復「 大禮包 」，送你特別福利

編輯：樂樂 | 來自：https://www.cnblogs.com/sbhglqy/p/18163862

上一篇：

大家好，我是Python人工智慧技術

1. 事務

innodb引擎中支持事務，myisam不支持。

CREATETABLE `users` (
`id` int(11) NOTNULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(32) DEFAULTNULL,
`amount` int(11) DEFAULTNULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


例如：李傑 給 武沛齊 轉賬 100，那就會涉及2個步驟。

這兩個步驟必須同時完成才算完成，並且如果第一個完成、第二步失敗，還是回滾到初始狀態。

事務，就是來解決這種情況的。 大白話：要成功都成功；要失敗都失敗。

事務的具有四大特性（ACID）：

1.1 MySQL客戶端

mysql>select*from users;
+----+---------+---------+
| id | name | amount |
+----+---------+---------+
|1| wupeiqi |5|
|2| alex |6|
+----+---------+---------+
3rowsinset (0.00 sec)
mysql>begin; -- 開啟事務 start transaction;
Query OK, 0rows affected (0.00 sec)
mysql>update users set amount=amount-2where id=1; -- 執行操作
Query OK, 1row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>update users set amount=amount+2where id=2; -- 執行操作
Query OK, 1row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>commit; -- 送出事務 rollback;
Query OK, 0rows affected (0.00 sec)
mysql>select*from users;
+----+---------+---------+
| id | name | amount |
+----+---------+---------+
|1| wupeiqi |3|
|2| ale x |8|
+----+---------+---------+
3rowsinset (0.00 sec)





mysql>select*from users;
+----+---------+---------+
| id | name | amount |
+----+---------+---------+
|1| wupeiqi |3|
|2| ale x |8|
+----+---------+---------+
3rowsinset (0.00 sec)
mysql>begin; -- 開啟事務
Query OK, 0rows affected (0.00 sec)
mysql>update users set amount=amount-2where id=1; -- 執行操作（此時資料庫中的值已修改）
Query OK, 1row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>rollback; -- 事務回滾（回到原來的狀態）
Query OK, 0rows affected (0.00 sec)
mysql>select*from users;
+----+---------+---------+
| id | name | amount |
+----+---------+---------+
|1| wupeiqi |3|
|2| ale x |8|
+----+---------+---------+
3rowsinset (0.00 sec)




1.2 Python程式碼

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor= conn.cursor()
# 開啟事務
conn.begin()
try:
cursor.execute("update users set amount=1 where id=1")
int('asdf')
cursor.execute("update tran set amount=2 where id=2")
except Exception as e:
# 回滾
print("回滾")
conn.rollback()
else:
# 送出
print("送出")
conn.commit()
cursor.close()
conn.close()




2. 鎖

在用MySQL時，不知你是否會疑問：同時有很多做更新、插入、刪除動作，MySQL如何保證數據不出錯呢？

MySQL中內建了鎖的功能，可以幫助我們實作開發過程中遇到的同時處理數據的情況。對於資料庫中的鎖，從鎖的範圍來講有：

MYISAM支持表鎖，不支持行鎖；
InnoDB引擎支持行鎖和表鎖。
即：在MYISAM下如果要加鎖，無論怎麽加都會是表鎖。
在InnoDB引擎支持下如果是基於索引查詢的數據則是行級鎖，否則就是表鎖。



所以，一般情況下我們會選擇使用innodb引擎，並且在 搜尋 時也會使用索引（命中索引）。

接下來的操作就基於innodb引擎來操作：

CREATETABLE `L1` (
`id` int(11) NOTNULL AUTO_INCREMENT,
`name` varchar(255) DEFAULTNULL,
`count` int(11) DEFAULTNULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


在innodb引擎中，update、insert、delete的行為內部都會先申請鎖（排它鎖），申請到之後才執行相關操作，最後再釋放鎖。

所以，當多個人同時像資料庫執行：insert、update、delete等操作時，內部加鎖後會排隊逐一執行。


而select則預設不會申請鎖。

select * from xxx;


如果，你想要讓select去申請鎖，則需要配合 事務 + 特殊語法來實作。

2.1 排它鎖

排它鎖（ for update ），加鎖之後，其他事務不可以讀寫。

套用場景：總共100件商品，每次購買一件需要讓商品個數減1 。

A: 存取頁面檢視商品剩余 100
B: 存取頁面檢視商品剩余 100
此時 A、B 同時下單，那麽他們同時執行SQL：
update goods set count=count-1where id=3
由於Innodb引擎內部會加鎖，所以他們兩個即使同一時刻執行，內部也會排序逐步執行。

但是，當商品剩余 1個時，就需要註意了。
A: 存取頁面檢視商品剩余 1
B: 存取頁面檢視商品剩余 1
此時 A、B 同時下單，那麽他們同時執行SQL：
update goods set count=count-1where id=3
這樣剩余數量就會出現 -1，很顯然這是不正確的，所以應該怎麽辦呢？

這種情況下，可以利用 排它鎖，在更新之前先查詢剩余數量，只有數量 >0 才可以購買，所以，下單時應該執行：
begin; -- start transaction;
select count from goods where id=3forupdate;
-- 獲取個數進行判斷
if 個數>0:
update goods set count=count-1where id=3;
else:
-- 已售罄
commit;




基於Python程式碼範例：

import pymysql
import threading

deftask():
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor(pymysql.cursors.DictCursor)
# cursor = conn.cursor()
# 開啟事務
conn.begin()
cursor.execute("select id,age from tran where id=2 for update")
# fetchall ( {"id":1,"age":10},{"id":2,"age":10}, ) ((1,10),(2,10))
# {"id":1,"age":10} (1,10)
result = cursor.fetchone()
current_age = result['age']
if current_age > 0:
cursor.execute("update tran set age=age-1 where id=2")
else:
print("已售罄")
conn.commit()
cursor.close()
conn.close()

defrun():
for i inrange(5):
t = threading.Thread(target=task)
t.start()

if __name__ == '__main__':
run()








2.2 共享鎖

共享鎖（ lock in share mode ），可以讀，但不允許寫。

加鎖之後，後續其他事物可以可以進行讀，但不允許寫（update、delete、insert），因為寫的預設也會加鎖。

Locking Read Examples

Suppose that you want to insert a new row into a table child , and make sure that the child row has a parent row in table parent . Your application code can ensure referential integrity throughout this sequence of operations.

First, use a consistent read to query the table PARENT and verify that the parent row exists. Can you safely insert the child row to table CHILD ? No, because some other session could delete the parent row in the moment between your SELECT and your INSERT , without you being aware of it.

To avoid this potential issue, perform the SELECT using LOCK IN SHARE MODE :

SELECT*FROM parent WHERE NAME ='Jones' LOCK IN SHARE MODE;


After the LOCK IN SHARE MODE query returns the parent 'Jones' , you can safely add the child record to the CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the PARENT table waits until you are finished, that is, until the data in all tables is in a consistent state.

3. 資料庫連線池

在操作資料庫時需要使用資料庫連線池。

pip3.9install pymysql
pip3.9install dbutils


import threading
import pymysql
from dbutils.pooled_db import PooledDB
MYSQL_DB_POOL = PooledDB(
creator=pymysql, # 使用連結資料庫的模組
maxconnections=5, # 連線池允許的最大連線數，0和None表示不限制連線數
mincached=2, # 初始化時，連結池中至少建立的空閑的連結，0表示不建立
maxcached=3, # 連結池中最多閑置的連結，0和None不限制
blocking=True, # 連線池中如果沒有可用連線後，是否阻塞等待。True，等待；False，不等待然後報錯
setsession=[], # 開始會話前執行的命令列表。如：["set date style to ...", "set time zone ..."]
ping=0,
# ping MySQL伺服端，檢查是否服務可用。
# 如：0 = None = never, 1 = default = whenever it is requested,
# 2 = when a cursor is created, 4 = when a query is executed, 7 = always
host='127.0.0.1',
port=3306,
user='root',
password='root123',
database='userdb',
charset='utf8'
)

deftask():
# 去連線池獲取一個連線
conn = MYSQL_DB_POOL.connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute('select sleep(2)')
result = cursor.fetchall()
print(result)
cursor.close()
# 將連線交換給連線池
conn.close()
defrun():
for i inrange(10):
t = threading.Thread(target=task)
t.start()

if __name__ == '__main__':
run()






4. SQL工具類

基於資料庫連線池開發一個公共的SQL操作類，方便以後操作資料庫。

4.1 單例和方法

# db.py
import pymysql
from dbutils.pooled_db import PooledDB

classDBHelper(object):
def__init__(self):
# TODO 此處配置，可以去配置檔中讀取。
self.pool = PooledDB(
creator=pymysql, # 使用連結資料庫的模組
maxconnections=5, # 連線池允許的最大連線數，0和None表示不限制連線數
mincached=2, # 初始化時，連結池中至少建立的空閑的連結，0表示不建立
maxcached=3, # 連結池中最多閑置的連結，0和None不限制
blocking=True, # 連線池中如果沒有可用連線後，是否阻塞等待。True，等待；False，不等待然後報錯
setsession=[], # 開始會話前執行的命令列表。如：["set date style to ...", "set time zone ..."]
ping=0,
# ping MySQL伺服端，檢查是否服務可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
host='127.0.0.1',
port=3306,
user='root',
password='root123',
database='userdb',
charset='utf8'
)
defget_conn_cursor(self):
conn = self.pool.connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
return conn, cursor
defclose_conn_cursor(self, *args):
for item in args:
item.close()
defexec(self, sql, **kwargs):
conn, cursor = self.get_conn_cursor()
cursor.execute(sql, kwargs)
conn.commit()
self.close_conn_cursor(conn, cursor)
deffetch_one(self, sql, **kwargs):
conn, cursor = self.get_conn_cursor()
cursor.execute(sql, kwargs)
result = cursor.fetchone()
self.close_conn_cursor(conn, cursor)
return result
deffetch_all(self, sql, **kwargs):
conn, cursor = self.get_conn_cursor()
cursor.execute(sql, kwargs)
result = cursor.fetchall()
self.close_conn_cursor(conn, cursor)
return result

db = DBHelper()















from db import db
db.exec("insert into d1(name) values(%(name)s)", name="武沛齊666")
ret = db.fetch_one("select * from d1")
print(ret)
ret = db.fetch_one("select * from d1 where id=%(nid)s", nid=3)
print(ret)
ret = db.fetch_all("select * from d1")
print(ret)
ret = db.fetch_all("select * from d1 where id>%(nid)s", nid=2)
print(ret)





4.2 上下文管理

如果你想要讓他也支持 with 上下文管理。

with 獲取連線：
執行SQL（執行完畢後，自動將連線交還給連線池）


# db_context.py
import threading
import pymysql
from dbutils.pooled_db import PooledDB
POOL = PooledDB(
creator=pymysql, # 使用連結資料庫的模組
maxconnections=5, # 連線池允許的最大連線數，0和None表示不限制連線數
mincached=2, # 初始化時，連結池中至少建立的空閑的連結，0表示不建立
maxcached=3, # 連結池中最多閑置的連結，0和None不限制
blocking=True, # 連線池中如果沒有可用連線後，是否阻塞等待。True，等待；False，不等待然後報錯
setsession=[], # 開始會話前執行的命令列表。如：["set date style to ...", "set time zone ..."]
ping=0,
host='127.0.0.1',
port=3306,
user='root',
password='root123',
database='userdb',
charset='utf8'
)

classConnect(object):
def__init__(self):
self.conn = conn = POOL.connection()
self.cursor = conn.cursor(pymysql.cursors.DictCursor)
def__enter__(self):
return self
def__exit__(self, exc_type, exc_val, exc_tb):
self.cursor.close()
self.conn.close()
defexec(self, sql, **kwargs):
self.cursor.execute(sql, kwargs)
self.conn.commit()
deffetch_one(self, sql, **kwargs):
self.cursor.execute(sql, kwargs)
result = self.cursor.fetchone()
return result
deffetch_all(self, sql, **kwargs):
self.cursor.execute(sql, kwargs)
result = self.cursor.fetchall()
return result







from db_context import Connect
with Connect() as obj:
# print(obj.conn)
# print(obj.cursor)
ret = obj.fetch_one("select * from d1")
print(ret)
ret = obj.fetch_one("select * from d1 where id=%(id)s", id=3)
print(ret)


總結

本節內容比較重要，也是開發中經常會使用到的技能。

為了跟上AI時代我幹了一件事兒，我建立了一個知識星球社群：ChartGPT與副業。想帶著大家一起探索 ChatGPT和新的AI時代 。

有很多小夥伴搞不定ChatGPT帳號，於是我們決定，凡是這三天之內加入ChatPGT的小夥伴，我們直接送一個正常可用的永久ChatGPT獨立帳戶。

不光是增長速度最快，我們的星球品質也絕對經得起考驗，短短一個月時間，我們的課程團隊釋出了 8個專欄、18個副業計畫 ：

簡單說下這個星球能給大家提供什麽：

1、不斷分享如何使用ChatGPT來完成各種任務，讓你更高效地使用ChatGPT，以及副業思考、變現思路、創業案例、落地案例分享。

2、分享ChatGPT的使用方法、最新資訊、商業價值。

3、探討未來關於ChatGPT的機遇，共同成長。

4、幫助大家解決ChatGPT遇到的問題。

5、 提供一整年的售後服務，一起搞副業

星球福利：

1、加入星球4天後，就送ChatGPT獨立帳號。

2、邀請你加入ChatGPT會員交流群。

3、贈送一份完整的ChatGPT手冊和66個ChatGPT副業賺錢手冊。

其它福利還在籌劃中... 不過，我給你大家保證，加入星球後，收獲的價值會遠遠大於今天加入的門票費用 ！

本星球第一期原價 399 ，目前屬於試營運，早鳥價 169 ，每超過50人漲價10元，星球馬上要來一波大的漲價，如果你還在猶豫，可能最後就要以 更高價格加入了 。。

早就是優勢。建議大家盡早以便宜的價格加入！

歡迎有需要的同學試試，如果本文對您有幫助，也請幫忙點個 贊 + 在看 啦！❤️

在 還有更多優質計畫系統學習資源，歡迎分享給其他同學吧！

你還有什 麽想要補充的嗎？

免責聲明：本文內容來源於網路，文章版權歸原作者所有，意在傳播相關技術知識&行業趨勢，供大家學習交流，若涉及作品版權問題，請聯系刪除或授權事宜。

技術君個人微信

添加技術君個人微信即送一份驚喜大禮包

→ 技術資料共享

→ 技術交流社群

--END--

往日熱文：

Python程式設計師深度學習的「四大名著」：

這四本書著實很不錯！我們都知道現在機器學習、深度學習的資料太多了，面對海量資源，往往陷入到「無從下手」的困惑出境。而且並非所有的書籍都是優質資源，浪費大量的時間是得不償失的。給大家推薦這幾本好書並做簡單介紹。

獲得方式：

1.掃碼關註本公眾號

2.後台回復關鍵詞：名著

▲長按掃描關註，回復名著即可獲取