Python實戰之MySQL資料庫操作

本文地址：http://blog.csdn.net/kongxx/article/details/7094018

1. 要想使Python可以操作MySQL資料庫，首先需要安裝MySQL-python包，在CentOS上可以使用一下命令來安裝

$ sudo yum install MySQL-python2. 啥都不說了，走兩步吧，下面的程式建立了一個到mysql資料庫的串連，然後執行了一個簡單的查詢，並列印查詢結果

import MySQLdb</p><p>conn = MySQLdb.connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", db = "fkong")<br />cursor = conn.cursor ()<br />cursor.execute ("SELECT VERSION()")<br />row = cursor.fetchone ()<br />print "MySQL server version:", row[0]<br />cursor.close ()<br />conn.close ()3. 下面看一個資料庫建表和插入操作
import MySQLdb</p><p>conn = MySQLdb.connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", db = "fkong")<br />cursor = conn.cursor ()</p><p>cursor.execute ("""<br />CREATE TABLE TEST<br />(<br />ID INT,<br />COL1 VARCHAR(40),<br />COL2 VARCHAR(40),<br />COL3 VARCHAR(40)<br />)<br />""")</p><p>cursor.execute ("""<br />INSERT INTO TEST (ID, COL1, COL2, COL3)<br />VALUES<br />(1, 'a', 'b', 'c'),<br />(2, 'aa', 'bb', 'cc'),<br />(3, 'aaa', 'bbb', 'ccc')<br />""")</p><p>conn.commit()<br />cursor.close ()<br />conn.close ()4. 下面再來看看查詢，查詢通常有兩種方式：一種是使用cursor.fetchall（）擷取所有查詢結果，然後再一行一行的迭代；另一種每次通過cursor.fetchone()擷取一條記錄，直到擷取的結果為空白為止。看一下下面的例子：

import MySQLdb</p><p>conn = MySQLdb.connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", db = "fkong")<br />cursor = conn.cursor ()</p><p>cursor.execute ("SELECT * FROM TEST")<br />rows = cursor.fetchall()<br />for row in rows:<br />print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])</p><p>print "Number of rows returned: %d" % cursor.rowcount</p><p>cursor.execute ("SELECT * FROM TEST")<br />while (True):<br />row = cursor.fetchone()<br />if row == None:<br />break<br />print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])</p><p>print "Number of rows returned: %d" % cursor.rowcount</p><p>cursor.close ()<br />conn.close ()