How-to use MySQL-python in Python

標籤：mysql   python   

對於資料庫操作，和 TCP/IP 的三向交握異曲同工之妙，建立串連，執行操作，中斷連線。當然這就需要建立串連的工具

Python串連mysql的方案有oursql、PyMySQL、 myconnpy、MySQL Connector 等，不過本篇說的確是另外一個類庫MySQLdb，MySQLdb 是用於Python連結Mysql資料庫的介面，它實現了 Python 資料庫 API 規範 V2.0，基於 MySQL C API 上建立的。

Reference Package MySQL-python MySQLdb User’s Guide

安裝

yum install Mysql-python -ypip install Mysql-python 源碼解壓縮進入主目錄執行 python setup.py install

使用

1. 資料庫的串連

MySQLdb提供了connect方法用來和資料庫建立串連,接收數個參數,返回連線物件：
conn=MySQLdb.connect(host=”hostname”,user=”username”,passwd=”password”,db=”dbname”,charset=”utf8″)

比較常用的參數包括:
host:資料庫主機名稱.預設是用本地主機
user:資料庫登陸名.預設是目前使用者
passwd:資料庫登陸的秘密.預設為空白
db:要使用的資料庫名.沒有預設值
port:MySQL服務使用的TCP連接埠.預設是3306
charset:資料庫編碼
然後,這個連線物件也提供了對事務操作的支援,標準的方法:
commit() 提交
rollback() 復原

#!/usr/bin/env python# -*- coding=utf-8 -*-import MySQLdb try:    conn=MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘‘,port=3306)        # 使用cursor()方法擷取操作遊標    cur=conn.cursor()    # 選擇資料庫    conn.select_db(‘test‘)    # 使用execute方法執行SQL語句    cur.execute("SELECT VERSION()")    # 使用 fetchone() 方法擷取一條資料庫。    data = cur.fetchone()    print "Database version : %s " % data        # 關閉串連    conn.commit()    cur.close()    conn.close() except MySQLdb.Error,e:     print "Mysql Error %d: %s" % (e.args[0], e.args[1])

執行結果

Database version : 5.1.73

2. cursor方法執行與傳回值

cursor方法提供兩類操作：
1.執行命令
2.接收傳回值
cursor用來執行命令的方法

#用來執行預存程序,接收的參數為預存程序名和參數列表,傳回值為受影響的行數
callproc(self, procname, args)
#執行單條sql語句,接收的參數為sql語句本身和使用的參數列表,傳回值為受影響的行數
execute(self, query, args)
#執行單挑sql語句,但是重複執行參數列表裡的參數,傳回值為受影響的行數
executemany(self, query, args)
#移動到下一個結果集
nextset(self)
cursor用來接收傳回值的方法
#接收全部的返回結果行.
fetchall(self)
#接收size條返回結果行.如果size的值大於返回的結果行的數量,則會返回cursor.arraysize條資料
fetchmany(self, size=None)
#返回一條結果行
fetchone(self)
#移動指標到某一行.如果mode=’relative’,則表示從當前所在行移動value條,如果mode=’absolute’,則表示從結果集的第一行移動value條
scroll(self, value, mode=’relative’)
#這是一個唯讀屬性，並返回執行execute()方法後影響的行數
rowcount

3.  資料庫操作

a.建立表

#!/usr/bin/env python# -*- coding=utf-8 -*-import MySQLdb try:    conn=MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘‘,port=3306)        # 使用cursor()方法擷取操作遊標    cur=conn.cursor()    # 選擇資料庫    conn.select_db(‘test‘)    # 如果資料表已經存在使用 execute() 方法刪除表。    cur.execute("DROP TABLE IF EXISTS stu_info")    # 建立資料表SQL語句    sql = """CREATE TABLE stu_info (             `id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,             `name` CHAR(20) NOT NULL,             `age` INT,             `sex` CHAR(6))"""    cur.execute(sql)    conn.commit()    cur.close()    conn.close() except MySQLdb.Error,e:     print "Mysql Error %d: %s" % (e.args[0], e.args[1])

b. 插入資料

 添加單行記錄

#!/usr/bin/env python# -*- coding=utf-8 -*-import MySQLdb # 建立串連conn=MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘‘,port=3306)# 使用cursor()方法擷取操作遊標cur=conn.cursor()# 選擇資料庫conn.select_db(‘test‘)# 插入一條記錄sql = "insert into stu_info(name,age,sex) values(%s,%s,%s)"cur.execute(sql,(‘Lisa‘,18,‘female‘))conn.commit()cur.close()conn.close()

添加多行記錄

#!/usr/bin/env python# -*- coding=utf-8 -*-import MySQLdb# 建立串連conn = MySQLdb.connect(host=‘localhost‘, user=‘root‘, passwd=‘‘, port=3306)# 使用cursor()方法擷取操作遊標cur = conn.cursor()# 選擇資料庫conn.select_db(‘test‘)# 插入一條記錄sql = "insert into stu_info(name, age, sex) values(%s, %s, %s)"cur.executemany(sql, [    (‘jack‘, 20, ‘male‘),    (‘Danny‘, 19, ‘female‘),    ])conn.commit()cur.close()conn.close()

executemany()方法可以一次插入多條值，執行單挑sql語句,但是重複執行參數列表裡的參數,傳回值為受影響的行數。

c. 查詢資料

