网站外包后呗百度降权,甘肃网站建设选哪家,禹城网站建设电话,儋州网站建设培训学校一、需求描述
1、从Mysql数据库表下载数据到服务器#xff1b; 2、将数据已csv文件格式存储并对数据格式进行处理#xff08;添加表头#xff0c;表头和数据均用竖线分隔符隔开#xff0c;末尾也加分割符#xff09;#xff1b; 3、文件路径文件夹以天为单位#xff0c…一、需求描述
1、从Mysql数据库表下载数据到服务器 2、将数据已csv文件格式存储并对数据格式进行处理添加表头表头和数据均用竖线分隔符隔开末尾也加分割符 3、文件路径文件夹以天为单位文件名中含日期和序号序号记录相同文件在同一天重新下载传送的批次 3、将文件压缩成.gz格式 4、文件以Sftp方式传送到对方服务器固定位置。
二、Python 全代码
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import paramiko,csv
import os, sys, stat
import pymysql
import time, gzipdef create_dir(sftp, sftpRemoteDir):try:if stat.S_ISDIR(sftp.stat(sftpRemoteDir).st_mode):passexcept Exception as e:sftp.mkdir(sftpRemoteDir)def sftp_upload(sftp, sftpLocalDir, sftpRemoteDir):if os.path.isdir(sftpLocalDir):for file in os.listdir(sftpLocalDir):remoteDirTmp os.path.join(sftpRemoteDir, file)localDirTmp os.path.join(sftpLocalDir, file)if os.path.isdir(localDirTmp):create_dir(sftp, remoteDirTmp)sftp_upload(sftp, localDirTmp, remoteDirTmp)else:print(upload file:, sftpLocalDir)try:sftp.put(sftpLocalDir, sftpRemoteDir)except Exception as e:print(upload error:, e)if __name__ __main__:# 变量date time.strftime(%Y%m%d, time.localtime())sftpHost 192.168.220.104sftpPort 22sftpUser sftpsunsftpPassord hadoophadoopsftpLocalDir /home/hadoop/python_file/ datesftpRemoteDir /uploadxhDir /home/hadoop/xh/ datexhFile xhDir / xhFile.txt# 序号if os.path.isdir(xhDir):if os.path.isfile(xhFile):with open(xhFile, r, encodingutf-8) as f:xh f.readline()xh int(xh) 1xh str(xh).zfill(2)with open(xhFile, w, encodingutf-8) as f:f.write(xh)else:with open(xhFile, w, encodingutf-8) as f:f.write(00)else:os.makedirs(xhDir)with open(xhFile, w, encodingutf-8) as f:f.write(00)with open(xhFile, r, encodingutf-8) as f:xh f.readline()sendFile ZXSEND_0112_082_ date _ xh _001sftpLocalFile sftpLocalDir / sendFile# 目录创建if os.path.isdir(sftpLocalDir):passelse:os.makedirs(sftpLocalDir)# 数据下载conn pymysql.connect(hosthadoop100,port3306,userroot,passwordMysql123456#,databaseflink_sql,charsetutf8)cursor conn.cursor()sql select t.* from hot_item tcursor.execute(sql)data cursor.fetchall()print(data)cursor.close()# 数据格式处理写入本地文件header (w_end, item_id, item_count, rk)with open(sftpLocalFile .csv, w, newline, encodingutf-8) as f:lines csv.writer(f,delimiter|)lines.writerow(header)for line in data:a list(line)lines.writerow(line)f2 open(sftpLocalFile .dat, w,encodingutf-8)with open(sftpLocalFile .csv, r) as f:for line in f:line line.strip()if not line.endswith(r|):line r|line \nf2.write(line)f2.close()os.remove(sftpLocalFile .csv)# 压缩with open(sftpLocalFile .dat, rb) as f:data f.read()with gzip.open(sftpLocalFile .dat .gz, wb) as f:f.write(data)os.remove(sftpLocalFile .dat)# 文件上传sf paramiko.Transport((sftpHost, sftpPort))sf.connect(usernamesftpUser, passwordsftpPassord)sftp paramiko.SFTPClient.from_transport(sf)sftp_upload(sftp, sftpLocalDir, sftpRemoteDir)sf.close()
