Python 操作MySQL資料庫

標籤：

閱讀目錄

• 簡單介紹
• 環境準備
• 執行個體操作[建立、插入]
  

1. 連結MySQL的testdb資料庫並擷取一個欄位的屬性
2. 建立一個VFORBOX資料表
3. 執行 SQL INSERT 語句向表 VFORBOX 插入記錄
4. 使用變數向SQL語句中傳遞參數

• 執行個體操作[查詢、更新]

1. 查詢VFORBOX表中name（姓名）欄位等於 vforbox 的所有資料
2. 將 VFORBOX表中的 SEX 欄位全部修改為 ‘M‘，AGE 欄位遞增1

• DB-API錯誤處理

 

此篇隨筆是 Python 操作MySQL資料庫，下面我將把我自己所理解的筆記整理出來，以作為參考，希望對讀者有協助

為了讓讀者能夠看懂筆者的隨筆 我將顏色加深了

簡單介紹

• Python 標準資料庫介面為"Python DB-API",而"Python DB-API"為開發人員提供了資料庫應用編程介面
• Python 資料庫介面支援非常多的資料庫這裡舉例五種

1. MySQL
2. mSQL
3. PostgreSQL
4. Microsoft SQL Server
5. Oracle

• 如果想要知道更多，可以訪問官網Python資料庫介面及API查看詳細的支援資料庫列表
• 對於不同的資料庫需要下載不同的DB API模組，比如需要訪問Mysql資料庫和Oracle資料庫，則需要下載MySQL和Oracle資料庫模組
• Python的DB-API，為多數的資料庫實現了介面，使用它串連各資料庫後，就可以用相同的方式操作各資料庫
• Python DB-API操作流程

1. 匯入 DB-API 模組
2. 擷取與資料庫的串連
3. 執行SQL語句和預存程序
4. 關閉資料庫連接

此篇隨筆是 Python 操作MySQL資料庫，所以需要在作業系統安裝MySQLdb模組來支援對Python操作Mysql資料庫

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

回到頂部環境準備

• 首先為了用Python的MySQLdb操作MySQL之前的準備條件

　　† 必須確保作業系統（Linux CentOS）之上已經已經安裝了MySQL資料庫

[[email protected] ~]# rpm -qa | grep mysql                   //查詢系統中是否安裝Mysql[[email protected] ~]# yum -y install mysql mysql-server      //安裝Mysql[[email protected] ~]# service mysqld start   //啟動Mysql[[email protected] ~]# chkconfig mysqld on   //設定Mysql開機啟動

　　† 在MySQL資料庫上建立資料庫 testdb

mysql> show databases;       //顯示所有資料庫+--------------------+| Database           |+--------------------+| information_schema || mysql              || test               |+--------------------+3 rows in set (0.00 sec)mysql> create database testdb;                         //建立testdb資料庫Query OK, 1 row affected (0.00 sec)

　　† 在testdb資料庫中建立資料表 testtab

　　† testdb表欄位裡面有 id,name,age,sex

mysql> use testdb;   //進入testdb資料庫Database changedmysql> create table testtab(                           //建立testtab資料表    -> id int(4),    -> name varchar(255),    -> age int(3),    -> sex varchar(1)    -> );Query OK, 0 rows affected (0.01 sec)mysql> desc testtab;   //顯示資料表屬性+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id    | int(4)       | YES  |     | NULL    |       || name  | varchar(255) | YES  |     | NULL    |       || age   | int(3)       | YES  |     | NULL    |       || sex   | varchar(1)   | YES  |     | NULL    |       |+-------+--------------+------+-----+---------+-------+4 rows in set (0.00 sec)

　　† 授權串連資料庫testdb使用的使用者名稱為"root" ，密碼為 "test123"，所有許可權

mysql> grant all privileges on testdb.* to ‘root‘@‘%‘ identified by ‘test123‘ with grant option;mysql> flush privileges;  //重新整理剛才修改的許可權，使其生效Query OK, 0 rows affected (0.00 sec)

　　† 能串連MySQL保證系統中安裝了 Python MySQLdb 模組

　　† 如果匯入MySQLdb的輸出結果如下所示，意味著沒有安裝 MySQLdb 模組

[[email protected] ~]# pythonPython 2.6.6 (r266:84292, Nov 22 2013, 12:16:22)[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> import MySQLdbTraceback (most recent call last):  File "<stdin>", line 1, in <module>ImportError: No module named MySQLdb

　　† 安裝MySQLdb，點擊進入從這裡可選擇適合您的平台的安裝包，分為先行編譯的二進位檔案和原始碼安裝包

　　† 這裡使用源碼包進行安裝,先把依賴包進行安裝,不讓等下執行安裝的時候會出現很多錯誤...

[[email protected] ~]# yum install -y python-devel mysql-devel zlib-devel openssl-devel python-setuptools

