33.企業級開發進階6：資料庫操作
python作為一個程式語言，在開發B/S或者C/S結構的軟體時，不可避免的會設計到和資料庫之間的互動操作，和其他高階的物件導向的語言一樣，Python在運算元據庫的過程中，儘量追求了簡潔、統一、易用的風格。
本節內容
- mysql資料庫連線驅動的安裝
- python連線mysql資料庫
- 增刪改查（CRUD）資料操作
注意，關於mysql資料庫的教程，我們後續還在其他的模組進行總結新增，如果大家需要的話^_^畢竟現在網路上關於這樣常規的技術教程還是非常多滴
1. mysql資料庫連線驅動的安裝
1.1. 親，請明白為什麼要有資料庫連線驅動
首先：我們明白，程式語言和資料庫各自都是什麼 程式語言：專門用於進行資料處理的獨立的個體 資料庫：專門用於進行資料儲存的獨立的個體 也就是說，程式語言和資料庫本身是兩個完全獨立的個體，為了讓資料能更加優雅的持久的儲存和處理，程式語言就得和資料庫配合完成我們的工作
因為程式語言如果獨立處理資料的話，程式是執行在系統的記憶體中的，如果程式一旦終止，意味著處理的資料就會丟失。為了持久的有效的儲存資料，我們選擇將處理的資料儲存在資料庫中
其次：程式語言，憑什麼可以訪問資料庫 資料庫給程式語言專門開了一個後門（API），通過這個後門（API）就可以讓程式語言對資料庫中的資料進行增刪改查操作了。當然，必須得拿著資料庫提供給程式語言的正確的鑰匙才是可以的哦【鑰匙：資料庫連線驅動+連線憑證】
最後：OK，此時，我們明白了，程式語言為什麼和資料庫配合使用，為什麼要有連線驅動，接下來，進入我們的安裝環節
python運算元據庫，其實就是兩個獨立個體之間的資料通訊，和我們現實生活一樣，需要中間連線兩個獨立的人之間的手機和正確的電話號碼
1.2. 親，出錯了~
安裝資料庫驅動，我們想到的第一件事應該是搜尋官方文件或者問問度娘/谷哥，得到結果如下： ```
# 安裝mysql的python語言的資料庫連線驅動
pip install mysql-connector-python --allow-exrternal mysql-connector-python
```
請注意：如果你使用的python版本是2.7或者3.4以下版本，是不會有任何問題的，因為mysql官方提供的驅動支援的最高版本是Python2.7或者python3.4，如下圖
如果你跟我一樣，在一臺電腦上安裝了python2.7和python3.6的版本，尤其是目前使用的是python3.6的版本，上述安裝驅動方式就會出現版本不支援的錯誤，錯誤資訊如下：
1.3. 沒事，有我在！
如果是對於Python3.4+的版本，mysql官方提供的驅動已經不滿足我們的需要，此時需要安裝一個第三方的驅動來完成和資料庫的連線支援
這個神奇的第三方資料庫就是：PyMySQL
接下來，安裝它： ```
python3 -m pip install pymysql
```
安裝過程如下圖所示：
安裝完成後，可以通過import引入到我們的python程式中哦
注意：python2和python3連運算元據庫的方式稍有差異，python2.x運算元據庫主要使用的是mysqldb模組；python3.x運算元據庫我們選擇使用pymysql。當然，操作方式是一樣的，並沒有什麼太大區別
2. python連線mysql資料庫
我們在前面的內容中，已經安裝好了資料庫連線驅動，接下來，通過python程式來連線資料庫 廢話不多，上乾貨： ```
# 引入我們需要的運算元據庫模組
import pymysql
# 連線資料庫
conn = pymysql.connect(
host="localhost", # 資料庫主機IP地址
user="root", # 資料庫登入賬號
password="", # 資料庫登入密碼
database="pydb", # 要連線的資料庫
port=3306, # 連線資料庫的埠號
charset="utf-8" # 使用指定編碼連線資料庫
)
請記住上面的程式碼，連線資料庫就是這麼簡單！
有人說~我記不住怎麼辦，記不住那麼多資訊，可以記住pymysql.connect()，這樣總是可以的吧，然後進入pymysql提供的connections.py原始碼中就可以看到connect()方法，它是這麼寫的
def __init__(self, host=None, user=None, password="",
database=None, port=0, unix_socket=None,
charset='', sql_mode=None,
read_default_file=None, conv=None, use_unicode=None,
client_flag=0, cursorclass=Cursor, init_command=None,
connect_timeout=10, ssl=None, read_default_group=None,
compress=None, named_pipe=None, no_delay=None,
autocommit=False, db=None, passwd=None, local_infile=False,
max_allowed_packet=16*1024*1024, defer_connect=False,
auth_plugin_map={}, read_timeout=None, write_timeout=None,
bind_address=None):
``` 上述pymysql的connections.py中上面的程式碼的意思比較簡單，每一個引數都通過引數名稱我們基本就能明白引數是什麼意義了。常用的也就那麼幾個。
3. python運算元據庫中的資料
首先，我們開啟mysql資料庫編輯工具（這裡我使用的是sqlyog操作mysql，大家可以隨意），建立使用者表（我們將資料庫表建立的稍微正式點）： ```
# 建立資料庫
CREATE DATABASE pydb;
# 指定使用資料庫
USE pydb;
# 建立使用者表
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL COMMENT '使用者賬號',
userpass VARCHAR(50) NOT NULL COMMENT '登入密碼',
nickname VARCHAR(50) COMMENT '暱稱',
age INT COMMENT '年齡',
gender VARCHAR(5) COMMENT '性別',
phone VARCHAR(15) COMMENT '聯絡方式',
email VARCHAR(50) COMMENT '郵箱',
createTime DATETIME COMMENT '賬號建立時間',
updateTime DATETIME COMMENT '賬號最後修改時間',
lastLogin DATETIME COMMENT '賬號最後登入時間',
usersFlag INT COMMENT '賬號狀態：0 正常 1 鎖定 2 刪除',
remark TEXT COMMENT '備註'
) DEFAULT CHARSET "utf8";
# 增加測試資料
INSERT INTO users(username, userpass, nickname, age, gender, phone, email, createTime, updateTime, lastLogin, usersFlag, remark)
VALUES("tom", "123", "凱特", 48, "男", "13868686868", "cat@163.com", "2017-06-01","2017-06-02","2017-06-05",0,"tom and jerry 管理員"),
("jerry", "111", "傑瑞", 46, "女", "15688888888", "mouse@163.com", "2017-06-01","2017-06-03","2017-06-04",0,"tom and jerry 管理員");
```
3.1. 運算元據庫資料的步驟
- 連線資料庫
- 獲取一個訪問資料庫的操作物件
- 定義SQL語句
- 執行SQL語句
- 處理結果
- 關閉和資料庫之間的連線
我們使用python運算元據庫，開啟和資料庫的連線並維持連線是需要消耗系統資源滴，切記操作完成之後一定要關閉和資料庫之間的連線
3.2. 查詢資料庫中的資料
核心API： executer(sql)：執行指定的sql語句，返回影響的行數 fetchall()：獲取SQL操作的所有資料 fetchone()：獲取SQL操作的第一條資料
接下來，上乾貨： ```
# 引入我們需要的運算元據庫模組
import pymysql
# 資料庫連線資訊
HOST = "localhost"
USER = "root"
PASSWORD = ""
DATABASE = "pydb"
PORT = 3306
CHARSET = "utf8"
# 使用異常包含處理過程，方便在finally中關閉和資料庫的連線
try:
# 連線資料庫
conn = pymysql.connect(
host=HOST, # 資料庫主機IP地址
user=USER, # 資料庫登入賬號
password=PASSWORD, # 資料庫登入密碼
database=DATABASE, # 要連線的資料庫
port=PORT, # 連線資料庫的埠號
charset=CHARSET # 使用指定編碼連線資料庫
)
# 獲取執行物件
cursor = conn.cursor();
# 定義查詢sql語句
sql = "select * from users"
# 執行sql語句
rows = cursor.execute(sql)
# 獲取查詢結果
result = cursor.fetchall()
# 遍歷查詢結果
for user in result:
print("userid<%d>username<%s>userpass<%s>nickname<%s>createTime<%s>"
% (user[0], user[1], user[2], user[3], user[8]))
except Exception as e:
print("執行過程出現異常<%s>" % str(e))
finally:
# 不論是否出現異常，執行完成後，保證資料庫連線關閉
cursor.close()
conn.close()
``` 執行上述程式碼，返回如下預期的結果
userid<1>usernameuserpass<123>nickname<凱特>createTime<2017-06-01 00:00:00> userid<2>usernameuserpass<111>nickname<傑瑞>createTime<2017-06-01 00:00:00>
下面是我們操作的過程中，明確操作結果就是一條資料的情況下 ```
import pymysql
# 資料庫連線資訊
HOST = "localhost"
USER = "root"
PASSWORD = ""
DATABASE = "pydb"
PORT = 3306
CHARSET = "utf8"
# 使用異常包含處理過程，方便在finally中關閉和資料庫的連線
try:
# 連線資料庫
conn = pymysql.connect(
host=HOST, # 資料庫主機IP地址
user=USER, # 資料庫登入賬號
password=PASSWORD, # 資料庫登入密碼
database=DATABASE, # 要連線的資料庫
port=PORT, # 連線資料庫的埠號
charset=CHARSET # 使用指定編碼連線資料庫
)
# 獲取執行物件
cursor = conn.cursor()
# 定義sql語句
sql = "select * from users"
# 執行sql語句
rows = cursor.execute(sql)
# 抓取查詢結果：獲取結果中的第一條資料
result = cursor.fetchone()
print("result:%s--%s--%s--%s" % (result[0], result[1], result[2], result[3]))
except Exception as e:
print("出現異常<%s>" % str(e))
finally:
# 關閉資料庫連線
cursor.close()
conn.close()
``` 執行上述程式碼，可以看到資料也是正常獲取的
result:1--tom--123--凱特
3.3. 新增/更新/刪除資料到資料庫
廢話不說，直接上程式碼，一定要看註釋啊 ```
# 引入資料庫模組
import pymysql
# 定義資料庫連線資訊
HOST = "localhost"
USER = "root"
PASSWORD = ""
DATABASE = "pydb"
PORT = 3306
CHARSET = "utf8"
try:
# 連線資料庫
conn = pymysql.connect(
host=HOST,
user=USER,
password=PASSWORD,
database=DATABASE,
port=PORT,
charset=CHARSET
)
# 獲取執行物件
cursor = conn.cursor()
"""
增加資料到資料庫的操作：insert
"""
# 定義sql語句
insertSql = 'INSERT INTO users(username, userpass, nickname, age, gender, phone, email, createTime, updateTime, lastLogin, usersFlag, remark)\
VALUES("shuke", "123", "舒克", 42, "男", "15686868686", "shuke@163.com", "2017-06-01","2017-06-02","2017-06-05",0,"shuke and beita")'
# 執行sql語句
rows = cursor.execute(insertSql)
# 將更改的資料提交更新
conn.commit()
print("共有%d條資料被新增到資料庫中了" % rows)
"""
更新資料到資料庫的操作：update
"""
# 定義sql語句
updateSql = 'update users set nickname = "凱特大叔" where id = 1'
# 執行sql語句
rows = cursor.execute(updateSql)
# 將更改的資料提交更新
conn.commit()
print("共有%d條資料在資料庫中被修改了" % rows)
"""
從資料庫中刪除資料：delete
"""
# 定義sql語句
deleteSql = 'delete from users where id = 2'
# 執行sql語句
rows = cursor.execute(deleteSql)
# 將刪除資料進行提交更新
conn.commit()
print("共有%d條資料在資料庫中被刪除了" % rows)
except Exception as e:
print("出現異常<%s>" % str(e))
finally:
cursor.close()
conn.close()
``` 上述程式碼，包含了基本的insert/update/delete三種型別的操作，分別操作了不同的資料
運算元據之間資料庫中的資料
上述程式執行結束之後資料庫中的資料，仔細觀察
3.4. 使用佔位符進行資料操作【需要掌握】
在SQL操作的過程中，如果我們通過將SQL字串和對應的資料通過拼接來操作的話，會變得非常的麻煩，大家可以試試上面的程式中的資料，如果都是使用者輸入的，然後增加到SQL語句中，會是什麼樣的場景
所以有了佔位符的方式，來簡化資料和SQL語句之間的操作，廢話不多，程式碼大家一看就懂，上乾貨： ```
# 引入資料庫模組
import pymysql
# 定義資料庫連線資訊
HOST = "localhost"
USER = "root"
PASSWORD = ""
DATABASE = "pydb"
PORT = 3306
CHARSET = "utf8"
try:
# 連線資料庫
conn = pymysql.connect(
host=HOST,
user=USER,
password=PASSWORD,
database=DATABASE,
port=PORT,
charset=CHARSET
)
# 獲取執行物件
cursor = conn.cursor()
"""
增加資料到資料庫的操作：insert
"""
# 定義sql語句
insertSql = 'INSERT INTO users(username, userpass, nickname, age, gender, phone, email, createTime, updateTime, lastLogin, usersFlag, remark)\
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
# 執行sql語句
rows = cursor.execute(insertSql, ("shuke", "123", "舒克", 42, "男", "15686868686", "shuke@163.com", "2017-06-01","2017-06-02","2017-06-05",0,"shuke and beita"))
# 將更改的資料提交更新
conn.commit()
print("共有%d條資料被新增到資料庫中了" % rows)
"""
更新資料到資料庫的操作：update
"""
# 定義sql語句
updateSql = 'update users set nickname = %s where id = %s'
# 執行sql語句
rows = cursor.execute(updateSql, ["凱特大叔", 1])
# 將更改的資料提交更新
conn.commit()
print("共有%d條資料在資料庫中被修改了" % rows)
"""
從資料庫中刪除資料：delete
"""
# 定義sql語句
deleteSql = 'delete from users where id = %s'
# 執行sql語句
rows = cursor.execute(deleteSql, 1)
# 將刪除資料進行提交更新
conn.commit()
print("共有%d條資料在資料庫中被刪除了" % rows)
except Exception as e:
print("出現異常<%s>" % str(e))
finally:
cursor.close()
conn.close()
``` 上述程式碼的執行操作，和前面的基本操作是一致的，大家可以試試。
3.5. 批量操作及效能優化建議
在python中，為了方便進行批量資料的處理【批量資料增加、修改、刪除等操作】提供了一個executemany()函式，操作方式和佔位符的方式有點類似
直接上乾貨 ```
# 引入資料庫模組
import pymysql
# 定義資料庫連線資訊
HOST = "localhost"
USER = "root"
PASSWORD = ""
DATABASE = "pydb"
PORT = 3306
CHARSET = "utf8"
try:
# 連線資料庫
conn = pymysql.connect(
host=HOST,
user=USER,
password=PASSWORD,
database=DATABASE,
port=PORT,
charset=CHARSET
)
# 獲取執行物件
cursor = conn.cursor()
"""
增加資料到資料庫的操作：使用佔位符進行批量操作
"""
# 定義sql語句
insertSql = 'INSERT INTO users(username, userpass, nickname, age) VALUES(%s, %s, %s, %s)'
args = [("member1", "123", "會員1", 12),
("member2", "123", "會員2", 34),
("member3", "123", "會員3", 23),
("member4", "123", "會員4", 42)]
# 執行sql語句
rows = cursor.executemany(insertSql, args)
# 將更改的資料提交更新
conn.commit()
print("共有%d條資料被新增到資料庫中了" % rows)
except Exception as e:
print("出現異常<%s>" % str(e))
finally:
cursor.close()
conn.close()
``` 上述程式碼中，我們可以看到，sql語句只是定義了一條語句，但是在後面的引數卻是一個列表，列表中包含了多條資料值，執行的時候多條資料值會一起插入到資料庫中
開啟sqlyog，執行情況資料表users 的操作
```
truncate table users; # 清空users表中的資料
``` 執行上述程式,資料庫中就出現對應的資料
但是，我們要說的是但是 executemany(sql, args)函式只是適合執行多條資料，但是不要去執行大量資料（如執行幾千幾萬條資料） 這是為什麼呢？ 因為常規專案中，會有批量刪除、修改等操作，但是常規專案中的批量只是幾十條資料，為了簡化操作python提供了executemany()函式來實現了這樣的功能 但是大量資料操作，使用executemany()反倒會影響執行效率，讓資料庫操作變得緩慢，此時建議根據不同的資料庫使用多條sql語句拼接的方式來實現。
相關文章
- 29.企業級開發進階1：檔案輸入輸出流[IO操作]
- TiDB 6.0 發版：向企業級雲資料庫邁進TiDB資料庫
- 32.企業級開發進階4：正規表示式
- 30.2. 企業級開發進階2.2：TCP實戰TCP
- 30.4. 企業級開發進階2.4：服務端快捷開發服務端
- 企業資料庫安全管理規範 | 運維進階資料庫運維
- 31.2企業級開發進階3.2：接收電子郵件
- 30.企業級開發進階2：網路程式設計程式設計
- 30.1. 企業級開發進階2.1：TCP程式設計TCP程式設計
- ArcGIs建立企業級資料庫資料庫
- Linux企業級開發技術（6）——libevent企業級開發之記憶體管理Linux記憶體
- 雲資料庫將進入企業級百萬IOPS時代資料庫
- 十大企業級資料庫資料庫
- 企業級雲資料庫最佳實踐資料庫
- 企業級開發框架----------Django框架Django
- 資料庫開發（21）高階應用開發資料庫
- 打造企業級開源資料庫 服務關基行業核心系統資料庫行業
- Linux企業級開發技術（1）——epoll企業級開發之簡介Linux
- Linux企業級開發技術（2）——epoll企業級開發之epoll介面Linux
- Linux企業級開發技術（3）——epoll企業級開發之epoll模型Linux模型
- Linux企業級開發技術（4）——epoll企業級開發之epoll例程Linux
- 【資料庫】Redis進階篇資料庫Redis
- 使用NineData定製企業級資料庫規範資料庫
- 資料庫——基礎（資料庫操作，表格操作）——增加高階查詢資料庫
- Sentry 企業級資料安全解決方案 - Relay 操作指南
- 【進階之路】詳解資料庫事物與隔離級別資料庫
- iOS資料庫技術進階iOS資料庫
- Java中JDBC進階教程之資料庫的隔離級別！JavaJDBC資料庫
- 25. 企業級開發基礎6：物件導向特徵（繼承）物件特徵繼承
- 自定義開發資料庫升級程式資料庫
- 資料庫發展階段之資料庫系統階段資料庫
- 對話李飛飛:下一代企業級資料庫6大技術方向資料庫
- 企業級 Web 開發的挑戰Web
- python資料庫-MySQL資料庫高階查詢操作(51)Python資料庫MySql
- AndoridSQLite資料庫開發基礎教程（6）SQLite資料庫
- Linux企業級開發技術（7）——libevent企業級開發之鎖和執行緒Linux執行緒
- Java EE 6之企業應用開發Java
- 鵝廠資料庫的進階之路資料庫