Python implementation of database update script generation

When I was at work, the database used in the test environment was inconsistent with the database of the production environment, and when the database in our test environment was ready to be updated to the database in the production environment, we needed to prepare the update script, so it was accidentally forgotten where it was, where it was added, This is really a headache. So I try to use Python to implement the automatic generation update script, lest I have this bad memory, can't remember things.

The main operations are as follows:

1. In the original basedao.py, add the following method, so that the old can easily obtain the data of the database, the test database and production database to do a comparison to lay the foundation.

1     defselect_database_struts (self):2         " "3 finds the database structure in the current connection configuration in a dictionary collection4         " "5sql =" "SELECT column_name, is_nullable, Column_type, Column_key, Column_comment6 From information_schema. ' COLUMNS '7 WHERE table_schema= "%s" and Table_name= "{0}"" "% (self.__database)8Struts = {}9          forKinchSelf.__primarykey_dict. Keys ():TenSelf.__cursor. Execute (Sql.format (k)) Oneresults = self.__cursor. Fetchall () ASTRUTS[K] = {} -              forResultinchResults: -Struts[k][result[0]] = {} thestruts[k][result[0]]["column_name"] =Result[0] -struts[k][result[0]]["is_nullable"] = result[1] -struts[k][result[0]]["Column_type"] = result[2] -struts[k][result[0]]["Column_key"] = Result[3] +struts[k][result[0]]["column_comment"] = Result[4] -         returnSelf.__configStruts

View Code

2. Writing a comparison of Python scripts

1 " "2 database Migration scripts that currently support several features:3 1. Generate SQL scripts for database tables that are not available in the old database (with support for table data), and the generated script is in the Temp directory (table name. sql). 4 2. Generate the Add column SQL script, and the generated SQL script is uniformly placed in the depoyed.sql in the temp directory. 5 3. Generate the Modify Column properties SQL script, the generated SQL script is uniformly placed in the depoyed.sql in the temp directory. 6 4. Generate the Delete column SQL script, and the generated SQL script is uniformly placed in the depoyed.sql in the temp directory. 7 " "8 Importjson, OS, sys9  fromBasedaoImportBasedaoTen  OneTemp_path = sys.path[0] +"/temp" A if  notos.path.exists (temp_path): - Os.mkdir (Temp_path) -  the defMain (old, new, has_data=False): -     " " - @old old database (target database) - @new The latest database (source database) + @has_data whether to generate SQL scripts for structure + data -     " " +Clear_temp ()#Clean the temp directory first AOld_config, old_struts = Old atNew_config, new_struts =New -      forNew_table, New_fieldsinchNew_struts.items (): -         ifOld_struts.get (new_table) isNone: -Gc_sql (new_config["User"], new_config["Password"], new_config["Database"], new_table, Has_data) -         Else: - cmp_table (old_struts[new_table], new_struts[new_table], new_table) in  - defcmp_table (old, new, table): to     " " + Compare table structure to generate SQL -     " " theOld_fields = Old *New_fields =New $ Panax NotoginsengSql_add_column ="ALTER table ' {table} ' ADD COLUMN ' {column_name} ' {Column_type} COMMENT ' {column_comment} '; \ n" -Sql_change_column ="ALTER table ' {table} ' change ' {column_name} ' {column_name} ' {Column_type} COMMENT ' {column_comment} '; \ n" theSql_del_column ="ALTER table ' {table} ' DROP {column_name};" +  A     ifOld_fields! =New_fields: thef = open (Sys.path[0] +"/temp/deploy.sql","a", encoding="UTF8") +Content ="" -          forNew_field, New_field_dictinchNew_fields.items (): $Old_filed_dict =old_fields.get (New_field) $             ifOld_filed_dict isNone: -                 #build Add Column SQL -Content + = Sql_add_column.format (table=table, * *new_field_dict) the             Else: -                 #generate modify Column SQLWuyi                 ifOld_filed_dict! =new_field_dict: theContent + = Sql_change_column.format (table=table, * *new_field_dict) -                 Pass Wu         #Generate delete Column SQL -          forOld_field, Old_field_dictinchOld_fields.items (): About             ifNew_fields.get (Old_field) isNone: $Content + = Sql_del_column.format (table=table, column_name=Old_field) -                  - f.write (content) - f.close () A  + defgc_sql (user, PWD, db, table, Has_data): the     " " - Generate SQL File $     " " the     ifHas_data: theSys_order ="mysqldump-u%s-p%s%s%s >%s/%s.sql"%(user, PWD, db, table, Temp_path, table) the     Else: theSys_order ="mysqldump-u%s-p%s-d%s%s >%s/%s.sql"%(user, PWD, db, table, Temp_path, table) - Os.system (Sys_order) in  the defclear_temp (): the     " " About call this every time you execute, and first clean up the old files under the temp directory. the     " " the     ifos.path.exists (temp_path): theFiles =Os.listdir (Temp_path) +          forFileinchFiles: -f =os.path.join (temp_path, file) the             ifOs.path.isfile (f):Bayi Os.remove (f) the     Print("Temporary file directory cleanup complete") the  - if __name__=="__main__": -Test1_config = { the         "User":"Root",  the         "Password":"Root", the         "Database":"test1",  the     } -Test2_config = { the         "User":"Root",  the         "Password":"Root", the         "Database":"test2", 94     } the      theTest1_dao = Basedao (* *test1_config) theTest1_struts =test1_dao.select_database_struts ()98      AboutTest2_dao = Basedao (* *test2_config) -Test2_struts =test2_dao.select_database_struts ()101 102Main (test2_struts, test1_struts)

View Code

Only 4 types of SQL scripts are currently supported for generation.

If you are interested in learning together, discuss Python can add QQ group: 626787819, what comments or suggestions can send me e-mail: [email protected].

Python implementation of database update script generation