python操作Mysql資料庫

先下載MySQLdb：MySQL for Python

學習資源在慕課網：Python操作MySQL資料庫_python操作資料庫對象_python增刪改查資料庫-慕課網

主要的思路是：利用mysqldb模塊，連接上myslq資料庫，再獲取游標，執行數據操作（select ,update,delete），處理獲取數據（放入文件，列印等），關閉游標和連接，釋放資源。

以select為例，完整的一次操作大概如下：

import MySQLdb#獲取mysql的連接conn = MySQLdb.Connect(host = 127.0.0.1,port = 3306,user = root,passwd = 123456,db = imooc,charset = utf8)# 這是我本機的mysql資料庫，可根據自己的情況調整參數#獲取游標cursor = conn.cursor()sql = "select * from users"try: cursor.execute(sql) rs = cursor.fetchall() for row in rs: print userid=%s ,username=%s % rowexcept Exception as e: print Error: + ecursor.close()conn.close()# 關閉連接和游標，釋放資源

fetchone() 取當前行數據；fetchmany(N) 取當前行開始，共計N行數據；fetchall() 取當前行開始，餘下的所有數據

資料庫中事務的概念：

練習：銀行轉賬

思路：

# -*- coding: UTF-8 -*-import sysimport MySQLdbclass TransferMoney(object): def __init__(self, conn): self.conn = conn def check_acct_available(self,acctid): cursor = self.conn.cursor() try: sql = "select * from account where acctid=%s"%acctid cursor.execute(sql) print "check_acct_available:" + sql rs = cursor.fetchall() if len(rs) !=1: raise Exception("賬號%s" % acctid) finally: cursor.close def has_enough_money(self,acctid,money): cursor = self.conn.cursor() try: sql = "select * from account where acctid=%s and money>=%s"%(acctid,money) cursor.execute(sql) print "check_acct_available:" + sql rs = cursor.fetchall() if len(rs) !=1: raise Exception("賬號%s餘額不足" % acctid) finally: cursor.close def reduce_money(self,acctid,money): cursor = self.conn.cursor() try: sql = sql = "update account set money = money - %s where acctid=%s"%(money,acctid) cursor.execute(sql) print "check_acct_available:" + sql if cursor.rowcount !=1: raise Exception("賬號%s減款失敗" % acctid) finally: cursor.close def add_money(self,acctid,money): cursor = self.conn.cursor() try: sql = sql = "update account set money = money + %s where acctid=%s"%(money,acctid) cursor.execute(sql) print "check_acct_available:" + sql if cursor.rowcount !=1: raise Exception("賬號%s加款失敗" % acctid) finally: cursor.close def transfer(self, source_acctid ,target_acctid ,money): try: self.check_acct_available(source_acctid) self.check_acct_available(target_acctid) self.has_enough_money(source_acctid, money) self.reduce_money(source_acctid, money) self.add_money(target_acctid, money) self.conn.commit() except Exception as e: self.conn.rollback() print Error: + str(e) raise eif __name__=="__main__": source_acctid = sys.argv[1] target_acctid = sys.argv[2] money = sys.argv[3] conn = MySQLdb.connect(host="127.0.0.1",port=3306,user=root,passwd=123456,db=imooc,charset=utf8) tr_money = TransferMoney(conn) try: tr_money.transfer(source_acctid,target_acctid,money) except Exception as e: print Error: + str(e) finally: conn.close()

推薦閱讀：