#!/usr/bin/env python# -*- coding=utf-8 -*-import MySQLdb# 建立串連conn = MySQLdb.connect(host=‘localhost‘, user=‘root‘, passwd=‘‘, port=3306)# 使用cursor()方法擷取操作遊標cur = conn.cursor()# 選擇資料庫conn.select_db(‘test‘)# 擷取記錄條數rec_count = cur.execute("select * from stu_info")print "There have %s records" % rec_count#列印表中的資料#rows = cur.fetchmany(rec_count)rows = cur.fetchall()for row in rows:    print rowconn.commit()cur.close()conn.close()

執行結果

There have 3 records(1L, ‘Lisa‘, 18L, ‘female‘)(2L, ‘jack‘, 20L, ‘male‘)(3L, ‘Danny‘, 19L, ‘female‘)

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

import MySQLdb # 建立串連conn=MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘‘,port=3306)# 使用cursor()方法擷取操作遊標cur=conn.cursor()# 選擇資料庫conn.select_db(‘test‘)# 執行那個查詢，這裡用的是select語句cur.execute("select * from stu_info")# 使用cur.rowcount擷取結果集的條數numrows = int(cur.rowcount)for i in range(numrows):  row = cur.fetchone()  print str(row[0]) + "," + row[1] + "," + str(row[2]) + "," + row[3]conn.commit()cur.close()conn.close()

執行結果

1,Lisa,18,female2,jack,20,male3,Danny,19,female

 

使用字典cursor取得結果集（可以使用表欄位名字訪問值）

import MySQLdb # 建立串連conn=MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘‘,port=3306)# 使用cursor()方法擷取操作遊標cur=conn.cursor()# 選擇資料庫conn.select_db(‘test‘)# 擷取串連上的字典cursor，注意擷取的方法，# 每一個cursor其實都是cursor的子類cur = conn.cursor(MySQLdb.cursors.DictCursor)# 執行語句不變cur.execute("SELECT * FROM stu_info")# 擷取資料方法不變rows = cur.fetchall()# 遍曆資料也不變（比上一個更直接一點）for row in rows:    # 這裡，可以使用索引值對的方法，由鍵名字來擷取資料    print "%s %s %s" % (str(row["id"]), row["name"], str(row["age"]))conn.commit()cur.close()conn.close()

執行結果：

1 Lisa 182 jack 203 Danny 19

使用Prepared statements執行查詢

import MySQLdb # 建立串連conn=MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘‘,port=3306)# 使用cursor()方法擷取操作遊標cur=conn.cursor()# 選擇資料庫conn.select_db(‘test‘)# 我們看到，這裡可以通過寫一個可以組裝的sql語句來進行cur.execute("UPDATE stu_info SET Name = %s WHERE Id = %s",    ("cherry", "3"))# 使用cur.rowcount擷取影響了多少行print "Number of rows updated: %d" % cur.rowcountconn.commit()cur.close()conn.close()

執行結果

:<EOF>Number of rows updated: 1In [16]:

Transaction – 事務

事務機制可以確保資料一致性。
事務應該具有4個屬性：原子性、一致性、隔離性、持久性。這四個屬性通常稱為ACID特性。
① 原子性（atomicity）。一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。
② 一致性（consistency）。事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。
③ 隔離性（isolation）。一個事務的執行不能被其他事務幹擾。即一個事務內部的操作及使用的資料對並發的其他事務是隔離的，並發執行的各個事務之間不能互相干擾。
④ 持久性（durability）。持久性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中資料的改變就應該是永久性的。接下來的其他動作或故障不應該對其有任何影響。

Python DB API 2.0 的事務提供了兩個方法 commit 或 rollback。
對於支援事務的資料庫， 在Python資料庫編程中，當遊標建立之時，就自動開始了一個隱形的資料庫事務。commit()方法遊標的所有更新操作，rollback（）方法復原當前遊標的所有操作。每一個方法都開始了一個新的事務。

import MySQLdbtry:    # 建立串連    conn = MySQLdb.connect(        host=‘localhost‘, user=‘root‘, passwd=‘‘, port=3306)    # 使用cursor()方法擷取操作遊標    cur = conn.cursor()    # 選擇資料庫    conn.select_db(‘test‘)    # 如果某個資料庫支援事務，會自動開啟    # 這裡用的是MYSQL，所以會自動開啟事務（若是MYISM引擎則不會）    cur.execute("UPDATE stu_info SET name = %s WHERE id = %s",                   ("tonny", "1"))    cur.execute("UPDATE stu_infos SET name = %s WHERE id = %s",                   ("jim", "2"))    # 事務的特性1、原子性的手動提交    conn.commit()    cur.close()    conn.close()except MySQLdb.Error, e:    # 如果出現了錯誤，那麼可以復原，就是上面的三條語句要麼執行，要麼都不執行 [ 儲存引擎支援事物 ]    # 如儲存引擎不只是事務[MyISM], 則只提交成功的結果    conn.rollback()    print "Error %d: %s" % (e.args[0], e.args[1])

執行結果

Error 1146: Table ‘test.stu_infos‘ doesn‘t exist

 

 

http://www.suzf.net/thread-1104-435.html     未完待續  … …. 

本文出自 “Jeffrey Blog” 部落格，請務必保留此出處http://oceanszf.blog.51cto.com/6268931/1710322

How-to use MySQL-python in Python