Python incremental loop method to delete MySQL table data _python

Requirements Scenario:

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

Words not much, on the script:

# coding:utf-8 Import mysqldb import time # delete config delete_datetime = ' 2016-08-31 23:59:59 ' delete_rows = 10000 EX
Ec_detail_file = ' exec_detail.txt ' sleep_second_per_batch = 0.5 Datetime_format = '%y-%m-%d%x ' # MySQL Connection Config Default_mysql_host = ' localhost ' default_mysql_port = 3358 default_mysql_user = "root" Default_mysql_password = ' roo@0123 9876 ' Default_mysql_charset = "UTF8" default_mysql_connect_timeout = default_database_name = ' testdb001 ' def get_time _string (dt_time): "" Gets the time string in the specified format:p Aram Dt_time: The time to convert to a string: return: Returns the string "" of the specified format "" Global Datetime_format return TIME.S
Trftime (Datetime_format, Dt_time) def print_info (message): "" Outputs the message to the console and writes the message to the log file:p Aram message: string to output : Return: No Returns "" "" "Print (message) global Exec_detail_file new_message = get_time_string (Time.localtime ()) + CHR (+) + str (Me ssage) Write_file (Exec_detail_file, New_message) def write_file (file_path, message): "" Append the incoming message to the File_ Path in the file you specify, create the directory where the file is located:p Aram File_path:The file path to write:p Aram message: The information to write: return: "" "File_handle = Open (File_path, ' a ') File_handle.writelines (Messages) # append a newline to square Browse File_handle.writelines (CHR) file_handle.close () def get_mysql_connection (): "" Returns 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) return conn def mysql_exec (Sql_script, Sql_param=none): "" Executes the incoming script, returns the number of rows affected:p Aram Sql_script::p Aram Sql_param:: R Eturn: Script Last statement execution affects 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. RowCount Else:cursor.execute (sql_script) Row_count = Cursor.rowcount Conn.commit () cursor.close () Conn.close () except Exception, E:print_info ("Execute Exception:" + STR (e)) Row_count = 0 return row_count def mysql_query (Sql_script, Sql_param=none): "" Executes the incoming SQL script and returns the query results:p Aram Sql_scrip T::p Aram Sql_param:: Return: Returns the 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:curs Or.execute (sql_script) Exec_result = Cursor.fetchall () cursor.close () Conn.close () return exec_result except Exception, E:print_info ("Execute exception:" + str (e)) def get_id_range (table_name): "" To get the maximum ID, minimum ID, delete Total row number:p Aram table in the incoming table  _name: Table to delete: return: Returns the maximum ID, minimum ID, delete Total row number "" "Global delete_datetime Sql_script =" "SELECT Max (ID) as max_id, min (id)
As min_id, COUNT (1) as Total_count from {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's a hole here, maybe Total_count is not 0 but max_id and min_id are NOne's case # so to determine if max_id and min_id are null if (max_id is None) or (min_id are none): max_id, min_id, total_count = 0, 0, 0 return m ax_id, min_id, Total_count def delete_data (table_name): max_id, min_id, Total_count = Get_id_range (table_name) temp_id = m in_id while 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_rows print (sql_script) row_ Count = Mysql_exec (sql_script) print_info ("Affect number of rows: {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 is {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 ID, it gives us the opportunity to incrementally cycle the deletion, find out the maximum ID and minimum ID that satisfies the deletion condition, then increment by ID, each time a small range (such as 10,000) to delete.

Implementation benefits:

Realize the effect of "small ax chopping big wood", transaction small, the impact on the line is small, print out the current processing to "ID", you can close at any time, slightly modified code can start from the ID, convenient.

Insufficient implementation:

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

The above is a small series to introduce the Python incremental cycle to delete the MySQL table data, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!