26. Python 對 mysql 的操作

標籤：python mysql

mysql資料庫現在已經成為市場佔有率最高的資料庫，在開發過程中，很多情況下我們都需要操作mysql，所以對於python操作mysql的瞭解是必不可少的。

Python標準資料庫介面為Python DB-API, Python DB-API為開發人員提供了資料庫應用 編程介面。

參考地址：https://wiki.python.org/moin/DatabaseInterfaces，你可以查看python支援資料庫的詳細列表。

不同的資料庫需要下載不同的DB API模組。

DB-API是一個規範。它定義了一系列必須的對象和資料庫存取方式, 以便為各種各樣的底層資料庫系統和多種多樣的資料庫介面程式提供一致的提供者。

Python的DB-API,為大多數的資料庫實現了介面,使用它串連各資料庫後,就可以用相同 的方式操作各資料庫。

Python DB-API使用流程：

1.引入API模組。

2.擷取與資料庫的串連。

3.執行SQL語句和預存程序。

4.關閉資料庫連接。

1. 安裝所需要的包

MySQLdb 是用於Python連結Mysql資料庫的介面，它實現了 Python 資料庫 API 規範 V2.0，基於 MySQL C API 上建立的。

如果是windows系統：登入 https://pypi.python.org/pypi/MySQL-python/1.2.5 找到.exe結尾的包；

下載安裝就好了，然後在cmd中執行：

650) this.width=650;" src="https://s2.51cto.com/oss/201711/20/64ae193ccae5be8f8d75eb9631a50c73.png" title="clipboard.png" alt="64ae193ccae5be8f8d75eb9631a50c73.png" />

如果結果如所示，就說明你安裝成功了，如果有如下報錯資訊，

>>> import MySQLdb

Traceback (most recent call last):

File "<stdin>", line 1, in <module>

ImportError: No module named MySQLdb

那是環境變數有問題，把安裝剛才下載的.exe包的路徑添加到環境變數中就可以了。

如果是linux系統，可以下載源碼包進行安裝：

連結中的zip包，然後安裝：

# yum install –y python-devel

# yum install –y mysql-devel

# yum install –y gcc

解壓：

# unzip MySQL-python-1.2.5.zip

# cd MySQL-python-1.2.5

# python setup.py build

# python setup.py install

# python

>>> import MySQLdb

2.資料庫的串連

MySQLdb提供了connect方法用來和資料庫建立串連,接收數個參數,返回連線物件：

平常我們在Mysql的資料庫中手動建立python庫的方法：

> create database python;

通過以下代碼進行python庫的串連：

conn=MySQLdb.connect(host="192.168.48.128",user="test",passwd="123456",db="python",port=3306,charset="utf8")

包含了幾種參數：

host:資料庫主機名稱.預設是用本地主機

user:資料庫登陸名.預設是目前使用者

passwd:資料庫登陸的秘密.預設為空白

db:要使用的資料庫名.沒有預設值

port:MySQL服務使用的TCP連接埠.預設是3306，數字類型

charset:資料庫編碼字元集

更多參數的資訊可以查這裡 http://mysql-python.sourceforge.net/MySQLdb.html

有時候，為了代碼規範，推薦把所有資料庫的配置寫在一個字典中，如下所示：

def connect_mysql():    db_config = {        ‘host‘: ‘192.168.48.128‘,        ‘port‘: 3306,        ‘user‘: ‘xiang‘,        ‘passwd‘: ‘123456‘,        ‘db‘: ‘python‘,        ‘charset‘: ‘utf8‘    }    cnx = MySQLdb.connect(**db_config)    return cnx

這樣寫的代碼更規範，即使以後資料庫有協議，只需要改動 db_config 字典中的內容就可以了，後面的內容就不用改了，增加代碼的可移植性；

也可以把 mysql 的串連封裝成一個函數，以後在串連 mysql 的時候，直接調用函數就可以了！

3.Mysql事務

瞭解什麼是mysql的事物

MySQL 事務主要用於處理操作量大，複雜度高的資料。比如，你操作一個資料庫，公司的一個員工離職了，你要在資料庫中刪除他的資料，也要刪除該人員相關的，比如郵箱，個人資產等。這些資料庫操作語言就構成了一個事務。在MySQL中只有使用了Innodb資料庫引擎的資料庫或表才支援事務，所以很多情況下我們都使用innodb引擎。交易處理可以用來維護資料庫的完整性，保證成批的SQL語句要麼全部執行，要麼全部不執行。

