您好,登錄后才能下訂單哦!
今天在慕課網(wǎng)上學(xué)習(xí)了有關(guān)于python操作MySQL的相關(guān)知識(shí),在此做些總結(jié)。python操作數(shù)據(jù)庫(kù)還是相對(duì)比較簡(jiǎn)單的,由于python統(tǒng)一了各個(gè)數(shù)據(jù)庫(kù)的接口程序,也就是所謂的Python DB,所以無(wú)論使用何種數(shù)據(jù)可,都可以用統(tǒng)一的接口對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作。操作中主要涉及connection對(duì)象的操作和cursor的操作,前者主要是為了建立起python與數(shù)據(jù)庫(kù)的數(shù)據(jù)交換通道,后者則是訪問(wèn)數(shù)據(jù)的游標(biāo),也可以理解為指針。數(shù)據(jù)庫(kù)的相關(guān)結(jié)構(gòu)化語(yǔ)言在Python中均是以字符串的形式呈現(xiàn)的。另外注意rollback的重要性,一旦操作失敗,所有操作都要回滾到之前的狀態(tài),否則會(huì)發(fā)生錯(cuò)誤。
另外,在編寫(xiě)實(shí)例的時(shí)候,對(duì)于面向?qū)ο蟮木幊趟悸酚钟辛诵碌恼J(rèn)識(shí),自頂向下的程序編寫(xiě)模式非常有利于拆分程序的功能,分而治之。面向?qū)ο蟮姆庋b性在此提醒的淋漓盡致!
代碼如下,在原有基礎(chǔ)上,我又增加了添加記錄的功能。
#coding=utf8 import MySQLdb import sys class TranseferMonet(object): def __init__(self,conn): self.conn = conn def createNewUser(self,userID,money): cursor = self.conn.cursor() try: sql = 'INSERT account VALUES(%s,%s)' %(str(userID),str(money)) cursor.execute(sql) self.conn.commit() except Exception as e: self.conn.rollback() raise e def transferMoney(self,transeferID,recivierID,money): try: self.checkID(transeferID) self.checkID(receiverID) self.checkEnoughMoney(transferID,money) self.subMoney(transferID,money) self.addMoney(receiverID,money) self.conn.commit() except Exception as e: self.conn.rollback() raise e def checkID(self,userID): cursor = self.conn.cursor() try: sql = 'SELECT userID FROM account WHERE userID = %s' %str(userID) cursor.execute(sql) rs = cursor.fetchall() if len(rs) != 1: raise Exception("ID錯(cuò)誤!") finally: cursor.close() def checkEnoughMoney(self,transferID,money): cursor = self.conn.cursor() try: sql = 'SELECT money FROM account WHERE userID = %s and money >= %s' %(str(transferID),str(money)) cursor.execute(sql) rs = cursor.fetchall() if len(rs) != 1: raise Exception("余額不足!") finally: cursor.close() def subMoney(self,transferID,money): cursor = self.conn.cursor() try: sql = 'UPDATE account SET money = money-%s WHERE userID = %s' %(str(money),str(transferID)) cursor.execute(sql) if cursor.rowcount != 1: raise Exception('減款失??!') finally: cursor.close() def addMoney(self,receiverID,money): cursor = self.conn.cursor() try: sql = 'UPDATE account SET money = money+%s WHERE userID = %s' %(str(money),str(receiverID)) cursor.execute(sql) if cursor.rowcount != 1: raise Exception('加款失敗!') finally: cursor.close() if __name__=="__main__": transferID = 2002 receiverID = 2001 money = 300 newID = 2003 newmoney = 900 conn = MySQLdb.connect(host = '127.0.0.1',port = 3306,user = 'root',passwd = '914767195',db = 'test',charset = 'utf8') trMoney = TranseferMonet(conn) try: trMoney.transferMoney(transferID,receiverID,money) except Exception as e: print "轉(zhuǎn)賬錯(cuò)誤"+str(e) try: trMoney.createNewUser(newID,newmoney) except Exception as e: print "創(chuàng)建用戶(hù)失敗!"+str(e) finally: conn.close()
以上就是本文的全部?jī)?nèi)容,希望對(duì)大家的學(xué)習(xí)有所幫助,也希望大家多多支持億速云。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。