Python generates database update scripts and python scripts

Python generates database update scripts and python scripts

When I was working, the databases used in the test environment were inconsistent with those in the production environment, when the database in the test environment completes the test preparation and updates to the database in the production environment, we need to prepare the update script. If we accidentally did not remember it, we will forget where to change it, this is a headache. So I tried to use Python to automatically generate an update script, so that I could not remember it.

The main operations are as follows:

1. Add the following method to the original basedao. py, so that you can easily obtain the database data and lay a foundation for comparison between the test database and the production database.

1 def select_database_struts (self): 2 ''' 3 find the database structure in the current connection configuration and use the dictionary set 4''' 5 SQL = ''' SELECT 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 (): 10 self.w.cursor.exe cute (SQL. format (k) 11 results = self. _ cursor. fetchall () 12 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] ["COLUMN_KEY"] = result [3] 19 struts [k] [result [0] ["COLUMN_COMMENT"] = result [4] 20 return self. _ config, struts

View Code

2. Compile a Python script for comparison.

1 ''' 2 database migration script. Currently, the following functions are supported: 3 1. generate database tables that are not in the old database and execute SQL scripts (whether table data is supported). The generated SQL script is in the temp directory (table name. SQL ). 4 2. Generate the SQL script for adding columns, and place the generated SQL script in depoyed. SQL under the temp directory. 5. generate an SQL script for modifying column attributes. The generated SQL script is stored in depoyed. SQL under the temp directory. 4. generate an SQL script for deleting columns. All generated SQL scripts are stored in depoyed. SQL under the temp directory. 7 ''' 8 import json, OS, sys 9 from basedao import BaseDao 10 11 temp_path = sys. path [0] + "/temp" 12 if not OS. path. exists (temp_path): 13 OS. mkdir (temp_path) 14 15 def main (old, new, has_data = False): 16 '''17 @ old database (target database) 18 @ new latest database (source database) 19 @ has_data whether to generate structure + Data SQL script 20''' 21 clear_temp () # first clear the temp directory 22 old_config, old_struts = old 23 new_config, new_struts = new 24 for 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 ["database"], new_table, has_data) 27 else: 28 cmp_table (old_struts [new_table], new_struts [new_table], new_table) 29 30 def cmp_table (old, new, table ): 31 ''' 32 compare TABLE structure generation SQL 33 ''' 34 old_fields = old 35 new_fields = new 36 37 SQL _add_column = "ALTER table' {TABLE} 'Add column' {COLUMN_NAME} '{COLUMN_TYPE} COMMENT' {COLUMN_COMMENT }'; \ n "38 SQL _change_column =" ALTER TABLE '{TABLE} 'Chang' {COLUMN_NAME} ''{COLUMN_NAME}' {COLUMN_TYPE} COMMENT '{COLUMN_COMMENT }'; \ n "39 SQL _del_column =" ALTER TABLE '{TABLE}' DROP {COLUMN_NAME}; "40 41 if old_fields! = New_fields: 42 f = open (sys. path [0] + "/temp/deploy. SQL "," a ", encoding =" utf8 ") 43 content =" "44 for new_field, new_field_dict in new_fields.items (): 45 old_filed_dict = old_fields.get (new_field) 46 if old_filed_dict is None: 47 # generate SQL 48 content + = SQL _add_column.format (TABLE = table, ** new_field_dict) 49 else: 50 # generate SQL 51 if old_filed_dict! = New_field_dict: 52 content + = SQL _change_column.format (TABLE = table, ** new_field_dict) 53 pass 54 # generate and delete column SQL 55 for old_field, old_field_dict in old_fields.items (): 56 if new_fields.get (old_field) is None: 57 content + = SQL _del_column.format (TABLE = table, COLUMN_NAME = old_field) 58 59 f. write (content) 60 f. close () 61 62 def gc_ SQL (user, pwd, db, table, has_data): 63 ''' 64 generate the SQL file 65 ''' 66 if has_data: 67 sys_order = "mysqldump-u % s-p % s> % s/% s. SQL "% (user, pwd, db, table, temp_path, table) 68 else: 69 sys_order = "mysqldump-u % s-p % s-d % s> % s/% s. SQL "% (user, pwd, db, table, temp_path, table) 70 OS. system (sys_order) 71 72 def clear_temp (): 73 ''' 74 call this operation each time. First, clear the old file 75''' 76 if OS In the temp directory. path. exists (temp_path): 77 files = OS. listdir (temp_path) 78 for file in files: 79 f = OS. path. join (temp_path, file) 80 if OS. path. isfile (f): 81 OS. remove (f) 82 print ("temporary file directory cleared") 83 84 if _ name _ = "_ main __": 85 testbench Config = {86 "user": "root", 87 "password": "root", 88 "database": "test1 ", 89} 90 test2_config = {91 "user": "root", 92 "password": "root", 93 "database": "test2 ", 94} 95 96 testbench Dao = BaseDao (** testbench config) 97 testbench struts = Clerk () 98 99 test2_dao = BaseDao (** test2_config) 100 test2_struts = Clerk () 101 102 main (test2_struts, testbench struts)

View Code

Currently, only four types of SQL scripts are supported.

If you are interested in learning together, discuss Python can add QQ group: 626787819, have any opinions or suggestions can send me mail: 410093793@qq.com.