djanfo清空mysql_使用Django清理数据库中的数据

客官°小女子只卖身不卖艺 2022-10-25 05:52 181阅读 0赞

def datetimestr():

return datetime.now().strftime('%Y%m%d-%H%M%S')+'>>>'

def update\_dcData(req):

log\_path='apps/dc/l10n\_reports'

update\_dcData\_log=open(log\_path+'updateDcdataLog.log','w+')

sql\_getProjectIDs='select a.project\_id from' \\

' (select count(\*) num,project\_id from dc\_data where lableName=%s group by project\_id) a,' \\

' (select count(\*) num ,project\_id from management\_project\_target\_lang group by project\_id) b' \\

' where a.project\_id=b.project\_id and a.num!=b.num order by project\_id'

sql\_getAllProjectIDs='select project\_id from management\_project'

sql\_getLanguageSections='select b.name from management\_project\_target\_lang a,management\_l10nlanguage b' \\

' where a.project\_id=%s and a.l10nlanguage\_id=b.id and b.id!="1"'

sql\_getRecords='select id, languageSection,value from dc\_data where lableName =%s and project\_id=%s and important="1"'

sql\_addRecordByID='insert into dc\_data(lableName,languageSection,type,value,project\_id,task\_id,' \\

'important,unit,settlement,workload) ' \\

'select lableName,languageSection,type,value,project\_id,task\_id,important,unit,settlement,workload ' \\

'from dc\_data where id=%s'

sql\_updateLgs='update dc\_data set languageSection=%s where id=%s'

sql\_getLableNames='select lableName from dc\_data where lableName like "%%\_all" group by lableName'

update\_dcData\_log.write(datetimestr()+'sql\_getLableNames'+'>>>'+sql\_getLableNames+'\\n')

update\_dcData\_log.write(datetimestr()+'sql\_getRecords'+'>>>'+sql\_getRecords+'\\n')

update\_dcData\_log.write(datetimestr()+'sql\_getProjectIDs'+'>>>'+sql\_getProjectIDs+'\\n')

update\_dcData\_log.write(datetimestr()+'sql\_getLanguageSections'+'>>>'+sql\_getLanguageSections+'\\n')

update\_dcData\_log.write(datetimestr()+'sql\_addRecordByID'+'>>>'+sql\_addRecordByID+'\\n')

update\_dcData\_log.write(datetimestr()+'sql\_updateLgs'+'>>>'+sql\_updateLgs+'\\n')

context=Context(\{'msg':'Success'\})

resp=render\_to\_response("report/clean\_data.html", context,

context\_instance=RequestContext(req))

cursor=connection.cursor()

try:

cursor.execute(sql\_getLableNames)

lableNames=cursor.fetchall()

except Exception,e:

update\_dcData\_log.write(datetimestr()+'execute sql\_getLableNames error '+str(e)+'\\n')

context=Context(\{'msg':'Error'\})

return render\_to\_response("report/clean\_data.html", context,

context\_instance=RequestContext(req))

for lableName in lableNames:

try:

cursor.execute(sql\_getProjectIDs,\[lableName\[0\]\])

projectIDs=cursor.fetchall()

except Exception,e:

update\_dcData\_log.write(datetimestr()+'execute sql\_getProjectIDs error '+str(e)+'\\n')

context=Context(\{'msg':'Error'\})

return render\_to\_response("report/clean\_data.html", context,

context\_instance=RequestContext(req))

for pid in projectIDs:

try:

cursor.execute(sql\_getRecords,\[lableName\[0\],str(pid\[0\])\])

records=cursor.fetchall()

cursor.execute(sql\_getLanguageSections,\[str(pid\[0\])\])

languageSections=cursor.fetchall()

except Exception,e:

update\_dcData\_log.write(datetimestr()+'execute sql\_getRecords or sql\_getLanguageSections error '+str(e)+'\\n')

context=Context(\{'msg':'Error'\})

return render\_to\_response("report/clean\_data.html", context,

context\_instance=RequestContext(req))

values,lgs=\[\],\[\]

baseValue=str(records\[0\]\[2\])

baseID=str(records\[0\]\[0\])

for item in records:

lgs.append(str(item\[1\]))

values.append(str(item\[2\]))

if baseValue!=str(item\[2\]):

baseValue='false'

targetLgs=\[str(item\[0\]) for item in languageSections\]

if len(lgs)<1 or len(targetLgs)<1:

baseValue=='false'

if 'all' not in lgs:

try:

cursor.execute(sql\_addRecordByID,\[baseID\])

cursor.execute(sql\_updateLgs,\['all',baseID\])

transaction.commit\_unless\_managed()

except Exception,e:

update\_dcData\_log.write(datetimestr()+'execute sql\_addRecordByID or sql\_updateLgs error (all)'+str(e)+'\\n')

context=Context(\{'msg':'Error'\})

return render\_to\_response("report/clean\_data.html", context,

context\_instance=RequestContext(req))

update\_dcData\_log.write(datetimestr()+"all record is add into dc\_data,the lableName and projectID were "+str(lableName\[0\])+'-'+str(pid\[0\])+'\\n')

if baseValue=='false':

update\_dcData\_log.write(datetimestr()+"please update this record mutually,the lableName and projectID were "+str(lableName\[0\])+'-'+str(pid\[0\])+'\\n')

else:

if len(lgs)>len(targetLgs):

update\_dcData\_log.write(datetimestr()+"the lableName languageSection length is longer than target numbers lableName and projectID were "+str(lableName\[0\])+'-'+str(pid\[0\])+'\\n')

else:

for lg in targetLgs:

if lg not in lgs:

try:

cursor.execute(sql\_addRecordByID,\[baseID\])

cursor.execute(sql\_updateLgs,\[lg,baseID\])

transaction.commit\_unless\_managed()

except Exception,e:

update\_dcData\_log.write(datetimestr()+'execute sql\_addRecordByID or sql\_updateLgs error (lg)'+str(e)+'\\n')

context=Context(\{'msg':'Error'\})

return render\_to\_response("report/clean\_data.html", context,

context\_instance=RequestContext(req))

update\_dcData\_log.write(datetimestr()+lg+" record is add into dc\_data,the lableName and projectID were "+str(lableName\[0\])+'-'+str(pid\[0\])+'\\n')

update\_dcData\_log.close()

return resp