我在寫(xiě)爬蟲(chóng)的時(shí)候，經(jīng)常會(huì)獲取大量的數(shù)據(jù)。這個(gè)時(shí)候大量數(shù)據(jù)的數(shù)據(jù)庫(kù)操作如果不做一定的優(yōu)化的話，將會(huì)耗費(fèi)大量的時(shí)間。通過(guò)實(shí)踐我發(fā)現(xiàn)，批量的數(shù)據(jù)操作將會(huì)極大的提高數(shù)據(jù)庫(kù)操作的效率。同時(shí)，很多時(shí)候存在數(shù)據(jù)如果存在做更新操作，如果數(shù)據(jù)不存在，做插入插入操作的情況。于是我造了一個(gè)只需傳入一個(gè)數(shù)組，就會(huì)自動(dòng)更新或插入數(shù)據(jù)的工具。
實(shí)測(cè)以下方法操作幾十萬(wàn)條數(shù)據(jù)只需要幾秒到十幾秒
以下為代碼：

#說(shuō)明： #使用時(shí)直接調(diào)用saveAll方法，其他方法將被saveAll調(diào)用，你可以無(wú)視 #saveAll參數(shù)說(shuō)明： # table 表名 # datas 數(shù)據(jù)的數(shù)組 例 ：[{"key1":"value1","key2":"value2"},{"key1":"value1","key2":"value2"}] 建議數(shù)組大小不要超過(guò)一千。 # searchKeys 用于確定唯一行的鍵的數(shù)組，如用戶表的用戶名，選課表的課程ID與學(xué)生ID等 例 ["user_id","class_id"] # ifIgnoreSearchKey 是否忽略searchKey 如果你的searchKeys 是自增長(zhǎng)的ID 你肯定不希望插入的時(shí)候插入這個(gè)字段 "1"是，"0"否 # ifNotUpdate 是否不做更新操作 如果這個(gè)設(shè)為 "0" ,datas中數(shù)據(jù)如果已在數(shù)據(jù)庫(kù)中，將不會(huì)做更新操作 #getConnection 方法中的DB 是我從我的配置文件中導(dǎo)入的，你可以換成你的 def getConnection():conn = MySQLdb.connect(host=DB["host"],user=DB["user"],passwd=DB["passwd"],db=DB["db"],charset=DB["charset"])return conndef saveAll(table,datas,searchKeys,ifIgnoreSearchKey,ifNotUpdate):print datasconn = getConnection()cursor = conn.cursor()where = []#轉(zhuǎn)義數(shù)據(jù),避免sql發(fā)生錯(cuò)誤for data in datas:for key in data:data[key] = MySQLdb.escape_string(str(data[key]))for searchKey in searchKeys:searchKeyDatas = []for data in datas:searchKeyDatas.append(data[searchKey])searchKeyDatasString = "`"+searchKey+"` in ('"+"','".join(searchKeyDatas)+"')"where.append(searchKeyDatasString)whereString = " AND ".join(where)selectSql = "SELECT `"+"`,`".join(searchKeys)+"` from "+table+" WHERE " + whereStringcursor.execute(selectSql)conn.commit()results = cursor.fetchall()updateData = []insertData = []existKey = []for result in results:keyValue = []for value in result:keyValue.append(str(value))existKey.append(",".join(keyValue))for data in datas:keyValue = []for key in searchKeys:keyValue.append(data[key])currentKey = ",".join(keyValue)if currentKey in existKey:updateData.append(data)else:insertData.append(data)if ifNotUpdate == "0":updateAll(updateData,table,searchKeys)insertAll(insertData,table,searchKeys,ifIgnoreSearchKey)conn.close()passdef updateAll(datas,table,searchKeys):#同時(shí)更新多條數(shù)據(jù)if len(datas) == 0:returnconn = getConnection()cursor = conn.cursor()sets = {}updateSql = "UPDATE `"+table+"` set "whereValues = []whereKey = "WHERE CONCAT(`"+"`,',',`".join(searchKeys)+"`) IN "for data in datas:whereValue = []for searchKey in searchKeys:whereValue.append(data[searchKey])whereValueString = ",".join(whereValue)whereValues.append(whereValueString)for key in data:if key in searchKeys:passelse:searchValue = []for searchKey in searchKeys:searchValue.append(str(data[searchKey]))searchValueString = ",".join(searchValue)try:sets[key][searchValueString] = data[key]except KeyError as e:sets[key] = {}sets[key][searchValueString] = data[key]searchKeysString = "(`"+"`,',',`".join(searchKeys)+"`)"whereValuesString = "('"+"','".join(whereValues)+"')"setStringArray = []for key1 in sets:setString = ""for key2 in sets[key1]:if setString == "":setString = "`"+key1+"` = CASE WHEN (CONCAT"+searchKeysString+"='"+key2+"') THEN '"+sets[key1][key2]+"'"else:setString = setString + " WHEN (CONCAT"+searchKeysString+"='"+key2+"') THEN '"+sets[key1][key2]+"'"setString += " END "setStringArray.append(setString)setStrings = ",".join(setStringArray)whereStrings = whereKey + whereValuesStringupdateSql += setStringsupdateSql += whereStringsprint updateSqltry:cursor.execute(updateSql)result = cursor.fetchall()except Exception as e:print e.messageprint updateSqlconn.commit()conn.close()def insertAll(datas,table,searchKeys,ifIgnoreSearchKey):#多條數(shù)據(jù)同時(shí)添加if len(datas) == 0:returnconn = getConnection()cursor = conn.cursor()keys=[]for key in datas[0]:if key not in searchKeys or ifIgnoreSearchKey!= "1":keys.append(key)insertSql = "INSERT INTO "+table+" (`"+"`,`".join(keys)+"`) VALUES "valueStrings = []for data in datas:value = []for key in keys:value.append(data[key])valueString = "('" + "','".join(value) + "')"valueStrings.append(valueString)insertSql += ",".join(valueStrings)print insertSqltry:cursor.execute(insertSql)conn.commit()conn.close()except Exception as e:print insertSqlprint e.message

如果有幫到您，打個(gè)賞唄

總結(jié)

以上是生活随笔為你收集整理的python封装一个效率极高的 批量更新、插入合一的工具的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。