Use Django to clear data in the database

Use Django to clear data in the database

Clean up data in the database

Problem description: in my system, due to history and various reasons, the data recorded in the table in the database is faulty, with duplicate and incomplete data.

Solution: first, because the amount of data is large, manual cleaning is definitely not good. Then, I want to write an SQL script to update it according to the agreed rules, you can use cursors to traverse records in a table. However, SQL is a structured query language rather than a process-oriented language, so although it is possible, it is not convenient to use process-oriented such as C and python. Later, I wanted to create a new method in my project and then call it through the address bar of the browser, you can.

PS: Although Django's orm is very convenient, It is very embarrassing to use it myself. I still have to search for examples on the Internet to find out how to use it.

Fortunately, the method for directly executing SQL statements is provided. During the cleaning process, I used to execute native SQL statements.

The Code is as follows:

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) ,'\
'(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. l10n1_age_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.exe cute (SQL _getLableNames)
LableNames = cursor. fetchall ()
Except t 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.exe cute (SQL _getProjectIDs, [lableName [0])
ProjectIDs = cursor. fetchall ()
Except t 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.exe cute (SQL _getRecords, [lableName [0], str (pid [0])
Records = cursor. fetchall ()
Cursor.exe cute (SQL _getLanguageSections, [str (pid [0])
LanguageSections = cursor. fetchall ()
Except t Exception, e:
Update_dcData_log.write (datetimestr () + 'execute SQL _getRecords or SQL _get1_agesections 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.exe cute (SQL _addRecordByID, [baseID])
Cursor.exe cute (SQL _updateLgs, ['all', baseID])
Transaction. commit_unless_managed ()
Except t 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.exe cute (SQL _addRecordByID, [baseID])
Cursor.exe cute (SQL _updateLgs, [lg, baseID])
Transaction. commit_unless_managed ()
Except t 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

-------------------------------------- Split line --------------------------------------

Install Nginx + uWSGI + Django on Ubuntu Server 12.04

Deployment of Django + Nginx + uWSGI

Django tutorial

Build a Django Python MySQL Linux development environment

Django details: click here
Django's: click here

This article permanently updates the link address: