20230908_python练习_服务端与客户端数据交互

	用户可以通过简单操作进行服务端数据交互,通过简单的sql语句直接获取EXCEL表,可以用来作为交互的基础。

20230910更改输入sql语句时多行组合。

#语句存储
sql_text = ''
# 循环执行
while True:
    sql = input('请您输入需要发送的语句:').replace("'","^")
    try:
        sql_out = sql.index(';')
    except:
        sql_out = -1
    if sql_out == -1:
        sql_text = sql_text + ' ' + sql
    else:
        sql_text = sql_text + ' ' + sql
        break
print('sql_text', sql_text.replace(";", " "))

主要涉及三部分:

1:数据库存储表结构
在这里插入图片描述

--日志记录表结构
create table shzc.yytowz_service_title
(leixing  varchar2(18),ziduan1  varchar2(3000),ziduan2  varchar2(300),ziduan3  varchar2(300),ip_id  varchar2(18),
post_id  varchar2(18),in_time varchar2(30),mac varchar2(20),hostname varchar2(100),ip varchar2(30) );

2:服务端维持代码
在这里插入图片描述

# 服务端
import socketserver
import json
import os
#import pymysql
import cx_Oracle #Oracle 数据库连接
import time
import tqdm
import pandas as pd

class MyServer(socketserver.BaseRequestHandler):

    def handle(self):
        self.add_ip = self.client_address[0]
        self.add_post = str(self.client_address[1])

        while True:
            try:
                data = self.request.recv(102400)
                #如果获取为空就退出
                if not data:break
                #否则解码处理数据
                self.data = json.loads(data.decode('utf-8'))
                # data 是获取字典内容,self.client_address 是 ip地址与 端口
                print('客户端的消息:',self.data,self.client_address)
                #数据库对访问记录存档
                self.log_record()
                # 定义处理规则
                self.visit_response()
                #将结果反馈给客户端
                self.request.sendall(self.fankui.encode('utf-8'))
            except :
                #print('连接异常')
                break


    #定义处理规则
    def visit_response(self):
        #获取时间
        in_time = self.get_current_time()[0:14]

        if self.data['leixing'] == '文件传递':
            try:
                file_download_statr = self.file_download()
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, file_download_statr)
            except:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, '传递失败')

        elif self.data['leixing'] == '文件下载': 
            # 获取sql语句生成表格
            download_start, file_path, file_name, file_size = Oracle_download(self.data['mac'], in_time,str(self.data['ziduan1']).replace("^","'"))
            print('download_start', download_start, '大小字节', file_size, '文件名', file_name)
            try:
                # 将表格返回客户端
                file_download_statr = self.file_up(file_path,self.data['ziduan3'])
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], file_path, file_size, file_download_statr)
            except:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], file_path, file_size, '传递失败')

        elif self.data['leixing'] == '发起访问':
            try:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, '访问成功')
            except:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, '访问失败')

        else:
            try:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, '访问成功')
            except:
                self.fankui = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                self.data['leixing'], self.add_ip, self.add_post, '访问失败')

    #文件下载函数
    def file_download(self):
        in_time = self.get_current_time()[0:14]
        file_path = self.data['ziduan1']
        file_size = self.data['ziduan2']
        file_hz = file_path.split('/')[-1]
        print('接收文件名:',file_hz,' 接收文件大小:',file_size,' 字节')
        # 文件传输的缓冲区
        BUFFER_SIZE = 4096
        # 接受客户端信息
        filename, file_size, new_filename = self.data['ziduan1'], self.data['ziduan2'], str(self.data['ziduan3'])[0:6]
        #判断月文件夹是否存在,不存在创建一个
        file_path_state1 = os.path.exists('./file_server/'+new_filename)
        if file_path_state1 == False:
            os.mkdir('./file_server/'+new_filename)
        # 获取文件的名字
        filename = os.path.basename(filename)
        file_size = int(file_size)
        if os.path.isfile(filename):
            f = open('./file_server/'+new_filename +'/' + self.data['mac'] + '_' + in_time + '_' + file_hz, "wb")
        else:
            f = open('./file_server/'+new_filename +'/' + self.data['mac'] + '_' + in_time + '_' + file_hz, "wb")
        rece_size = 0
        while rece_size < file_size:
            data = self.request.recv(BUFFER_SIZE)
            f.write(data)
            rece_size += len(data)
        else:
            return '传递成功'

    def file_up(self,fujian_label,in_time):
        # 文件传输的缓冲区
        BUFFER_SIZE = 4096
        # 传递文件到指定目录下
        filename = fujian_label.replace('/', '//')
        # 文件大小
        file_size = os.path.getsize(filename)
        # 创建连接
        chuandi_tup = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%d","ziduan3":"%s" }' % (
            '文件提取', filename, file_size, in_time)
        self.request.sendall(chuandi_tup.encode('utf-8'))
        # 文件传输
        progress = tqdm.tqdm(range(file_size), f"发送{filename}", unit="B", unit_divisor=1024)

        with open(filename, "rb") as f:
            for _ in progress:
                # 读取文件
                bytes_read = f.read(BUFFER_SIZE)
                if not bytes_read:
                    break
                # sendall确保及时网络忙碌的时候,数据仍然可以传输
                self.request.sendall(bytes_read)
                progress.update(len(bytes_read))
        # 关闭资源
        self.request.close()

    #数据库登录
    def mysql_execute(self, in_sql, leixing):
        # 登录数据库
        dsn = "134.80.200.216/xxx"
        try:
            conn = cx_Oracle.connect(user="zbweb", password="zibo_xxx", dsn=dsn, encoding="UTF-8")
        except:
            time.sleep(10)
            conn = cx_Oracle.connect(user="zbweb", password="zibo_xxx", dsn=dsn, encoding="UTF-8")

        # 得到一个可以执行SQL语句的光标对象
        cursor = conn.cursor()
        # 数据库执行导入的语句
        if leixing == '数量':
            # 反馈数量
            count = cursor.execute(in_sql)
        elif leixing == '单条':
            # 反馈单条
            cursor.execute(in_sql)
            count = cursor.fetchone()[0]
        elif leixing == '多条':
            # 反馈多条
            cursor.execute(in_sql)
            count = cursor.fetchall()
        elif leixing == '编辑':
            count = cursor.execute(in_sql)
            conn.commit()
        # 关闭光标对象
        cursor.close()
        # 关闭数据库连接
        conn.close()
        # 反馈
        return count

    # 时间计算
    def get_current_time(self):
        ct = time.time()
        local_time = time.localtime(ct)
        data_head = time.strftime("%Y%m%d%H%M%S", local_time)
        data_secs = abs(ct - round(ct)) * 1000
        time_stamp = "%s%03d" % (data_head, data_secs)
        return time_stamp

    #日志留存
    def log_record(self):
        in_time = self.get_current_time()[0:14]
        ziduan1 = str(self.data['ziduan1']).replace("'","^")
        sql = "insert into shzc.yytowz_service_title (leixing,ziduan1,ziduan2,ziduan3,ip_id,post_id,in_time,mac,hostname,ip) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') " % (
            self.data['leixing'], ziduan1, self.data['ziduan2'], self.data['ziduan3'], str(self.client_address[0]),str(self.client_address[1]),in_time, self.data['mac'], self.data['hostname'], self.data['ip'])
        num = self.mysql_execute(sql,'编辑')
        #print('num',num)
        return num

