Python bulk Delete tables with the same MySQL prefix

1, the General game log database will store a large number of player behavior log, a behavior of a table, a day to generate a new table, a day will have a different behavior of the table, will usually retain the player log about 1 years, for more than 1 years of log need to delete

2,log database for one year save more than 1W tables, write a tool in Python, to efficiently delete the form in bulk

Solution Ideas:

1, analyze the table name, find the same law, I maintain the game log Library table name suffix is usually generated by the year + month + day, with this rule is good to do

2, Generate a drop table table name with a SQL syntax

#下面这条语法是模糊匹配找出2016年的所有表, then generate ' drop table ', table_name, '; ' Delete syntax

Select CONCAT (' drop table ', table_name, '; ') From Information_schema.tables Where table_name like '%_16% ';

The results of the implementation are as follows:

drop table logacclogout_160401;
drop table logacclogout_160402;
drop table logacclogout_160403;

drop table optradeinfo_160421;

drop table optradeinfo_160422;
drop table optradeinfo_160423;
drop table optradeinfo_160424;

3, the result of the execution is saved in the file, used for the Python script to read the

4,python Loop reads the file just saved, then MySQL module logs on to MySQL server

Module is mysqldb, installation method: Yum-y Install mysql-python*

The code is as follows:

#!/usr/bin/python#-*-coding:utf-8-*-Importmysqldbdb= MySQLdb.connect ("192.168.135.156","Mysql_user","Mysql_password",the game_log") Cursor=db.cursor () #获得mysql游标open_file= Open ('Jieguo_result_201_17.txt','R') #先打开已保存的文件 forLineinchopen_file.readlines (): #for循环逐行读取每条Try:        PrintLine , Cursor.execute (line) #执行sql删除语法 db.commit () #提交请求exceptException as E:Printeife: #遇到错误直接跳过, continue with the following delete syntaxContinuedb.close () open_file.close ( )

Python bulk Delete tables with the same MySQL prefix