Python基礎入門教程33：企業級開發進階6：數據庫操作

來源：奇酷教育發表于：2017-07-12 09:30:27

python作為一個編程語言，在開發B S或者C S結構的軟件時，不可避免的會設計到和數據庫之間的交互操作，和其他高級的面向對象的語言一樣，Py

python作為一個編程語言，在開發B/S或者C/S結構的軟件時，不可避免的會設計到和數據庫之間的交互操作，和其他高級的面向對象的語言一樣，Python在操作數據庫的過程中，盡量追求了簡潔、統一、易用的風格。

本節內容

mysql數據庫連接驅動的安裝
python連接mysql數據庫
增刪改查（CRUD）數據操作

注意，關于mysql數據庫的教程，我們后續還在其他的模塊進行總結添加，如果大家需要的話^_^畢竟現在網絡上關于這樣常規的技術教程還是非常多滴

1. mysql數據庫連接驅動的安裝

1.1. 親，請明白為什么要有數據庫連接驅動

首先：我們明白，編程語言和數據庫各自都是什么
編程語言：專門用于進行數據處理的獨立的個體
數據庫：專門用于進行數據儲存的獨立的個體
也就是說，編程語言和數據庫本身是兩個完全獨立的個體，為了讓數據能更加優雅的持久的存儲和處理，編程語言就得和數據庫配合完成我們的工作

因為編程語言如果獨立處理數據的話，程序是運行在系統的內存中的，如果程序一旦終止，意味著處理的數據就會丟失。為了持久的有效的保存數據，我們選擇將處理的數據保存在數據庫中

其次：編程語言，憑什么可以訪問數據庫
數據庫給編程語言專門開了一個后門（API），通過這個后門（API）就可以讓編程語言對數據庫中的數據進行增刪改查操作了。當然，必須得拿著數據庫提供給編程語言的正確的鑰匙才是可以的哦【鑰匙：數據庫連接驅動+連接憑證】

最后：OK，此時，我們明白了，編程語言為什么和數據庫配合使用，為什么要有連接驅動，接下來，進入我們的安裝環節

python操作數據庫，其實就是兩個獨立個體之間的數據通信，和我們現實生活一樣，需要中間連接兩個獨立的人之間的手機和正確的電話號碼

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，如下圖

python驅動版本

如果你跟我一樣，在一臺電腦上安裝了python2.7和python3.6的版本，尤其是目前使用的是python3.6的版本，上述安裝驅動方式就會出現版本不支持的錯誤，錯誤信息如下：

python3.4+版本安裝驅動報錯提示

1.3. 沒事，有我在！

如果是對于Python3.4+的版本，mysql官方提供的驅動已經不滿足我們的需要，此時需要安裝一個第三方的驅動來完成和數據庫的連接支持

這個神奇的第三方數據庫就是：PyMySQL

接下來，安裝它：

python3 -m pip install pymysql

安裝過程如下圖所示：

安裝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三種類型的操作，分別操作了不同的數據

操作數據之間數據庫中的數據

python數據庫基本操作

上述程序運行結束之后數據庫中的數據，仔細觀察

python數據庫基本操作

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表中的數據

執行上述程序,數據庫中就出現對應的數據

pymysql批量執行增加數據操作

但是，我們要說的是但是
executemany(sql, args)函數只是適合執行多條數據，但是不要去執行大量數據（如執行幾千幾萬條數據）
這是為什么呢？
因為常規項目中，會有批量刪除、修改等操作，但是常規項目中的批量只是幾十條數據，為了簡化操作python提供了executemany()函數來實現了這樣的功能
但是大量數據操作，使用executemany()反倒會影響執行效率，讓數據庫操作變得緩慢，此時建議根據不同的數據庫使用多條sql語句拼接的方式來實現。