def insertOrUpdateRecord(jsondata,tblName): for i in range(len(jsondata)): Id = jsondata[i]['Id'] query ="delete from "+tblName+" where Id='"+str(Id)+"'" c.execute(query) if "BoxID" in jsondata[i].keys() and "ReferenceID" in jsondata[i].keys(): query ="delete from "+tblName+" where BoxID='"+str(jsondata[i]['BoxID'])+"'" + " and ReferenceID='"+str(jsondata[i]['ReferenceID'])+"'" print('Record deleted for '+tblName+' :'+str(Id)) c.execute(query) query ='insert into '+tblName+'(' cols = '' vals = '' try: for key in jsondata[i].keys(): try: key = key.encode('UTF-8') except Exception,e: key = key value =jsondata[i][key] try: value = value.encode('UTF-8') except Exception,e: value = value try: value = value.replace('"','') except Exception,e: value = value #Check if it is a file to download and download it. if "IsActive" in jsondata[i].keys(): if jsondata[i]["IsActive"]==1: FileMgr.CheckAndDownloadFile(key,value,0,Id) else: FileMgr.CheckAndDownloadFile(key,value,0,Id) if 'Id' in key: print('Record Added/Updated for '+tblName+' :'+str(value)) if 'AlbumDetails' in key: insertOrUpdateRecord(value,'AlbumDetails') else: if 'BoxMappings' in key: insertOrUpdateRecord(value,"ArticleBoxMapping") else: if value is not None: cols = cols + key + ',' vals = vals +'"'+ str(value)+ '",'
def insertOrUpdateRecord(jsondata, tblName, ModuleId): for i in range(len(jsondata)): Id = jsondata[i]['Id'] query = "delete from " + tblName + " where Id='" + str(Id) + "'" c.execute(query) # print query query = 'insert into ' + tblName + '(' cols = '' vals = '' try: for key in jsondata[i].keys(): try: key = key.encode('UTF-8') except Exception, e: key = key value = jsondata[i][key] try: value = value.encode('UTF-8') except Exception, e: value = value try: value = value.replace('"', '') except Exception, e: value = value if value is not None: cols = cols + key + ',' vals = vals + '"' + str(value) + '",'
def insertOrUpdateRecord(jsondata, tblName): for i in range(len(jsondata)): Id = jsondata[i]["Id"] query = "delete from " + tblName + " where Id='" + str(Id) + "'" c.execute(query) query = "insert into " + tblName + "(" cols = "" vals = "" try: for key in jsondata[i].keys(): try: key = key.encode("UTF-8") except Exception, e: key = key value = jsondata[i][key] try: value = value.encode("UTF-8") except Exception, e: value = value try: value = value.replace('"', "") except Exception, e: value = value if value is not None: cols = cols + key + "," vals = vals + '"' + str(value) + '",'
def insertOrUpdateRecord(jsondata,tblName): for i in range(len(jsondata)): Id = jsondata[i]['Id'] query ="delete from "+tblName+" where Id='"+str(Id)+"'" c.execute(query) query ='insert into '+tblName+'(' cols = '' vals = '' try: for key in jsondata[i].keys(): try: key = key.encode('UTF-8') except Exception,e: key = key value =jsondata[i][key] try: value = value.encode('UTF-8') except Exception,e: value = value try: value = value.replace('"','') except Exception,e: value = value if 'Id' in key: print('Record Added/Updated for '+tblName+' :'+str(value)) if 'AlbumDetails' in key: insertOrUpdateRecord(value,'AlbumDetails') else: if 'BoxMappings' in key: insertOrUpdateRecord(value,"ArticleBoxMapping") else: if value is not None: cols = cols + key + ',' vals = vals +'"'+ str(value)+ '",'
def insertOrUpdateRecord(jsondata,tblName): for i in range(len(jsondata)): Id = jsondata[i]['Id'] query ="delete from "+tblName+" where Id='"+str(Id)+"'" c.execute(query) if "BoxID" in jsondata[i].keys() and "ReferenceID" in jsondata[i].keys(): query ="delete from "+tblName+" where BoxID='"+str(jsondata[i]['BoxID'])+"'" + " and ReferenceID='"+str(jsondata[i]['ReferenceID'])+"'" print('Record deleted for '+tblName+' :'+str(Id)) c.execute(query) query ='insert into '+tblName+'(' cols = '' vals = '' try: for key in jsondata[i].keys(): try: key = key.encode('UTF-8') except Exception,e: key = key value =jsondata[i][key] try: value = value.encode('UTF-8') except Exception,e: value = value try: value = value.replace('"','') except Exception,e: value = value if 'Id' in key: print('Record Added/Updated for '+tblName+' :'+str(value)) if value is not None: cols = cols + key + ',' vals = vals +'"'+ str(value)+ '",'
if 'SurveyMapping' in key: insertOrUpdateRecord(value,'SurveyBoxMapping',Id) else: if 'SurveyQuestions' in key and not 'SurveyQuestionsOptions' in key: insertOrUpdateRecord(value,"SurveyQuestions",Id) else: if 'SurveyQuestionsOptions' in key: insertOrUpdateRecord(value,"SurveyQuestionOptions",Id) else: if value is not None: cols = cols + key + ',' vals = vals +'"'+ str(value)+ '",' query += cols[:-1] +') values('+vals[:-1]+');' c.execute(query) except Exception,e: print 'Insert Error for ['+ tblName +'] id=' + str(Id) +' details:'+ str(e)+'\nQuery:'+query continue imgpath = GetCMSData.GetData('Surveys') insertOrUpdateRecord(imgpath,"Survey",None) con = sqlite3.connect(ConfReader.GetSyncDBPath()) con.isolation_level = None c = con.cursor() c.execute("update Survey set ProvinceId = -1 where ProvinceId is Null;") con.close()
if 'Id' in key: print('Record Added/Updated for '+tblName+' :'+str(value)) if 'AlbumDetails' in key: insertOrUpdateRecord(value,'AlbumDetails') else: if 'BoxMappings' in key: insertOrUpdateRecord(value,"ArticleBoxMapping") else: if value is not None: cols = cols + key + ',' vals = vals +'"'+ str(value)+ '",' query += cols[:-1] +') values('+vals[:-1]+');' c.execute(query) #print(query) except Exception,e: print 'Insert Error for ['+ tblName +'] id=' + str(Id) +' details:'+ str(e) continue #db.commit() #def LoadDB(): #imgpath = GetCMSData.GetData('Adverts') #insertOrUpdateRecord(imgpath,"Language")
def insertOrUpdateRecord(jsondata, tblName): for i in range(len(jsondata)): Id = jsondata[i]["Id"] query = "delete from " + tblName + " where Id='" + str(Id) + "'" c.execute(query) if "BoxID" in jsondata[i].keys() and "ReferenceID" in jsondata[i].keys(): query = ( "delete from " + tblName + " where BoxID='" + str(jsondata[i]["BoxID"]) + "'" + " and ReferenceID='" + str(jsondata[i]["ReferenceID"]) + "'" ) print ("Record deleted for " + tblName + " :" + str(Id)) c.execute(query) query = "insert into " + tblName + "(" cols = "" vals = "" try: for key in jsondata[i].keys(): try: key = key.encode("UTF-8") except Exception, e: key = key value = jsondata[i][key] try: value = value.encode("UTF-8") except Exception, e: value = value try: value = value.replace('"', "") except Exception, e: value = value # Check if it is a file to download and download it. if "IsActive" in jsondata[i].keys(): if jsondata[i]["IsActive"] == 1: FileMgr.CheckAndDownloadFile(key, value, 0, Id) else: FileMgr.CheckAndDownloadFile(key, value, 0, Id) if "Id" in key: print ("Record Added/Updated for " + tblName + " :" + str(value)) if "AlbumDetails" in key: insertOrUpdateRecord(value, "AlbumDetails") else: if "BoxMappings" in key: insertOrUpdateRecord(value, "ArticleBoxMapping") else: if value is not None: cols = cols + key + "," vals = vals + '"' + str(value) + '",'
def insertOrUpdateRecord(jsondata,tblName,ParentID): if 'CompetitionBoxMapping' in tblName and ParentID is not None: query = "delete from " + tblName + " where CompetitionID='" + str(ParentID) + "'" print('Records deleted for ' + tblName + ' for CompetitionID :' + str(ParentID)) c.execute(query) for i in range(len(jsondata)): Id = jsondata[i]['Id'] query ="delete from "+tblName+" where Id='"+str(Id)+"'" c.execute(query) if "BoxID" in jsondata[i].keys() and "CompetitionID" in jsondata[i].keys(): query ="delete from "+tblName+" where BoxID='"+str(jsondata[i]['BoxID'])+"'" + " and CompetitionID='"+str(jsondata[i]['CompetitionID'])+"'" print('Record deleted for '+tblName+' :'+str(Id)) c.execute(query) query ='insert into '+tblName+'(' cols = '' vals = '' try: for key in jsondata[i].keys(): try: key = key.encode('UTF-8') except Exception,e: key = key value =jsondata[i][key] try: value = value.encode('UTF-8') except Exception,e: value = value try: value = value.replace('"','') except Exception,e: value = value #Check if it is a file to download and download it. if "IsActive" in jsondata[i].keys(): if jsondata[i]["IsActive"]==1: FileMgr.CheckAndDownloadFile(key,value,4,Id) else: FileMgr.CheckAndDownloadFile(key,value,4,Id) if 'Id' in key: print('Record Added/Updated for '+tblName+' :'+str(value)) if 'CompetitionMapping' in key: insertOrUpdateRecord(value,'CompetitionBoxMapping',Id) else: if 'CompetitionQuestions' in key and not 'CompetitionQuestionsOptions' in key: insertOrUpdateRecord(value,'CompetitionQuestions',Id) else: if 'CompetitionQuestionsOptions' in key: insertOrUpdateRecord(value,"CompetitionQuestionOptions",Id) else: if 'CompetitionWinners' in key: insertOrUpdateRecord(value,"CompetitionWinner",Id) else: if value is not None: cols = cols + key + ',' vals = vals +'"'+ str(value)+ '",'