MysqldbHelper and savedb and SVNCommand

#-*-coding: utf-8 -*-
'''
    __auther__ = martinawang
    数据库操作
'''
import pymysql
 
class MysqldbHelper:
    # def getMysql(self):
    #     return MysqldbHelper()
    #获取数据库连接
    def __getCon(self):
        try:
            conn = pymysql.connect(host='127.0.0.1', user='root', password='root@123', db='db_oa',charset="utf8")
            return conn
        except Exception as e:
            print("Mysqldb Error:%s",e)
    #查询方法，返回结果为字典    
    def select(self,sql):
        try:
            con=self.__getCon()
            cur = con.cursor()
            cur.execute(sql)
            fc=cur.fetchall()
            return fc
        except Exception as e:
            print("Mysqldb Error:%s",e)
        finally:
            cur.close()
            con.close()
    #带参数的更新方法,eg:sql='insert into pythontest values(%s,%s,%s,now()',params=(6,'C#','good book')
    def updateByParam(self,sql,params):
        try:
            con=self.__getCon()
            cur=con.cursor()
            count=cur.execute(sql,params)
            con.commit()
            return count
        except Exception as e:
            con.rollback()
            print("Mysqldb Error:%s",e)
        finally:
            cur.close()
            con.close()
    #不带参数的更新方法
    def update(self,sql):
        try:
            con=self.__getCon()
            cur=con.cursor()
            count=cur.execute(sql)
            con.commit()
            return count
        except Exception as e:
            con.rollback()
            print("Mysqldb Error:%s",e)
        finally:
            cur.close()
            con.close()
    
    #更新多行数据
    def updateRaws(self,sql,raws):
      
        con=self.__getCon()
        cur=con.cursor()
        try:
            for raw in raws:
                cur.execute(sql,raw)
                con.commit()
        except Exception as e:
            con.rollback()
            print("Mysqldb Error:%s",e)
        finally:
            cur.close()
            con.close()

     #被外键关联的表的更新方法
    def updateForeignKey(self,sql1,sql2,sql3):
        try:
            con=self.__getCon()
            cur=con.cursor()
            cur.execute(sql1)
            con.commit()
            cur.execute(sql2)
            con.commit()
            count = cur.execute(sql3)
            con.commit()
            print(count)
        except Exception as e:
            con.rollback()
            print("Mysqldb Error:%s",e)
        finally:
            cur.close()
            con.close()
    #带外键的中间表的更新
    def updateForeignKeyInter(self,sql1,sql2,raws,sql3):
        try:
            con=self.__getCon()
            cur=con.cursor()
            cur.execute(sql1)
            con.commit()
            for raw in raws:
                cur.execute(sql2,raw)
                con.commit()
            count = cur.execute(sql3)
            con.commit()
            print(count)
        except Exception as e:
            con.rollback()
            print("Mysqldb Error:%s",e)
        finally:
            cur.close()
            con.close()

 

 

'''
    __auther__ = martinawang
    存入数据库
'''
import MysqldbHelper
import readExcel

class saveDB:
    
    def __init__(self):
        self.readexc = readExcel.readExcel()
        self.mysqldb = MysqldbHelper.MysqldbHelper()

        
       
    def saveProps(self):
        
        truncate1 = self.mysqldb.update(''' truncate table oa_prop_channel_table''')
        print("truncate: "+str(truncate1))

        truncate2 = self.mysqldb.updateForeignKey(''' SET FOREIGN_KEY_CHECKS = 0''','''truncate table oa_prop_table ''',''' SET FOREIGN_KEY_CHECKS = 1 ''')
        print("truncate: "+str(truncate2))

        
        prop = self.readexc.read_prop_xls_file()
        hero = self.readexc.read_hero_xls_file()
        micro = self.readexc.read_microcosm_xls_file()
        other = self.readexc.read_other_prop()
        #切片合并list
        prop[len(prop):len(prop)] = hero
        prop[len(prop):len(prop)] = micro
        prop[len(prop):len(prop)] = other

        #props插入数据
        sql3 = '''
        insert into oa_prop_table(prop_id,prop_name,prop_typeid,prop_typename,prop_desc,type)
        values(%s,%s,%s,%s,%s,%s);
       '''
        self.mysqldb.updateRaws(sql3,prop)
    def saveChannels(self):
        truncate1 = self.mysqldb.update(''' truncate table oa_prop_channel_table''')
        print("truncate: "+str(truncate1))

        truncate3 = self.mysqldb.updateForeignKey(''' SET FOREIGN_KEY_CHECKS = 0''','''truncate table oa_channel_table ''',''' SET FOREIGN_KEY_CHECKS = 1 ''')
        print("truncate: "+str(truncate3))
         
        
        channel = self.readexc.read_channel_xls_file()
        other = self.readexc.read_other_channel()

        channel[len(channel):len(channel)] = other

        #channels插入数据
        sql4 = '''insert into oa_channel_table(channel_name)
        values(%s);
       '''
        self.mysqldb.updateRaws(sql4,channel)
    
    #第三张表
    def savePC(self):

        pcList = self.readexc.read_pc()
        sql5 = '''
        insert into oa_prop_channel_table(oachannel_id,index_id,number)
        values(%s,%s,%s);
       '''
        self.mysqldb.updateForeignKeyInter(''' SET FOREIGN_KEY_CHECKS = 0''',sql5,pcList,''' SET FOREIGN_KEY_CHECKS = 1 ''')




        

 

 

