python3操作MySQL資料庫

標籤：encoding   .com   奇葩   tran   方便   使用者   cut   exce   date   

這是python3下的MySQL基本操作。其他類型的資料庫用法基本一樣。就是庫的名字不同。因為python官方很早之前就規定了資料庫第三方庫的借口，來避免API混亂的情況。

安裝與準備

這是python3的庫，所以windows下安裝不會像python2那樣各種奇葩VC錯誤。是比較方便的傻瓜安裝。

• Windows平台下: py -3 -m pip install PyMySQL
• Linux：python3 pip install PyMySQL

當然，引入的時候：import pymysql

資料庫連接對象connection

Function	描述
connection	建立connection對象
cursor()	使用該連結建立+返回遊標
commit()	提交當前事務
rollback()	復原當前十五
close()	關閉串連

介紹一下connection的參數

1. host mysql伺服器位址
2. port 數字類型 連接埠
3. user 使用者名稱
4. passwd 密碼
5. db 資料庫名稱
6. charset 串連編碼，需要顯式指明編碼方式

上一段程式碼範例：

conn = pymysql.Connect(host=‘127.0.0.1‘,port=3306,user=‘root‘,passwd=‘dyx240030‘,db=‘imooc‘,charset=‘utf8‘)cursor = conn.cursor()print(conn)print(cursor)cursor.close()conn.close()

OUT:<pymysql.connections.Connection object at 0x00000051C15BFDA0><pymysql.cursors.Cursor object at 0x00000051C15BFD68>

資料庫遊標對象cursor

Function	描述
execute(op[,args])	執行一個資料庫查詢和命令
fetchone()	取得結果集下一行
fetchmany(size)	取得結果集size行
fetchall()	取得結果集剩下所有行
rowcount	最近一次execute返回資料的行數或影響行數
close()	關閉cursor

代碼實現：

conn = pymysql.Connect(host=‘127.0.0.1‘,port=3306,user=‘root‘,passwd=‘dyx240030‘,db=‘imooc‘,charset=‘utf8‘)cursor = conn.cursor()sql = "select * from user"cursor.execute(sql)print("cursor.excute:",cursor.rowcount)rs = cursor.fetchone()print("rs:",rs)for each in cursor.fetchmany(2):    print(each)print()for each in cursor.fetchall():    print(each)

OUT:cursor.excute: 4rs: (‘1‘, ‘name1‘)(‘2‘, ‘name2‘)(‘3‘, ‘name3‘)(‘4‘, ‘name4‘)

更新資料庫insert/update/delete

不同於select操作，這三個操作修改了資料庫內容，所以需要commit()，否則資料庫沒有做相應的更改，但是也不會報錯。

按照一般的思路，一般是以下套路：

1. 關閉自動commit:conn.autocommit(False)
2. 出現：conn.rowback()復原
3. 未出現：conn.commit()

下面這段指令碼，實現insert/update/delete操作。其實這種檢錯模式不對，這裡只做簡單raise，後面有更好的方法。

conn = pymysql.Connect(host=‘127.0.0.1‘,port=3306,user=‘root‘,passwd=‘dyx240030‘,db=‘imooc‘,charset=‘utf8‘)conn.autocommit(False)cursor = conn.cursor()sqlInsert = "insert into user(userid,username) values(‘6‘,‘name6‘)"sqlUpdate = "update user set username=‘name41‘ where userd=‘4‘"sqlDelete = "delete from user where userid=‘1‘"try:    cursor.execute(sqlInsert)    print(cursor.rowcount)    cursor.execute(sqlUpdate)    print(cursor.rowcount)    cursor.execute(sqlDelete)    print(cursor.rowcount)        conn.commit()except Exception as e:    print("Reason:",e)    conn.rollback()    cursor.close()cursor.close()

[OUT]:1Reason: (1054, "Unknown column ‘userd‘ in ‘where clause‘")

執行個體 銀行轉賬

可以看一下類思想的SQL操作，其中之前提到過的進階報錯模式用到了之前看似無用的rowcount函數，通過查看操作對於資料庫的影響來檢錯。

import osimport sysimport pymysqlclass transferMoney(object):    def __init__(self,conn):        self.conn = conn    def transfer(self,sourceID,targetID,money):        #   其他函數中若是有錯會拋出異常而被檢測到。        try:            self.checkIdAvailable(sourceID)            self.checkIdAvailable(targetID)            self.ifEnoughMoney(sourceID,money)            self.reduceMoney(sourceID,money)            self.addMoney(targetID,money)            self.conn.commit()        except Exception as e:            self.conn.rollback()            raise e    def checkIdAvailable(self,ID):        cursor = self.conn.cursor()        try:            sql = "select * from account where id = %d" % ID #select語句判斷可以用len(rs)            cursor.execute(sql)            rs=  cursor.fetchall()            if len(rs) != 1:#   資料庫類思想的報錯模式，檢查操作對資料庫的影響條目。沒有達到目標，拋出異常                raise Exception("帳號 %d 不存在"%ID)        finally:            cursor.close()            def ifEnoughMoney(self,ID,money):        cursor = self.conn.cursor()        try:            sql = "select * from account where id = %d and money>=%d" % (ID,money)            cursor.execute(sql)            rs=  cursor.fetchall()            if len(rs) != 1:                raise Exception("帳號 %d 不存在 %d Yuan"%(ID,money))        finally:            cursor.close()        def reduceMoney(self,ID,money):        cursor = self.conn.cursor()        try:            sql = "update account set money = money-%d where id=%d"%(money,ID)            cursor.execute(sql)            if cursor.rowcount != 1:                raise Exception("失敗減錢")        finally:            cursor.close()        def addMoney(self,ID,money):        cursor = self.conn.cursor()        try:            sql = "update account set money = money+%d where id=%d"%(money,ID)            cursor.execute(sql)            if cursor.rowcount != 1:                raise Exception("失敗加款")        finally:            cursor.close()    if __name__=="__main__":    if len(sys.argv)>=2:        sourceID = int(sys.argv[1])        targetID = int(sys.argv[2])        money = int(sys.argv[3])        conn = pymysql.Connect(host=‘127.0.0.1‘,port=3306,user=‘root‘,passwd=‘dyx240030‘,db=‘imooc‘,charset=‘utf8‘)        trMoney = transferMoney(conn)        try:            trMoney.transfer(sourceID,targetID,money)        except  Exception as e:            print("出現問題"+str(e))        finally:            conn.close()

我踩過的坑。。。(這裡是Python3哦)‘NoneType‘ object has no attribute ‘encoding‘ ，之前指明的charset必須是"UTF8",不是"utf-8"/"UTF-8"MySQL語句後面必須有‘;‘，否則不會報錯，也難以探索資料庫insert/update/delete操作需要commit()在構造命令的時候，注意用 " 包裹起來，因為SQL語句字串需要 ‘ 包裹。所以，" 比較簡單的避免歧義。

python3操作MySQL資料庫