一般來說，事務是必須滿足4個條件（ACID）： Atomicity（原子性）、Consistency（穩定性）、Isolation（隔離性）、Durability（可靠性）

1、事務的原子性：一組事務，要麼成功；要麼撤回。

2、穩定性 ： 有非法資料（外鍵約束之類），事務撤回。

3、隔離性：事務獨立運行。一個交易處理後的結果，影響了其他事務，那麼其他事務會撤回。事務的100%隔離，需要犧牲速度。

4、可靠性：軟、硬體崩潰後，InnoDB資料表驅動會利用記錄檔重構修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit選項 決定什麼時候吧事務儲存到日誌裡。

mysql在預設的情況下，是把每個select，insert，update，delete等做為一個事務的，登入mysql伺服器，進入mysql，執行以下命令：

mysql> show variables like ‘auto%‘;

+----------------------------------+--------+| Variable_name                    | Value  |+----------------------------------+--------+| auto_increment_increment         | 1      || auto_increment_offset            | 1      || autocommit                       | ON     || automatic_sp_privileges          | ON     |+----------------------------------+--------+4 rows in set (0.00 sec)

如上所示：

有一個參數 autocommit 就是自動認可的意思，每執行一個msyql的 select，insert，update 等操作，就會進行自動認可。

如果把改選項關閉，我們就可以每次執行完一次代碼就需要進行手動提交，connect 對象給我們提供了兩種辦法來操作提交資料。

4. mysql事務的方法

commit():提交當前事務，如果是支援事務的資料庫執行增刪改後沒有commit則資料庫預設復原，白操作了

rollback():取消當前事務

下面我們來看個例子：

建立一個員工表：

> create table employees (

emp_no int not null auto_increment,

emp_name varchar(16) not null,

gender enum(‘M‘, ‘F‘) not null,

hire_date date not null,

primary key (emp_no)

);

emp_no:         員工id，為主鍵且唯一

emp_name:    員工的名字

fender：         性別，只有M和F兩種選擇

hire_date：    僱傭的時間。

插入幾條資料：

> insert into employees(emp_no, emp_name, gender, hire_date) values(1001, ‘li‘, ‘M‘, ‘2015-04-01‘);

> insert into employees(emp_no, emp_name, gender, hire_date) values(1002, ‘xian‘, ‘M‘, ‘2015-04-01‘);

> insert into employees(emp_no, emp_name, gender, hire_date) values(1003, ‘sheng‘, ‘M‘, ‘2015-04-01‘);

> select * from employees;

+-----------+----------------+----------+----------------+| emp_no    | emp_name       | gender   | hire_date      |+-----------+----------------+----------+----------------+|   1001    | li             | M        | 2015-04-01     ||   1002    | xian           | M        | 2015-04-01     ||   1003    | sheng          | M        | 2015-04-01     |+-----------+----------------+----------+----------------+

通過python代碼增加一條資料到資料庫中，代碼如下：

import MySQLdbdef connect_mysql():    db_config = {        ‘host‘: ‘192.168.48.128‘,        ‘port‘: 3306,        ‘user‘: ‘xiang‘,        ‘passwd‘: ‘123456‘,        ‘db‘: ‘python‘,        ‘charset‘: ‘utf8‘    }    cnx = MySQLdb.connect(**db_config)    return cnx    if __name__ == ‘__main__‘:    cnx = connect_mysql()    cus = cnx.cursor()    sql  = ‘‘‘ create table test(id int not null);insert into test(id) values (100);‘‘‘    try:        cus.execute(sql)        cus.close()        cnx.commit()    except Exception as e:        cnx.rollback()        print(‘Error‘)        # raise e    finally:        cnx.close()

結果返回：

Error

查看資料庫中的資料：select * from employees;

並沒有發生變化

解釋：

在我們插入資料僱傭時間欄位 hire_date 的時候，故意把時間寫錯，導致異常發生，捕獲到異常之後，列印 Error，最後關閉mysql串連。

cus = cnx.cursor() 的作用是建立一個遊標對象。

本文出自 “筆記空間” 部落格，請務必保留此出處http://286577399.blog.51cto.com/10467610/1983631

26. Python 對 mysql 的操作