MySQL bulk modifies the same suffix table name

Perform steps

1. Use Concat to batch generate statements that modify table names

SELECTCONCAT ('ALTER TABLE', TABLE_NAME,'RENAME to', substring(TABLE_NAME,1, locate ('_postfix', table_name)),'_new_postfix',';' )  fromInformation_schema.tablesWheretable_name like '%_postfix';

2. Execute the generated statement once to complete the modification

—————————————————————————————————————————————————————————————————————————————————————————————————

The MySQL function explains:

1.substring (str,index,num) intercepts the specified substring (num characters from index) from a specified string (str).

Example:

Select substring ('class_name',2,5);

Execution result is ' Lass_ '

2.locate (Cstr,str[,position]) finds the coordinates of the substring (CStr) in the string (str), the optional parameter position the starting position of the lookup

Example:

SELECT Locate ('s','students_name'   SELECT Locate ('s','students_name',5 

The 1th statement executes with the result: 1

The 2nd statement executes with the result: 8

Python script bulk modifying table names

The work of the test environment to do the initialization work every day, you need to change the name of some of the table from yesterday to the day, it will modify the table name of the work written as a script.

Because the header is known and generally does not change, the table header list Inittable_prefix is defined directly, instead of using the SQL method described above (substring)

ImportMySQLdbImportdatetimeImportTracebacktoday= Datetime.date.today (). Strftime ('%y%m%d') Yesterday= (Datetime.date.today ()-Datetime.timedelta (Days=1)). Strftime ('%y%m%d') Inittable_prefix= ['exam_table1_','exam_table2_','exam_table3_',                'exam_table4_','exam_table5_','exam_table6_']today_list= [I+today forIinchinittable_prefix]lastday_list= [I+yesterday forIinchInittable_prefix]sqllit= ['ALTER TABLE%s RENAME to%s'% (Lastday_list[i],today_list[i]) forIinchRange (len (inittable_prefix))]classMymysql (object):def __init__(self): Self.conn=MySQLdb.connect (Host='127.0.0.1', Port= 3306, the user='Root', passwd='Root', DB='XT')    defrenametable (self,sql_list): forSqlinchSql_list:cur=self.conn.cursor ()Try: Cur.execute (SQL) Self.conn.commit ()except: Self.conn.rollback () traceback.print_exc ()finally: Cur.close ()defCloseconn (self): Self.conn.close ()if __name__=='__main__': Co=Mymysql () co.renametable (Sqllit) co.closeconn ()

MySQL bulk modifies the same suffix table name