python 手动同步mysql表结构脚本

来源：互联网发布：php base64解码成图片编辑：程序博客网时间：2024/06/05 10:24

经常研发忘记提交数据库更新脚本，写了手动脚本同步，输入表名和列名即可把研发新增的列同步到测试库

#!/usr/bin/env python# -*- coding: utf-8 -*-import MySQLdbclass Addcolumn():    #从研发环境查询列的类型和长度    def querycolumn(self, colnum, tablename,dbname):        queryc = MySQLdb.connect(            host='192.168.20.151',            port=3306,            user='root',            passwd='111111',            db='information_schema',        )        curl = queryc.cursor()        curl.execute("SELECT COLUMN_TYPE from columns where table_name="+"'"+tablename+"'"+"and COLUMN_NAME=" +"'"+ colnum +"'"+ "and TABLE_SCHEMA="+"'"+dbname+"'")        result = curl.fetchone();        return result    #拼接生成执行的SQL语句    def addc(self):        tablename = raw_input("Enter tablename: ")        column = raw_input("Enter column: ")        result=self.querycolumn(column, tablename, 'zlax_business')        type = result[0]        #length = result[1]        #length=bytes(length)        type = bytes(type)        #print 'ALTER TABLE '+tablename+' ADD COLUMN '+column+' '+type+';'        return 'ALTER TABLE '+tablename+' ADD COLUMN '+column+' '+type+';'    #执行更新语句到测试库    def conn(self,sqlstr):        conn = MySQLdb.connect(            host='192.168.20.155',            port=3306,            user='test',            passwd='test123',            db='zlax_test',        )        cur = conn.cursor()        cur.execute(sqlstr)    #写SQL更新语句到文件中    def writelog(self,filename):        logconent=self.addc()        file_object = open(filename, 'a')        file_object.write(logconent)        file_object.close()        return logconentif __name__=="__main__":    a=Addcolumn()    sqlstr = a.writelog('E://update_sql0310..txt')    a.conn(sqlstr)

1 0