python mysql資料庫程式設計

來自個人部落格：http://ningning.today/2014/12/19/python/python-mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BC%96%E7%A8%8B/

安裝mysql和MySQLdb模組

安裝mysql資料庫:

sudo apt-get install mysql-server

安裝python MySQLdb模組:

sudo apt-get install python-mysqldb

---

配置mysql

首先我們建立一個新的資料庫使用者和資料庫。

$ mysql -u root -p

mysql> SHOW DATABASES;

顯示資料庫資訊:

+——————————+

| Database |

+——————————+

| information_schema |

| mysql |

+——————————+

建立一個資料庫testdb用來實驗:

mysql> CREATE DATABASE testdb

Query OK, 1 row affected (0.02 sec)

mysql> CREATE USER 'estuser'@'localhost' IDENTIFIED BY 'test623'

使用testdb資料庫:

mysql> USE testdb;

更改許可權:

mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';

退出:

mysql> quit;

---

MySQLdb模組操作

0. 獲取資料庫版本(version.py)

#!/usr/bin/python
#coding=utf-8

import MySQLdb as mdb
import sys

try:

    #連線資料庫函式connect(host, username, password, dbname)
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

    #cursor物件遍歷記錄
    cur = con.cursor()

    #execute()函式執行sql語句
    cur.execute("SELECT VERSION()")

    #fetchone()獲取一個記錄
    ver = cur.fetchone()

    print "Database version : %s " % ver

except mdb.Error, e:
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

finally:    
    if con:    
        con.close()

執行

python version.py

或者

chmod a+x version.py

./version

可以看到輸出mysql版本號:

MySQL version: 5.5.40-0ubuntu0.14.04.1

1. 建立和更新資料庫表(create_add.py)

#!/usr/bin/python
#coding=utf-8

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

 #python直譯器碰到with關鍵字會自動釋放資源和錯誤處理

with con:

    cur = con.cursor()

    cur.execute("DROP TABLE IF EXISTS Writers")

    #建立作者表Writers,注意表名大小寫敏感

    cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \

                 Name VARCHAR(25))")

    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")

    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")

    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")

    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")

    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

執行python create_add.py然後進入mysql查詢SELECT * FROM Writers;

+——+—————————-+

| Id | Name |

+——+—————————-+

| 1 | Jack London |

| 2 | Honore de Balzac |

| 3 | Lion Feuchtwanger |

| 4 | Emile Zola |

| 5 | Truman Capote |

+——+—————————-+

5 rows in set (0.00 sec)

2. 檢索資料(retrieve.py)

#!/usr/bin/python
#coding=utf-8

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

with con: 

    cur = con.cursor()

    cur.execute("SELECT * FROM Writers")

    #fetchall()得到所有記錄，返回一個二維tuple，每個inner tuples代表資料庫表的一行
    rows = cur.fetchall()

    for row in rows:
        print row
```

執行`python retrieve.py`:

(1L, 'Jack London')
(2L, 'Honore de Balzac')
(3L, 'Lion Feuchtwanger')
(4L, 'Emile Zola')
(5L, 'Truman Capote')

3. 字典遊標(dictcur.py)

之前cursor返回的是一個tuple的tuple，也可以使用列名作為字典的下標獲得資料

#!/usr/bin/python
#coding=utf-8

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute("SELECT * FROM Writers LIMIT 4")
    rows = cur.fetchall()
    for row in rows:
        print row["Id"], row["Name"]

執行下看看:

python ./dictcur.py

1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola

4. 獲取列資訊Column headerscolumnheaders.py)

#!/usr/bin/python
#coding=utf-8

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:
    cur = con.cursor()
    cur.execute("SELECT * FROM Writers LIMIT 5")
    rows = cur.fetchall()

    #cursor物件的description返回一個查詢的列資訊
    desc = cur.description

    print "%s %3s" % (desc[0][0], desc[1][0])

    for row in rows:    
        print "%2s %3s" % row

python columnheaders.py

Id Name
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

5. 預處理語句Prepared statements(prepared.py)

使用佔位符而不是直接輸出值

#!/usr/bin/python
#coding=utf-8

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:    
    cur = con.cursor()
    cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Guy de Maupasant", "4"))        
    print "Number of rows updated:",  cur.rowcount

python ./prepared.py

Number of rows updated: 1

We have updated one row.

去資料庫檢視:

mysql> SELECT Name FROM Writers WHERE Id=4;

+—————————+
| Name |
+—————————+
| Guy de Maupasant |
+—————————+

1 row in set (0.00 sec)

6. 事務支援Transaction support

一個事務Transaction是指在一個或多個資料庫間的原子操作，在一個Transaction中的資料庫語句可以全部提交(commit())或者回滾(rollback())。

注意老版本的mysql使用的儲存引擎是MyISAM(不支援Transaction)，從MySQL5.5以後使用InnoDB作為預設儲存引擎，在安全性和速度上做了權衡。

#!/usr/bin/python
#coding=utf-8

import MySQLdb as mdb
import sys

try:
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
    # A transaction is started when the cursor is created.
    cur = con.cursor()

    cur.execute("DROP TABLE IF EXISTS Writers")
    cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                 Name VARCHAR(25)) ENGINE=INNODB")
    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")

    #We must end a transaction with either a commit() or a rollback() method

    #If we comment this line, the table is created but the data is not written to the table.
    con.commit()

except mdb.Error, e:
    if con:
        con.rollback()

    print "Error %d: %s" % (e.args[0],e.args[1])

    sys.exit(1)

finally:    
    if con:    
        con.close()

實際上之前的程式碼示例已經通過with語句使用了Transaction而沒有顯示宣告它。with語句適用於對資源進行訪問的場合，確保不管使用過程中是否發生異常都會執行必要的“清理”操作，釋放資源，比如檔案使用後自動關閉、執行緒中鎖的自動獲取和釋放等。在這裡with自動commits()或者rollback()，很方便。

---

Reference

MySQL Python tutorial
淺談 Python 的 with 語句