# 时间计算
def get_current_time(input_date='0'):
    # 如果时间传入为空
    if input_date == '0':
        ct = time.time() # - 24 * 60 * 60  #如果是取昨天日期是减数值
        local_time = time.localtime(ct)
        data_head = time.strftime("%Y%m%d%H%M%S", local_time)
        data_secs = abs(ct - round(ct)) * 1000
        time_stamp = "%s%03d" % (data_head, data_secs)
    else:
        time_stamp = input_date + '120000001'
    return time_stamp

def file_transfer(user,file_name):
    #获取月份
    in_month = get_current_time()[0:6]
    # 文件传递给服务器
    file_path = file_name
    file_statr = user.file_up(file_path, in_month)
    # 如果执行结果不成功,再次执行一次,保底
    if file_statr['ziduan3'] != '传递成功':
        file_statr = file_transfer(user,file_name)
        return file_statr
    else:
        return file_statr

#这里用作程序预备,目前建立必要的文件夹
def server_init():
    # 程序执行前先确认 ./file_server/ 是否存在,不存在新建
    file_path_state1 = os.path.exists('./file_server')
    if file_path_state1 == False:os.mkdir('./file_server')
    file_path_state1 = os.path.exists('./file_server/file_out')
    if file_path_state1 == False: os.mkdir('./file_server/file_out')

#文件数据生成,分mac与时间,不然没法同步下载
def Oracle_download(mac,in_time,sql='0'):
    # 结果数据生成表格准备发送
    dsn = "134.80.200.216/pdbzbjs1"
    conn = cx_Oracle.connect(user="zbweb", password="zibo_533_03", dsn=dsn, encoding="UTF-8")
    df = pd.read_sql("""%s""" % sql, con=conn)
    df.to_excel("./file_server/file_out/"+mac+"_"+in_time+"_结果下载.xlsx", index=False)
    # 文件大小
    file_path = './file_server/file_out/'+mac+'_'+in_time+'_结果下载.xlsx'
    file_name = file_path.split('/')[-1]
    file_size = os.path.getsize(file_path)
    #返回根据语句处理结果与
    return "结果下载",file_path,file_name,file_size

