Python incremental loop deletes 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-8import Mysqldbimport time# Delete configdelete_datetime = ' 2016-08-31 23:59:59 ' DELETE_ROWS = 10000EXEC_DET Ail_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 = ' roo@01239876 ' Default_mysql_charset = "UTF8" default_mysql_connect_timeout = 120default_database_name = ' testdb001 ' def Get_time_string (dt_time): "" "gets the time string for the specified format: param dt_time: The time to convert to a string: return: Returns the string" "in the specified format," "Global datetime_ Formatreturn time.strftime (Datetime_format, Dt_time) def print_info (message): "" Outputs a message to the console and writes the message to the log file: Param message: The string to output: return: no Return "" "Print (message) Global exec_detail_filenew_message = get_time_string ( Time.localtime ()) + CHR (+) + str (message) write_file (Exec_detail_file, New_message) def write_file (file_path, message) : "" To append the incoming message to the file specified by File_path, create the directory where the file is located: param file_path: File path to write: param message: Message to write: return: """File_handle = open (File_path, ' a ') file_handle.writelines (message) # Append a newline to facilitate browsing File_handle.writelines (CHR) file _handle.close () def get_mysql_connection (): "" Returns the 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) return Conndef mysql_exec ( Sql_script, Sql_param=none): "" "executes the incoming script, returns the number of rows affected: param 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 () If Sql_param is not None:cursor.execute (Sql_script, sql_param) Row_count = Cursor.rowcountelse: Cursor.execute (sql_script) Row_count = Cursor.rowcountconn.commit () cursor.close () conn.close () except Exception, E: Print_info ("Execute exception:" + str (e)) Row_count = 0return row_countdef mysql_query (Sql_script, Sql_param=none): "" " Perform an incomingSQL script and returns the result of the query: param sql_script::p Aram Sql_param::return: Return SQL query Result "" "Try:conn = Get_mysql_connection () print_info (" Execute script on server {0}: {1} '. Format (Conn.get_host_info (), sql_script)) cursor = Conn.cursor () if sql_param! = ': Cursor.execute ( Sql_script, Sql_param) Else:cursor.execute (sql_script) Exec_result = Cursor.fetchall () cursor.close () Conn.close () Return exec_resultexcept Exception, E:print_info ("Execute Exception:" + str (e)) def get_id_range (table_name): "" " Follow the incoming table for the maximum ID, minimum ID, and total number of rows to delete: param table_name: Table to delete: return: Returns the maximum ID, minimum ID, total number of rows to delete, "" "Global Delete_datetimesql_ 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, may appear total_count not 0 but max_id and min_id for the case of none # so judge whether max_id and min_id are Nullif (max_id is None) or (min_id is None): max_id, min_id, total_count = 0, 0, 0return max_id, min_id, Total_countdef delete_data (table_name): max_id, min_id, Total_count = Get_id_range (table_name) temp_id = Min_ Idwhile temp_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 above is a small series to introduce you to the Python incremental cycle delete MySQL table data, I hope that we have some help, if you have any questions please give me a message, small series will promptly reply to you. Thank you very much for your support for topic.alibabacloud.com!