# -*- coding:utf-8 -*-
import subprocess
import json
import os
 
class SVNCommands(object):
    userName = 'username'
    password = 'password'
    def __init__(self,dirPath='',fileName='',ciMessage='',svnServerUrl=''):
        self.__dirPath = dirPath
        if(len(dirPath.strip())):
            self.setLocalPath(dirPath)
        self.__fileName = fileName
        if(len(fileName.strip())):
            self.setFileName(fileName)
        self.__svnUrl = svnServerUrl
        if(len(svnServerUrl.strip())):
            self.setSVNUrl(svnServerUrl)
        self.__svnCmd = ''
        self.__ciMessage = ciMessage
        if(len(ciMessage.strip())):
            self.setCiMessage(ciMessage)
        self.__outInfo = 'ss'
        self.__errInfo = 'ss'
    def setLocalPath(self,path):
        '''
        path: local directory path string
        '''
        path = path.replace('\\','/').strip()
        if(os.path.isdir(path)):
            if(path.endswith('/') == False):
                path = path + '/'
            self.__dirPath = path
        else:
            raise TypeError('It must be a real directory')
    def getLocalPath(self):
        return self.__dirPath
    def setFileName(self,file):
        '''
        file whick you want to up,delete,commite,contains the extension
        '''
        self.__fileName = file.strip()
 
    def setSVNUrl(self,url):
        '''
        the url in SVN server
        '''
        self.__svnUrl = url.strip()
        
    def setCiMessage(self,msg):
        if(isinstance(msg,str)):
            self.__ciMessage = msg.strip()
        else:
            raise TypeError('It need string type')
    
    def svnCheckout(self):
        if(len(self.__dirPath) and len(self.__svnUrl)):
            self.__svnCmd = 'svn checkout ' + self.__svnUrl + ' ' + self.__dirPath + ' --username ' + self.userName + ' --password ' + self.password
            self.displayCmd()  # display
            self.execCmd()
        else:
            raise ValueError('Please check the SVN url and local path')
    def svnUpdate(self):
        if(len(self.__dirPath)):
            self.__svnCmd = 'svn up ' + self.__dirPath
            self.displayCmd()  # display
            self.execCmd()
        else:
            raise ValueError('Please check local path')
    def svnAdd(self):
        if(os.path.isfile(self.__dirPath + self.__fileName)):
            self.__svnCmd = 'svn add '+ self.__dirPath + self.__fileName
            self.displayCmd()  # display
            self.execCmd()
        else:
            raise ValueError('Please check you file')
    def svnDel(self):
        if(os.path.isfile(self.__dirPath + self.__fileName)):
            self.__svnCmd = 'svn delete ' + self.__dirPath + self.__fileName
            self.displayCmd()  # display
            self.execCmd()
        else:
            raise ValueError('Please check you file')
    def svnCommit(self):
        if(os.path.isfile(self.__dirPath + self.__fileName)):
            self.__svnCmd = 'svn ci -m "' + self.__ciMessage + '" ' + self.__dirPath + self.__fileName
            self.displayCmd()  # display
            self.execCmd()
        elif(os.path.isdir(self.__dirPath)):
            self.__svnCmd = 'svn ci -m "' + self.__ciMessage + '" ' + self.__dirPath
            self.displayCmd()  # display
            self.execCmd()
        else:
            raise ValueError('Please check you file')
    def svnCleanup(self):
        if(len(self.__dirPath)):
            self.__svnCmd = 'svn cleanup ' + self.__dirPath
            self.displayCmd()  # display
            self.execCmd()
        else:
           raise ValueError('Please check local path') 
    def execCmd(self):
        p = subprocess.Popen(self.__svnCmd)
        try:
            self.__outInfo,self.__errInfo = p.communicate(timeout = 20)
        except subprocess.TimeoutExpired:
            p.kill()
            self.__outInfo,self.__errInfo = p.communicate()
    def displayInfo(self):
        print({'Revision':self.__outInfo,'svnErr':self.__errInfo})
    def displayCmd(self):
        print('Command is '+ self.__svnCmd)
 
if __name__ == '__main__':
    '''
    svn = SVNCommands('d:\\tmp\\svn_test1')
    svn.setSVNUrl('https://xxxxxxxxx')
    svn.svnCheckout()
    '''
    #exit()
    svn = SVNCommands()
    svn.setLocalPath('d:\\tmp\\svn_test1')
    svn.setFileName('ab.txt')
    svn.svnAdd()
    svn.setCiMessage('+ add this file')
    svn.svnCommit()
    #svn.displayInfo()
 
    exit()