if __name__ == '__main__':
    #服务器文件夹准备
    server_init()
    #服务器开始
    s = socketserver.ThreadingTCPServer(('134.35.10.10', 8967), MyServer)
    #类似实现连接循环
    s.serve_forever()

3:客户端代码
在这里插入图片描述

# 客户端
import socket
import json
import time
import os
import tqdm
import uuid #获取系统mac

class My_Main():

    def __init__(self):
        # 程序执行前先确认 ./file_server/file_work_order/ 是否存在,不存在新建
        file_path_state1 = os.path.exists('./file_main')
        if file_path_state1 == False: os.mkdir('./file_main')
        # 定义服务端地址
        self.ip_num, self.port_num = '134.35.10.10', 8967
        # mac
        self.mac = uuid.UUID(int=uuid.getnode()).hex[-12:]
        # 获取主机名
        self.hostname = socket.gethostname()
        # 获取IP
        self.ip = socket.gethostbyname(self.hostname)

    #发起访问调用模块
    def socket_dlgc(self,leixing, name_text, pass_text):
        # 获取14位长度时间,年月日时分秒的
        self.in_time = get_current_time()[0:14]
        tcp_client = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        tcp_client.connect((self.ip_num, self.port_num))
        if None == name_text:print('与服务器断开连接')
        #发送
        msg = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s","mac":"%s","hostname":"%s","ip":"%s" }' % (
            str(leixing), str(name_text), str(pass_text), self.in_time,self.mac, self.hostname, self.ip)
        tcp_client.send(msg.encode("utf-8"))  # 说话    #
        data = tcp_client.recv(102400)  # 听话
        js_data = json.loads(data.decode('utf-8'))
        tcp_client.close()
        return js_data

    # 文件下载函数
    def file_download(self,sql):
        # 获取14位长度时间,年月日时分秒的
        self.in_time = get_current_time()[0:14]
        # 套接字是对访问的ip地址和端口反馈,需要从开始定好
        tcp_client = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        tcp_client.connect((self.ip_num, self.port_num))
        # 申请数据下载
        chuandi_tup = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%d","ziduan3":"%s","mac":"%s","hostname":"%s","ip":"%s" }' % (
            '文件下载', sql, 1, self.in_time+'.xlsx',self.mac, self.hostname, self.ip)
        tcp_client.send(chuandi_tup.encode("utf-8"))  # 说话
        self.data = tcp_client.recv(102400)  # 听话
        js_data = json.loads(self.data.decode('utf-8'))
        #print(js_data, type(js_data))
        if js_data['ziduan1'] not in ('', None):
            file_size = int(js_data['ziduan2'])
            #print(file_size)
            filename = js_data['ziduan3']
            rece_size = 0
            recv_data = tcp_client.recv(4096)
            if recv_data:  # 如果获取数据不为空
                try:
                    with open('./file_main/' + filename, "wb")as f:
                        f.write(recv_data)
                        while rece_size < file_size:
                            recv_data = tcp_client.recv(4096)
                            f.write(recv_data)
                            rece_size += len(self.data)

                    js_data = {'leixing': '文件下载', 'ziduan1': js_data['ziduan3'], 'ziduan2': file_size, 'ziduan3': '传递成功'}
                except:
                    js_data = {'leixing': '文件下载', 'ziduan1': js_data['ziduan3'], 'ziduan2': file_size, 'ziduan3': '传递失败'}
            # 关闭套接字
            tcp_client.close()
        return js_data

    #客户端文件上传,fujian_label 文件全路径
    def file_up(self,fujian_label):
        # 获取14位长度时间,年月日时分秒的
        self.in_time = get_current_time()[0:14]
        # 文件传输的缓冲区
        BUFFER_SIZE = 4096
        # 创建连接
        s = socket.socket()
        s.connect((self.ip_num, self.port_num))
        # 传递文件到指定目录下
        filename = fujian_label.replace('/', '//')
        # 文件大小
        file_size = os.path.getsize(filename)

        chuandi_tup = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%d","ziduan3":"%s","mac":"%s","hostname":"%s","ip":"%s" }' % (
            '文件传递', filename, file_size, self.in_time,self.mac, self.hostname, self.ip )
        s.send(chuandi_tup.encode())
        # 文件传输
        progress = tqdm.tqdm(range(file_size), f"发送{filename}", unit="B", unit_divisor=BUFFER_SIZE)
        with open(filename, "rb") as f:
            for _ in progress:
                # 读取文件
                bytes_read = f.read(BUFFER_SIZE)
                if not bytes_read:
                    break
                try:
                    # sendall确保及时网络忙碌的时候,数据仍然可以传输
                    s.sendall(bytes_read)
                    progress.update(len(bytes_read))
                except:
                    js_data = {'leixing': '文件传递', 'ziduan1': self.ip_num, 'ziduan2': self.port_num, 'ziduan3': '传递失败'}
                    break
        #文件传递完后,看看是否有反馈,有的话函数返回
        try:
            data = s.recv(102400)  # 听话
            js_data = json.loads(data.decode('utf-8'))
        except:
            js_data = {'leixing': '文件传递', 'ziduan1': self.ip_num, 'ziduan2': self.port_num, 'ziduan3': '传递失败'}
        # 关闭资源
        s.close()
        return js_data