　　† 下載MySQL-Python 並解壓、安裝

[[email protected] ~]# cd /usr/local/src/[[email protected] src]# wget http://download.sourceforge.net/sourceforge/mysql-python/MySQL-python-1.2.3.tar.gz[[email protected] src]# tar zxf MySQL-python-1.2.3.tar.gz[[email protected] src]# cd MySQL-python-1.2.3[[email protected] MySQL-python-1.2.3]# python setup.py build[[email protected] MySQL-python-1.2.3]# python setup.py install

　　† 如果匯入MySQLdb的輸出沒有任何錯誤，意味著安裝 MySQLdb 模組成功

[[email protected] ~]# pythonPython 2.6.6 (r266:84292, Nov 22 2013, 12:16:22)[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> import MySQLdb>>>

回到頂部執行個體操作[建立、插入]

• 連結MySQL的testdb資料庫並擷取一個欄位的屬性

#!/usr/bin/python# -*- coding: UTF-8 -*-#匯入MySQLdb模組import MySQLdb#開啟資料庫連接#host 主機地址、user 使用者、passwd 密碼 、db 資料庫名稱、port 資料庫連接埠conn=MySQLdb.connect(host=‘192.168.1.187‘,user=‘root‘,passwd=‘test123‘,db=‘testdb‘,port=3306)# 使用cursor()方法擷取操作遊標cur=conn.cursor()# 使用execute方法執行SQL語句cur.execute("describe testtab")# 使用 fetchone() 方法擷取一個欄位的屬性data=cur.fetchone()#列印data這條資料print data# 關閉資料庫連接conn.close()

　　† 執行以上指令碼的輸出結果

(‘id‘, ‘int(4)‘, ‘YES‘, ‘‘, None, ‘‘)

• 建立一個VFORBOX資料表

#!/usr/bin/python# -*- coding: UTF-8 -*-#匯入MySQLdb模組import MySQLdb#開啟資料庫連接#host 主機地址、user 使用者、passwd 密碼 、db 資料庫名稱、port 資料庫連接埠conn=MySQLdb.connect(host=‘192.168.1.187‘,user=‘root‘,passwd=‘test123‘,db=‘testdb‘,port=3306)# 使用cursor()方法擷取操作遊標cur=conn.cursor()#建立資料庫sql = """CREATE TABLE VFORBOX (         NAME  CHAR(20) NOT NULL,         AGE INT,         SEX CHAR(1))"""# 使用execute方法執行SQL語句cur.execute(sql)# 關閉資料庫連接conn.close()

　　† 執行以上指令碼的輸出結果

mysql> use testdb;Database changedmysql> show tables;+------------------+| Tables_in_testdb |+------------------+| VFORBOX          || testtab          |+------------------+2 rows in set (0.00 sec)mysql> describe VFORBOX;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| NAME  | char(20) | NO   |     | NULL    |       || AGE   | int(11)  | YES  |     | NULL    |       || SEX   | char(1)  | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+3 rows in set (0.00 sec)

• 執行 SQL INSERT 語句向表 VFORBOX 插入記錄

#!/usr/bin/python# -*- coding: UTF-8 -*-#匯入MySQLdb模組import MySQLdb#開啟資料庫連接#host 主機地址、user 使用者、passwd 密碼 、db 資料庫名稱、port 資料庫連接埠conn=MySQLdb.connect(host=‘192.168.1.187‘,user=‘root‘,passwd=‘test123‘,db=‘testdb‘,port=3306)# 使用cursor()方法擷取操作遊標cur=conn.cursor()# SQL 插入語句sql = """INSERT INTO VFORBOX(         NAME, AGE, SEX)         VALUES (‘Vforbox‘, 18, ‘M‘)"""try:# 執行sql語句   cur.execute(sql)# 提交到資料庫執行   conn.commit()except:# 發生錯誤時復原   conn.rollback()# 關閉資料庫連接conn.close()

　　† 執行以上指令碼的輸出結果

mysql> select * from VFORBOX;+---------+------+------+| NAME    | AGE  | SEX  |+---------+------+------+| Vforbox |   18 | M    |+---------+------+------+1 row in set (0.00 sec)

• 使用變數向SQL語句中傳遞參數(從以上稍做修改)

#!/usr/bin/python# -*- coding: UTF-8 -*-#匯入MySQLdb模組import MySQLdb#開啟資料庫連接#host 主機地址、user 使用者、passwd 密碼 、db 資料庫名稱、port 資料庫連接埠conn=MySQLdb.connect(host=‘192.168.1.187‘,user=‘root‘,passwd=‘test123‘,db=‘testdb‘,port=3306)# 使用cursor()方法擷取操作遊標cur=conn.cursor()# SQL 插入語句sql = "INSERT INTO VFORBOX(        NAME, AGE, SEX)        VALUES (‘%s‘, ‘%d‘, ‘%c‘)" %        (‘vforbox‘, 18, ‘M‘)try:# 執行sql語句   cur.execute(sql)# 提交到資料庫執行   conn.commit()except:# 發生錯誤時復原   conn.rollback()# 關閉資料庫連接conn.close()

