Python操作Mysql執行個體代碼教程線上版(查詢手冊)

執行個體1、取得MYSQL的版本

在windows環境下安裝mysql模組用於python開發

MySQL-python Windows下EXE安裝檔案下載

複製代碼 代碼如下:# -*- coding: UTF-8 -*-

#安裝MYSQL DB for python
import MySQLdb as mdb

con = None

try:
#串連mysql的方法：connect('ip','user','password','dbname')
con = mdb.connect('localhost', 'root',
'root', 'test');

#所有的查詢，都在串連con的一個模組cursor上面啟動並執行
cur = con.cursor()

#執行一個查詢
cur.execute("SELECT VERSION()")

#取得上個查詢的結果，是單個結果
data = cur.fetchone()
print "Database version : %s " % data
finally:
if con:
#無論如何，串連記得關閉
con.close()

執行結果：複製代碼 代碼如下:Database version : 5.5.25

執行個體2、建立一個表並且插入資料

主要還是在cursor上面執行execute方法來進行，請見源碼：

複製代碼 代碼如下:# -*- coding: UTF-8 -*-
# 來源於總結整理
import MySQLdb as mdb
import sys

#將con設定為全域串連
con = mdb.connect('localhost', 'root', 'root', 'test');

with con:

#擷取串連的cursor，只有擷取了cursor，我們才能進行各種操作
cur = con.cursor()
#建立一個資料表 writers(id,name)
cur.execute("CREATE TABLE IF NOT EXISTS \
Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
#以下插入了5條資料
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')")

運行結果（在phpmyadmin中查看，在mysql命令列查看結果是一樣的）：

執行個體3、python使用slect擷取mysql的資料並遍曆

這個恐怕是用的最多的了，請速看代碼：

複製代碼 代碼如下:# -*- coding: UTF-8 -*-
# 來源：整理總結
import MySQLdb as mdb
import sys

#串連mysql，擷取串連的對象
con = mdb.connect('localhost', 'root', 'root', 'test');

with con:
#仍然是，第一步要擷取串連的cursor對象，用於執行查詢
cur = con.cursor()
#類似於其他語言的query函數，execute是python中的執行查詢函數
cur.execute("SELECT * FROM Writers")

#使用fetchall函數，將結果集（多維元組）存入rows裡面
rows = cur.fetchall()

#依次遍曆結果集，發現每個元素，就是表中的一條記錄，用一個元組來顯示
for row in rows:
print row

運行結果：複製代碼 代碼如下:(1L, ‘Jack London')
(2L, ‘Honore de Balzac')
(3L, ‘Lion Feuchtwanger')
(4L, ‘Emile Zola')
(5L, ‘Truman Capote')

上面的代碼，用來將所有的結果取出，不過列印的時候是每行一個元祖列印，現在我們使用方法，取出其中的單個資料：

複製代碼 代碼如下:# -*- coding: UTF-8 -*-
# 來源：瘋狂的螞蟻的部落格總結整理

import MySQLdb as mdb
import sys

#擷取mysql的連結化物件
con = mdb.connect('localhost', 'root', 'root', 'test');

with con:
#擷取執行查詢的對象
cur = con.cursor()

#執行那個查詢，這裡用的是select語句
cur.execute("SELECT * FROM Writers")

#使用cur.rowcount擷取結果集的條數
numrows = int(cur.rowcount)

#迴圈numrows次，每次取出一行資料
for i in range(numrows):
#每次取出一行，放到row中，這是一個元組(id,name)
row = cur.fetchone()
#直接輸出兩個元素
print row[0], row[1]

運行結果：複製代碼 代碼如下:1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

numrows = int(cur.rowcount)用於擷取結果集的數目
row = cur.fetchone()每次取出一行資料，同時記錄集的指標執行下一行

執行個體4、使用字典cursor取得結果集（可以使用表欄位名字訪問值）

複製代碼 代碼如下:# -*- coding: UTF-8 -*-
# 來源：瘋狂的螞蟻的部落格總結整理

import MySQLdb as mdb
import sys

#獲得mysql查詢的連結化物件
con = mdb.connect('localhost', 'root', 'root', 'test')

with con:
#擷取串連上的字典cursor，注意擷取的方法，
#每一個cursor其實都是cursor的子類
cur = con.cursor(mdb.cursors.DictCursor)

#執行語句不變
cur.execute("SELECT * FROM Writers")

#擷取資料方法不變
rows = cur.fetchall()

#遍曆資料也不變（比上一個更直接一點）
for row in rows:
#這裡，可以使用索引值對的方法，由鍵名字來擷取資料
print "%s %s" % (row["Id"], row["Name"])

執行個體5、擷取單個表的欄位名和資訊的方法

複製代碼 代碼如下:# -*- coding: UTF-8 -*-
# 來源：瘋狂的螞蟻的部落格總結整理

import MySQLdb as mdb
import sys

#擷取資料庫的連結化物件
con = mdb.connect('localhost', 'root', 'root', 'test')

with con:
#擷取普通的查詢cursor
cur = con.cursor()
cur.execute("SELECT * FROM Writers")

rows = cur.fetchall()

#擷取連線物件的描述資訊
desc = cur.description
print 'cur.description:',desc

#列印表頭，就是欄位名字
print "%s %3s" % (desc[0][0], desc[1][0])

for row in rows:
#列印結果
print "%2s %3s" % row

運行結果：複製代碼 代碼如下:cur.description: ((‘Id', 3, 1, 11, 11, 0, 0), (‘Name', 253, 17, 25, 25, 0, 1))
Id Name
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

執行個體6、使用Prepared statements執行查詢（更安全方便）

複製代碼 代碼如下:# -*- coding: UTF-8 -*-
# 來源：瘋狂的螞蟻的部落格總結整理
import MySQLdb as mdb
import sys

con = mdb.connect('localhost', 'root', 'root', 'test')

with con:

cur = con.cursor()
#我們看到，這裡可以通過寫一個可以組裝的sql語句來進行
cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Guy de Maupasant", "4"))
#使用cur.rowcount擷取影響了多少行
print "Number of rows updated: %d" % cur.rowcount

結果：

複製代碼 代碼如下:Number of rows updated: 1

執行個體7、把圖片用二進位存入MYSQL
有人喜歡把圖片存入MYSQL（這種做法貌似很少吧），我看大部分的程式，圖片都是存放在伺服器上的檔案，資料庫中存的只是圖片的地址而已，不過MYSQL是支援把圖片存入資料庫的，也相應的有一個專門的欄位BLOB (Binary Large Object)，即較大的二進位對象欄位，請看如下程式，注意測試圖片自己隨便找一個，地址要正確：

首先，在資料庫中建立一個表，用於存放圖片：

複製代碼 代碼如下:CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);

然後運行如下PYTHON代碼進行：

複製代碼 代碼如下:# -*- coding: UTF-8 -*-
# 來源：瘋狂的螞蟻的部落格總結整理
import MySQLdb as mdb
import sys

try:
#用讀檔案模式開啟圖片
fin = open("../web.jpg")
#將文本讀入img對象中
img = fin.read()
#關閉檔案
fin.close()

except IOError, e:
#如果出錯，列印錯誤資訊
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)

try:
#連結mysql，擷取對象
conn = mdb.connect(host='localhost',user='root',passwd='root', db='test')
#擷取執行cursor
cursor = conn.cursor()
#直接將資料作為字串，插入資料庫
cursor.execute("INSERT INTO Images SET Data='%s'" % mdb.escape_string(img))

#提交資料
conn.commit()

#提交之後，再關閉cursor和連結
cursor.close()
conn.close()

except mdb.Error, e:
#若出現異常，列印資訊
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)

結果：

escape_string函數將插入資料庫的字串進行轉義，這會指一些SQL注入的攻擊

執行個體8、從資料庫中把圖片讀出來

複製代碼 代碼如下:# -*- coding: UTF-8 -*-
# 來源：瘋狂的螞蟻的部落格總結整理
import MySQLdb as mdb
import sys

try:
#串連mysql，擷取串連的對象
conn = mdb.connect('localhost', 'root', 'root', 'test');

cursor = conn.cursor()

#執行查詢該圖片欄位的SQL
cursor.execute("SELECT Data FROM Images LIMIT 1")

#使用二進位寫檔案的方法，開啟一個圖片檔案，若不存在則自動建立
fout = open('image.png','wb')
#直接將資料如檔案
fout.write(cursor.fetchone()[0])
#關閉寫入的檔案
fout.close()

#釋放查詢資料的資源
cursor.close()
conn.close()

except IOError, e:
#捕獲IO的異常 ，主要是檔案寫入會發生錯誤
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)

實例9、使用Transaction即事務（手動提交，自動復原）

複製代碼 代碼如下:# -*- coding: UTF-8 -*-
# 來源：瘋狂的螞蟻的部落格總結整理
import MySQLdb as mdb
import sys

try:
#串連mysql，擷取串連的對象
conn = mdb.connect('localhost', 'root', 'root', 'test');

cursor = conn.cursor()
#如果某個資料庫支援事務，會自動開啟
#這裡用的是MYSQL，所以會自動開啟事務（若是MYISM引擎則不會）
cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Leo Tolstoy", "1"))
cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Boris Pasternak", "2"))
cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s",
("Leonid Leonov", "3"))

#事務的特性1、原子性的手動提交
conn.commit()

cursor.close()
conn.close()

except mdb.Error, e:
#如果出現了錯誤，那麼可以復原，就是上面的三條語句要麼執行，要麼都不執行
conn.rollback()
print "Error %d: %s" % (e.args[0],e.args[1])

結果：

1、因為不存在writer表（SQL第三條語句），所以出現錯誤：
Error 1146: Table ‘test.writer' doesn't exist
2、出現錯誤，出發異常處理，3條語句的前兩條會自動變成了沒有執行，結果不變
3、如果本代碼放到一個MyISAM引擎表，前兩句會執行，第三句不會；如果是INNDB引擎，則都不會執行。

為方便大家本地瀏覽特提供一個pdf版本下載

全文完，本文翻譯自：http://zetcode.com/databases/mysqlpythontutorial/