# 时间计算
def get_current_time(input_date='0'):
    # 如果时间传入为空
    if input_date == '0':
        ct = time.time() # - 24 * 60 * 60  #如果是取昨天日期是减数值
        local_time = time.localtime(ct)
        data_head = time.strftime("%Y%m%d%H%M%S", local_time)
        data_secs = abs(ct - round(ct)) * 1000
        time_stamp = "%s%03d" % (data_head, data_secs)
    else:
        time_stamp = input_date + '120000001'
    return time_stamp

#文件传递给服务器
def file_transfer(user,file_name,num=0):
    # 防止有语法错误等原因导致死循环,限制最多处理4次
    if num < 5:
        # 文件传递给服务器
        try:
            file_statr = user.file_up(file_name)
            num += 1
        except:
            file_statr = {'leixing': '文件传递', 'ziduan1': file_name, 'ziduan2': '0', 'ziduan3': '传递失败'}
            num += 1
        # 如果执行结果不成功,再次执行一次,保底
        if file_statr['ziduan3'] != '传递成功':
            file_statr = file_transfer(user,file_name,num)
            return file_statr
        else:
            return file_statr
    else:
        file_statr = {'leixing': '文件传递', 'ziduan1': file_name, 'ziduan2': '0', 'ziduan3': '传递失败'}
        return file_statr

#文件传递给服务器
def file_gain(user,sql,num=0):
    #防止有语法错误等原因导致死循环,限制最多处理4次
    if num < 5:
        # 文件传递给服务器
        try:
            file_statr = user.file_download(sql)
            num += 1
        except:
            file_statr = {'leixing': '文件下载', 'ziduan1': sql, 'ziduan2': '0', 'ziduan3': '传递失败'}
            num += 1
        # 如果执行结果不成功,再次执行一次,保底
        if file_statr['ziduan3'] != '传递成功':
            file_statr = file_gain(user,sql,num)
            return file_statr
        else:
            return file_statr
    else:
        file_statr = {'leixing': '文件下载', 'ziduan1': sql, 'ziduan2': '0', 'ziduan3': '传递失败'}
        return file_statr

def use_show():
    print('请您选择需要处理的类型(请输入选择的编码):')
    print('类型:发起访问 编码:1')
    print('类型:文件传递 编码:2')
    print('类型:文件下载 编码:3')

def use_choice(use_input):
    if use_input == '1':use_type = '发起访问'
    elif use_input == '2':use_type = '文件传递'
    elif use_input == '3':use_type = '文件下载'
    else:use_type = '类型不详'
    return use_type

def use_decision(user,use_input):
    if use_input == '1':
        name_text = input('请您输入需要发起的标题:')
        pass_text = input('请您输入需要发起的内容:')
        socket_dlgc_statr = user.socket_dlgc('发起访问', name_text, pass_text)
        print('系统反馈:', socket_dlgc_statr)

    elif use_input == '2':
        wav_write = input('请您输入需要发送文件名:')
        transfer_statr = file_transfer(user, wav_write)
        print('系统反馈:',transfer_statr)

    elif use_input == '3':
        sql = sql_out()
        gain_statr = file_gain(user, sql)
        print('系统反馈:', gain_statr)
    else:
        use_type = '类型不详'
    pass

#获取sql语句处理
def sql_out():
    # 语句存储
    sql_text = ''
    # 循环执行
    while True:
        sql = input('请您输入需要发送的语句:').replace("'", "^")
        try:
            sql_out = sql.index(';')
            #print('sql_out', sql_out, type(sql_out))
        except:
            sql_out = -1
            #print('sql_out', sql_out, type(sql_out))
        if sql_out == -1:
            sql_text = sql_text + ' ' + sql
        else:
            sql_text = sql_text + ' ' + sql
            break
    return sql_text.replace(";", " ")

if __name__ == '__main__':

    # 加载类
    user = My_Main()

    # 循环执行
    while True:
        use_show()
        use_input = input('请您输入选择的编码:')
        use_type = use_choice(use_input)

        print('您选择需要处理的类型:',use_type)

        #类型不详就断开
        if use_type=='类型不详':break

        use_decision(user, use_input)
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值