　　† 執行以上指令碼的輸出結果

mysql> select * from VFORBOX;+---------+------+------+| NAME    | AGE  | SEX  |+---------+------+------+| Vforbox |   18 | M    || Vforbox |   18 | M    |+---------+------+------+1 row in set (0.00 sec)

回到頂部執行個體操作[查詢、更新]

• Python查詢Mysql使用 fetchone() 方法擷取單條資料, 使用fetchall() 方法擷取多條資料

1. fetchone():  方法擷取下一個查詢結果集,結果集是一個對象
2. fetchall(): 接收全部的返回結果行
3. rowcount:  這是一個唯讀屬性，並返回執行execute()方法後影響的行數

• 查詢VFORBOX表中name（姓名）欄位等於 vforbox 的所有資料

#!/usr/bin/python# -*- coding: UTF-8 -*-#匯入MySQLdb模組import MySQLdb#開啟資料庫連接#host 主機地址、user 使用者、passwd 密碼 、db 資料庫名稱、port 資料庫連接埠conn=MySQLdb.connect(host=‘192.168.1.187‘,user=‘root‘,passwd=‘test123‘,db=‘testdb‘,port=3306)# 使用cursor()方法擷取操作遊標cur=conn.cursor()#SQL查詢語句sql="SELECT * FROM VFORBOX WHERE NAME =‘vforbox‘"try:    #執行SQL語句    cur.execute(sql)#擷取所有記錄列表    res = cur.fetchall()    for s in res:      name=s[0]      age=s[1]      sex=s[2]#列印結果輸出    print "name=%s,age=%d,sex=%s" % (name,age,sex)except:    print u"錯誤：無法行程資料"# 關閉資料庫連接conn.close()

　　† 執行以上指令碼的輸出結果

name=vforbox,age=20,sex=M

• 更新操作用於更新資料表的的資料，將 VFORBOX表中的 SEX 欄位全部修改為 ‘M‘，AGE 欄位遞增1

#!/usr/bin/python# -*- coding: UTF-8 -*-#匯入MySQLdb模組import MySQLdb#開啟資料庫連接#host 主機地址、user 使用者、passwd 密碼 、db 資料庫名稱、port 資料庫連接埠conn=MySQLdb.connect(host=‘192.168.1.187‘,user=‘root‘,passwd=‘test123‘,db=‘testdb‘,port=3306)# 使用cursor()方法擷取操作遊標cur=conn.cursor()#SQL更新語句sql = "UPDATE VFORBOX SET AGE = AGE + 1 WHERE SEX = ‘M‘"try:    #執行SQL語句    cur.execute(sql)    #提交到資料庫執行    conn=commit()except:    conn.rollback()# 關閉資料庫連接conn.close()

　　† 執行以上指令碼的輸出結果

mysql> select * from VFORBOX;+---------+------+------+| NAME    | AGE  | SEX  |+---------+------+------+| vforbox |   21 | M    |+---------+------+------+1 rows in set (0.00 sec)

回到頂部DB-API錯誤處理

異常	描述
Warning	當有嚴重警告時觸發，例如插入資料是被截斷等等,必須是 StandardError 的子類
Error	警告以外所有其他錯誤類,必須是 StandardError 的子類
InterfaceError	當有資料庫介面模組本身的錯誤（而不是資料庫的錯誤）發生時觸發,必須是Error的子類
DatabaseError	和資料庫有關的錯誤發生時觸發, 必須是Error的子類
DataError	當有資料處理時的錯誤發生時觸發，例如：除零錯誤，資料超範圍等等,必須是DatabaseError的子類
OperationalError	指非使用者控制的，而是操作資料庫時發生的錯誤 例如：串連意外斷開、資料庫名未找到、交易處理失敗、記憶體配置錯誤等等操作資料庫是發生的錯誤, 必須是DatabaseError的子類
IntegrityError	完整性相關的錯誤，例如外鍵檢查失敗等,必須是DatabaseError子類
InternalError	資料庫的內部錯誤，例如遊標（cursor）失效了、事務同步失敗等等. 必須是DatabaseError子類
ProgrammingError	程式錯誤，例如資料表（table）沒找到或已存在、SQL語句語法錯誤、 參數數量錯誤等等,必須是DatabaseError的子類
NotSupportedError	不支援錯誤，指使用了資料庫不支援的函數或API等 例如在連線物件上 使用.rollback()函數，然而資料庫並不支援事務或者事務已關閉,必須是DatabaseError的子類

Python 操作MySQL資料庫