Last week, met a pit dad thing, is the manager asked me to Excel format voucher form (a table in a financial account) in addition to the account of the last level of the total deletion, which makes not proficient in office I am very headache, because the voucher is only one year a hundred thousand of records (manual words from the early delete to the end of the night can be deleted). Then I wondered if I could get Excel into MySQL and then use Python to handle it. Because Python is very handy for working with MySQL, I finally wrote a little program to get this done.
#Coding=utf8ImportMySQLdb#Delete the parent account for the account in the voucher table, keeping only the stub account for the accountclassConn#define the classes used to execute SQLConnect = MySQLdb.connect ("192.168.30.235","Shiyan","123456","Hongcheng") Result=""SQL="" def __init__(self,sql): Self.sql=SQLdefexe (self): cur=self.connect.cursor () cur.execute (self.sql) Self.result=Cur.fetchall () self.connect.commit ( )#self.connect.close () returnSelf.resultdef __del__(self):PassN= 4leve01="____" #First- level account wildcard whilen! = 0:#Loop through the accounts except the first-level account, starting with the stub account and looping to the parent accountLEVE01 = leve01 + N"___"N= N-1Make_sql="SELECT * from code_14 where kmdm like \ ""+leve01+"\"" #load an account that meets the criteriaQuery01 =Conn (make_sql) leve01="____"RESULT1=Query01.exe () forlist01inchRESULT1:#looping through deletes the parent account of the current accountsjkm = List01[0][0:len (list01)-4] Sql_del="Delete from hc14 where kmdm = \ ""+sjkm+"\"" #Combining Delete SQLQUERY02 =Conn (sql_del). exe ()PrintSql_del#Print Delete SQLPrint "The traversal is complete! "
Delete the Python script for the parent account in the voucher table account