Python-batch data migration under the same mysql database, python-mysql

Python-batch data migration under the same mysql database, python-mysql

I recently took over some mysql database maintenance and found that mysql is a waste in batch operations. Compared with ms SQL SERVER, it is simply "not the same day ".

After consulting the master of MySQL, one way to solve the problem of data migration is to directly "One step in place" and insert all data queries to another table at a time, then delete the original table data. Another processing method is to use the pt -- archiver tool for archiving.

 

However, the "One step in place" method is too exciting, and the pt -- archiver tool is not easy to use. Because most of the tables currently use auto-incrementing IDS as the primary key, on this basis, you can write a small script with a thick face for your reference:

# Coding: utf-8import MySQLdbimport time # common configEXEC_DETAIL_FILE = 'exec_detail.txt 'datetime _ FORMAT = '% Y-% m-% d % x' Default _ MySQL_Host = '2017. 168.166.169 'default _ MySQL_Port = login = "mysql_admin" Default_MySQL_Password = 'mysql @ Admin @ pwd' Default _ MySQL_Charset = "utf8" Login = 120 # Transfer login = "db001" Login = "Tb2001" Transfer_Target_Table_Name = "tb2001_his" Transfer_Condition = "dt <'2017-10-01 '" condition = 0.5def get_time_string (dt_time): "Get the time string in the specified format: param dt_time: The time for converting to a string: return: returns the string "global DATETIME_FORMAT return time in the specified format. strftime (DATETIME_FORMAT, dt_time) def get_time_string (dt_time): return time. strftime ("% Y-% m-% d % X", dt_time) def highl Ight (s): return "% s [30; 2 m % s [1 m" % (chr (27), s, chr (27 )) def print_warning_message (message): "Display message content in Red: param message: message content: return: no return value" "message = str (message) print (highlight ('') +" % s [31; 1 m % s [0 m "% (chr (27), message, chr (27 ))) def print_info_message (message): "sends a reminder message in a green font: param message: message content: return: no return value" "message = str (message) print (highlight ('') +" % s [32; 2 m % s [0 m "% (chr (27), message, chr (27) def write_file (file_path, message ): "to append the incoming message to the file specified by file_path, first create the directory where the file is located: param file_path: Path of the file to be written: param message: Information to be written: return: "file_handle = open (file_path, 'A') file_handle.writelines (message) # append a line feed to facilitate browsing file_handle.writelines (chr (13) file_handle.close () def get_mysql_connection (): "return database connection according to default configuration: return: Database Connection" "conn = MySQLdb. connect (host = Def Login, port = login, user = Default_MySQL_User, passwd = Default_MySQL_Password, connect_timeout = login, charset = login, db = login) return conndef mysql_exec (SQL _script, SQL _param = None ): "Run the input script and return the affected rows: param SQL _script: param SQL _param: return: the number of affected rows in the last statement of the script" try: conn = get_mysql_connection () print_info_message ("in the server Run the script on the server {0}: {1 }". format (conn. get_host_info (), SQL _script) cursor = conn. cursor () if SQL _param is not None: cursor.exe cute (SQL _script, SQL _param) else: cursor.exe cute (SQL _script) affect_rows = cursor. rowcount conn. commit () cursor. close () conn. close () return affect_rows failed t Exception as ex: cursor. close () conn. rollback () raise Exception (str (ex) def mysql_exec_detail (SQL _script_list): "Run the input script and return Number of affected rows: param SQL _script_list: List of scripts to be executed. Each element in the List is SQL _script, and SQL _param is returned to return the List of rows affected by execution of each script. "" try: conn = get_mysql_connection () exec_result_list = [] for SQL _script, SQL _param in SQL _script_list: print_info_message ("execute script on server {0}: {1 }". format (conn. get_host_info (), SQL _script) cursor = conn. cursor () if SQL _param is not None: cursor.exe cute (SQL _script, SQL _param) else: cursor.exe cute (SQL _s Rows) affect_rows = cursor. rowcount exec_result_list.append ("affected rows: {0 }". format (affect_rows) conn. commit () cursor. close () conn. close () return exec_result_list failed t Exception as ex: cursor. close () conn. rollback () raise Exception (str (ex) def mysql_query (SQL _script, SQL _param = None): "Run the input SQL script and return the query result: param SQL _script :: param SQL _param: return the SQL query result "" try: conn = get_mysql_connection () Print_info_message ("Run Script: {1} on server {0}". format (conn. get_host_info (), SQL _script) cursor = conn. cursor () if SQL _param! = '': Cursor.exe cute (SQL _script, SQL _param) else: cursor.exe cute (SQL _script) exec_result = cursor. fetchall () cursor. close () conn. close () return exec_result failed t Exception as ex: cursor. close () conn. close () raise Exception (str (ex) def get_column_info_list (table_name): SQL _script = "" DESC {0 }""". format (table_name) column_info_list = [] query_result = mysql_query (SQL _script = SQL _script, SQL _param = No Ne) for row in query_result: column_name = row [0] column_key = row [3] column_info = column_name, column_key column_info_list.append (column_info) return column_info_listdef get_id_range (): "Get the maximum ID, minimum ID, and total number of rows to be deleted from the input table: return: return the maximum ID, minimum ID, and total number of rows to be deleted. "" global Transfer_Condition global Transfer_Rows_Per_Batch SQL _script = "SELECTMAX (ID) AS MAX_ID, MIN (ID) AS MIN_ID, COUNT (1) AS Total_CountFROM {0} WHERE {1 };""". format (Transfer_Source_Table_Name, Transfer_Condition) query_result = mysql_query (SQL _script = SQL _script, SQL _param = None) max_id, min_id, total_count = query_result [0] # a pitfall exists here, it may occur that total_count is not 0 but max_id and min_id are None # therefore, determine whether max_id and min_id are NULL if (max_id is None) or (min_id is None): max_id, min_id, total_count = 0, 0, 0 return max_id, min_id, total_countdef check_env (): try: Source_columns_info_list = get_column_info_list (Transfer_Source_Table_Name) target_columns_info_list = get_column_info_list (Transfer_Target_Table_Name) if len (source_columns_info_list )! = Len (target_columns_info_list): print_info ("the number of columns in the source and target tables is incorrect. migration conditions are not met.") return False column_count = len (source_columns_info_list) id_flag = False for column_id in range (column_count): source_column_name, source_column_key = primary [column_id] target_column_name, target_column_key = primary [column_id] if source_column_name! = Target_column_name: print_info ("the column names of the source and target tables do not match, and the migration conditions are not met") return False if source_column_name.lower () = 'id' \ and source_column_key.lower () = 'pri '\ and target_column_name.lower () = 'id' \ and target_column_key.lower () = 'pri': id_flag = True if not id_flag: print_info ("the id column for the primary key is not found and the Migration condition is not met") return False return True failed t Exception as ex: print_info ("execution Exception, Exception: {0 }". format (ex. message) return Falsedef main (): flag = check_env () if not flag: return loop_trans_data () def trans_data (current_min_id, current_max_id ): global Transfer_Source_Table_Name global Transfer_Target_Table_Name global Transfer_Condition global Transfer_Rows_Per_Batch print_info_message ("*" * 70) copy_data_script = "insert into {0} SELECT * FROM {1} where id >={ 2} and id <{3} AND {4 };""". format (Transfer_Target_Table_Name, Transfer_Source_Table_Name, current_min_id, current_max_id, Transfer_Condition) delete_data_script = "" delete from {0} where id in (select idfrom {1} where id >={ 2} and id <{3 }) and id >={ 4} and id <{5 };""". format (values, values, current_min_id, current_max_id, current_min_id, current_max_id) SQL _script_list = [] values = copy_data_script, None values (tem_ SQL _script) tem_ SQL _script = delete_data_script, None values (values) exec_result_list = Response (SQL _script_list) print_info_message ("execution result:") for item in exec_result_list: print_info_message (item) def evaluate (): max_id, min_id, total_count = get_id_range () if min_id = max_id: print_info_message ("no data needs to be carried forward") return current_min_id = min_id global while current_min_id <= max_id: current_max_id = current_min_id + {trans_data (current_min_id, current_max_id) current_percent = (current_max_id-min_id) * 100.0/(max_id-min_id) left_rows = max_id-current_max_id if left_rows <0: left_rows = 0 current_percent_str = "%. 2f "% current_percent info =" Current replication progress {0}/{1}, remaining {2}, progress: {3} % ". format (current_max_id, max_id, left_rows, current_percent_str) print_info_message (info) time. sleep (Sleep_Second_Per_Batch) current_min_id = current_max_id print_info_message ("*" * 70) print_info_message ("execution completed") if _ name _ = '_ main __': main ()View Code

 

You need to modify the database connection information as per your scenario:

Information about the tables to be migrated:

 

Mysql script for generating test data:

Create table 'tb2001 '('id' int (11) not null AUTO_INCREMENT, 'c1' varchar (200) default null, 'dt 'datetime default null, primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8; create table tb2001_his like tb2001; insert tb2001 (c1, dt) select 'abc ', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM mysql. user; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc ', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc ', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001; insert tb2001 (c1, dt) select 'abc ', date_add (localtime (), interval FLOOR (RAND () * 20000) hour) FROM tb2001;View Code

 

The final running result is as follows:

The display is simple and rude. If you are interested, you can modify it on this basis!

========================================================== ======================================