Python build script--Implement database update

Source: Internet
Author: User
When I was at work, The database used in the test environment is inconsistent with the database of the production environment, when the database in our test environment is ready to be updated to the database in the production environment, we need to prepare the update script, it is very careful not to remember to forget to change where, where added what, this is really very frustrating. 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 def select_database_struts (self): 2 "3 Find the database structure in the current connection configuration with a dictionary collection 4" "5 sql =" SEL  ECT column_name, Is_nullable, Column_type, Column_key, column_comment 6 from information_schema. ' COLUMNS ' 7 WHERE table_schema= "%s" and Table_name= "{0}" ""% (self.__database) 8 struts = {} 9 for K in Self.__primarykey_dict.keys (): Self.__cursor.execute (Sql.format (k)) results = Self.__curs Or.fetchall () struts[k] = {}13 for result in results:14 struts[k][result[0]] = { }15 struts[k][result[0]]["column_name"] = result[0]16 struts[k][result[0]]["is_nullable"] = result[1]17 struts[k][result[0]]["Column_type"] = result[2]18 struts[k][result[0]]["COLU Mn_key "] = result[3]19 struts[k][result[0]][" column_comment "] = result[4]20 return self.__config, Struts
View Code

2. Writing a comparison of Python scripts

  1 "2 Database Migration script, currently supports several functions: 3 1. Generate database tables that are not in the old database execute SQL scripts (with or without table data), and generate SQL scripts under the TEMP directory (table name. sql).  4 2. Generate the Add column SQL script, and the generated SQL scripts are uniformly placed in the depoyed.sql in the temp directory.  5 3. Generate the Modify Column properties SQL script, and the generated SQL scripts are 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 Import JSON, OS, sys 9 from Basedao import Basedao Temp_path = sys.path[0] +"/temp "for if not Os.path.ex Ists (Temp_path): Os.mkdir (Temp_path)-def main (old, New, Has_data=false): "" @old Legacy database (target database) @new the latest database (source database) @has_data whether to generate the structure + data SQL Script "Clear_temp () # Clean the temp directory first old_conf         IG, old_struts = Old new_config, new_struts = new to New_table, New_fields in New_struts.items (): 25 If Old_struts.get (new_table) is none:26 gc_sql (new_config["user"], new_config["password"], new_config["data Base "], new_table, Has_data) else:28 cmp_table (old_struts[new_table], New_struts[new_tabLe], new_table)-def cmp_table (old, new, table): 31 "32 comparison table structure generated SQL" Old_fields = Old 35 New_fields = new PNs Sql_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_ty PE} COMMENT ' {column_comment} '; \ n "sql_del_column =" ALTER TABLE ' {table} ' DROP {column_name}; "         If old_fields! = new_fields:42 F = open (Sys.path[0] + "/temp/deploy.sql", "a", encoding= "UTF8") 43 Content = "" New_field, New_field_dict in New_fields.items (): Old_filed_dict = Old_fiel Ds.get (New_field) If Old_filed_dict is none:47 # Build Add column SQL Content + = s                 Ql_add_column.format (table=table, **new_field_dict) ELSE:50 # Build Modify Column SQL 51   If old_filed_dict! = new_field_dict:52                  Content + = Sql_change_column.format (table=table, **new_field_dict) 54 # Generate Delete Column SQL Old_field, old_field_dict in Old_fields.items (): If New_fields.get (Old_field) is N         one:57 content + = Sql_del_column.format (table=table, Column_name=old_field) 58 59     F.write (content) F.close () gc_sql (user, PWD, db, table, Has_data): 63 "64 Generate SQL File 65 "" If has_data:67 Sys_order = "mysqldump-u%s-p%s%s%s >%s/%s.sql"% (user, PWD, db, table, Temp_ Path, table) else:69 Sys_order = "mysqldump-u%s-p%s-d%s%s >%s/%s.sql"% (user, PWD, db, table, temp _path, table) Os.system (Sys_order), Clear_temp (): 73 "74 each time the execution of the call this, first clean down the temp directory under the old file 75 '             "If Os.path.exists (temp_path): files:79 files = Os.listdir (Temp_path). f = os.path.join (temP_path, file) if Os.path.isfile (f): Bayi Os.remove (f) ("Temporary file directory cleanup completed") name__ = = "__main__": Test1_config = {"User": "root", "password": "Root", "the" Dat         Abase ":" Test1 "," password ", Test2_config = {" User ":" Root "," ":" Root ", 93 "Database": "Test2", 94} Test1_dao = Basedao (**test1_config) test1_struts = Test1_dao.selec T_database_struts () 98 Test2_dao = Basedao (**test2_config) test2_struts = Test2_dao.select_database_str UTS () 101 102 main (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: 410093793@qq.com.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.