Python擷取伺服器檔案內容寫入SqlServer

標籤：etc   add   txt   res   欄位   __name__   try   cep   val   

# -*- coding: utf-8 -*-import pymssqlimport sshimport datetimeimport loggingimport osclass SQLServer:    def __init__(self, host, user, pwd, db):        self.host = host        self.user = user        self.pwd = pwd        self.db = db    def __GetConnect(self):        if not self.db:            raise (NameError, "沒有設定資料庫連接資訊")        self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db)        cur = self.conn.cursor()        if not cur:            raise (NameError, "資料庫連接失敗")        else:            return cur    def ExecQuery(self, sql):        cur = self.__GetConnect()        cur.execute(sql)        resList = cur.fetchall()        self.conn.close()        return resList    def ExecNonQuery(self, sql):        cur = self.__GetConnect()        cur.execute(sql)        self.conn.commit()        self.conn.close()def main():    try:        daytime=datetime.datetime.now()-datetime.timedelta(days=1)        times=daytime.strftime("%Y%m%d")        mscon = SQLServer(host="***", user="***", pwd="***", db="***")        client = ssh.SSHClient()        client.set_missing_host_key_policy(ssh.AutoAddPolicy())        client.connect("***", port=22, username="***", password="***")        stdin, stdout, stderr = client.exec_command(‘cat /app/mount/session_csv/UserSession_‘+times+‘.csv‘)        str = stdout.read()        fa = open(‘aa.txt‘, ‘w+‘)        fa.write(str)        fa.close()        file = open("aa.txt")        while 1:            line = file.readline()            line = line.replace(",","‘,‘")            line = line[:-2]            if not line:                break            sql = "INSERT INTO dbo.表名(欄位1,欄位2) VALUES(‘" + line + "‘)"            mscon.ExecNonQuery(sql)        file.close()    except Exception,e:        print(e)if __name__ == ‘__main__‘:    main()    logging.basicConfig(filename=os.path.join(os.getcwd(), ‘log.txt‘), level=logging.DEBUG)    logging.debug(‘運行完成‘)

　　

Python擷取伺服器檔案內容寫入SqlServer