python--incremental loop to delete MySQL table data

Requirements Scenario:

There is a business database, using the MySQL 5.5 version, write a large amount of data each day, you need to either regularly delete the "specified period before" data in multiple tables, it is easy to implement in SQL Server, write a few while loop to do, although there are similar features in MySQL, Zennai himself not proficient, Python is then used to achieve

Words not much, on the script:

#Coding:utf-8ImportMySQLdbImport Time#Delete ConfigDelete_datetime ='2016-08-31 23:59:59'delete_rows= 10000Exec_detail_file='Exec_detail.txt'Sleep_second_per_batch= 0.5Datetime_format='%y-%m-%d%x'#MySQL Connection ConfigDefault_mysql_host ='localhost'Default_mysql_port= 3358Default_mysql_user="Root"Default_mysql_password='[email protected]'Default_mysql_charset="UTF8"Default_mysql_connect_timeout= 120Default_database_name='testdb001'defget_time_string (dt_time):"""gets the time string for the specified format:p Aram Dt_time: The time to convert to a string: return: Returns a string in the specified format"""    GlobalDatetime_formatreturntime.strftime (Datetime_format, Dt_time)defprint_info (message):"""output message to the console and write message to the log file:p Aram message: The string to output: return: no Return"""    Print(message)Globalexec_detail_file new_message= Get_time_string (Time.localtime ()) + CHR (13) +STR (message) write_file (Exec_detail_file, New_message)defwrite_file (file_path, message):"""append the incoming message to the file specified by File_path to create the directory where the file is located:p Aram File_path: The file path to write:p Aram message: Message to write: return :    """File_handle= Open (File_path,'a') file_handle.writelines (message)#append a line break for easy browsingFile_handle.writelines (Chr (13) ) File_handle.close ()defget_mysql_connection ():"""to return a database connection based on the default configuration: return: Database connection"""Conn=MySQLdb.connect (Host=Default_mysql_host, Port=default_mysql_port, User=Default_mysql_user, passwd=Default_mysql_password, Connect_timeout=Default_mysql_connect_timeout, CharSet=Default_mysql_charset, DB=default_database_name)returnConndefMysql_exec (Sql_script, sql_param=None):"""executes an incoming script that returns the number of rows affected:p Aram Sql_script::p Aram Sql_param:: Return: Script The last statement that affects the number of rows"""    Try: Conn=get_mysql_connection () print_info ("Execute script on server {0}: {1}". Format (Conn.get_host_info (), sql_script)) cursor=conn.cursor ()ifSql_param is  notNone:cursor.execute (Sql_script, Sql_param) Row_count=Cursor.rowcountElse: Cursor.execute (sql_script) Row_count=cursor.rowcount Conn.commit () cursor.close () Conn.close ( )exceptException, E:print_info ("Execute Exception:"+str (e)) Row_count=0returnRow_countdefmysql_query (Sql_script, sql_param=None):"""executes the incoming SQL script and returns the query result:p Aram Sql_script::p Aram Sql_param:: Return: Returning SQL query Results"""    Try: Conn=get_mysql_connection () print_info ("Execute script on server {0}: {1}". Format (Conn.get_host_info (), sql_script)) cursor=conn.cursor ()ifSql_param! ="': Cursor.execute (Sql_script, Sql_param)Else: Cursor.execute (sql_script) Exec_result=Cursor.fetchall () cursor.close () Conn.close ( )returnExec_resultexceptException, E:print_info ("Execute Exception:"+str (e))defGet_id_range (table_name):"""gets the maximum ID, minimum ID, and total number of rows to delete from the incoming table:P Aram table_name: The table to be deleted: return: Returns the maximum ID to delete, the minimum ID, the total number of rows deleted"""    Globaldelete_datetime Sql_script="""Selectmax (ID) as max_id,min (ID) as Min_id,count (1) as Total_countfrom {0}where create_time <= ' {1} ';""". Format (table_name, delete_datetime) Query_result= mysql_query (Sql_script=sql_script, sql_param=None) max_id, min_id, Total_count=Query_result[0]#there is a hole here, there may be a situation where total_count is not 0 but max_id and min_id are none    #therefore determine if max_id and min_id are null    if(max_id isNone)or(min_id isNone): max_id, min_id, Total_count=0, 0, 0returnmax_id, min_id, Total_countdefDelete_data (table_name): max_id, min_id, Total_count=Get_id_range (table_name) temp_id=min_id whiletemp_id <=Max_id:sql_script="""DELETE from {0}where id <= {1}and id >= {2}and create_time <= ' {3} '; """. Format (table_name, TEMP_ID +delete_rows, temp_id, delete_datetime) temp_id+=delete_rowsPrint(sql_script) Row_count=mysql_exec (Sql_script) print_info ("number of rows affected: {0}". Format (row_count)) Current_percent= (temp_id-min_id) * 1.0/(MAX_ID-min_id) Print_info ("current Progress {0}/{1}, remaining {2}, progress {3}%". Format (temp_id, max_id, max_id-temp_id,"%.2f"%current_percent)) Time.sleep (Sleep_second_per_batch) print_info ("the current table {0} has no data to delete". Format (table_name)) Delete_data ('TB001') Delete_data ('TB002') Delete_data ('TB003')

Execution effect:

Implementation principle:

Since the table has a self-incrementing ID, it gives us an opportunity to iterate over the increment, find the maximum ID and the minimum ID that meet the deletion criteria, and then increment by ID to delete each small range (such as 10,000).

Implementation benefits:

Realize the "Small axe chop big firewood" effect, transaction small, the impact on the line is small, print out the current processing to the "ID", can be closed at any time, slightly modified code can start from the ID, convenient.

Insufficient implementation:

In order to prevent the master-slave delay is too high, the use of each delete SLEEP1 second way, relatively rough, the best way should be the cycle scan this copy link, according to the delay to adjust the sleep cycle, anyway, scripted, and then intelligent Point!

The focus of this article is still sister, can not let you crossing white run a trip, is not!!!

python--incremental loop to delete MySQL table data