Python连接数据读取大数据量

#!/usr/bin/env python
# -* -coding:utf-8 -*-
# @Time: 2020/10/19 21:46
# @Author: 小梁同学0311
# @File : 梁岳总结的类方法-001.py
# @Software: PyCharm
"""
Explain: 梁岳总结的类方法-001
"""
import signal
import subprocess
import os
import time
import cx_Oracle
import pexpect


def equal_ctrl_c_2_terminal(command, current_path, current_path):
    """
    :param command: 你所要在终端执行的命令
    :param current_path: 终端命令执行所在的目录
    :param current_path: 终端命令执行指定时长后停止
    :return: None  类似终端 ctrl+c
    """
    process = subprocess.Popen(command, shell=True, cwd=current_path)
    pid = process.pid
    time.sleep(current_path)
    os.kill(pid, signal.CTRL_C_EVENT)
    return None


def append_content_file(file_name, appendLine):
    """
    :param file_name: 文件名
    :param appendLine: 追加行内容
    :return: None
    """
    f = open(file_name, 'a')
    f.write(appendLine + 'n')
    f.close()
    return None


def write_content_file(file_name, write_line):
    """
    :param file_name: 文件名
    :param write_line: 写入文件内容
    :return: None
    """
    f = open(file_name, 'w')
    f.write(write_line + 'n')
    f.close()
    return None


def truncate_content_file(file_name):
    """
    :param file_name: 文件名
    :return: None
    """
    f = open(file_name, 'w')
    f.seek(0)
    f.truncate(0)
    f.close()
    return None


class ConnectionOracleMethod(object):
    def __init__(self, user_name, pass_word, db_name, sql):
        self.user_name = user_name
        self.pass_word = pass_word
        self.db_name = db_name
        self.sql = sql

    def oracle_connection_fetchall(self):
        conn = cx_Oracle.connect(self.user_name, self.pass_word, self.db_name)
        cur = conn.cursor()
        cur.execute(self.sql)
        res_get_all = cur.fetchall()  # 以元组的形式获取sql查询的全部结果
        cur.close()
        conn.commit()
        conn.close()
        return res_get_all

    def oracle_connection_fetch_many(self, get_count_per):
        """
        :param get_count_per: 指定读取的数据量
        :Explain: 当执行sql数据量特别大的时候可以使用该方法，每次获取指定条数的数据量
        """
        conn = cx_Oracle.connect(self.user_name, self.pass_word, self.db_name)
        cur = conn.cursor()
        cur.execute(self.sql)
        while True:
            time.sleep(3)
            res_get_many = cur.fetchmany(get_count_per)  # 以元组的形式获取sql查询的全部结果
            if len(res_get_many) == 0:
                break
            else:
                print(res_get_many)  # 每次获取指定条数数据打印，直到获取完
                conn.commit()
        cur.close()
        conn.close()


def file_transmission():
    """
    :return:通过scp远程复制单个文件
    """
    curr_host_file = input('请输入你要传输的路径文件：33[5;32m /app/tmp/testFile.txt33[0mn')
    visit_ip = input('请输入你要访问的IP：33[5;32m 127.0.0.133[0mn')
    visit_user = input('请输入你要访问的用户：33[5;32m root33[0mn')
    visit_pass_word = input('请输入你要访问主机的密码：33[5;32m liangYue031133[0mn')
    output_path = input('请输入你要下载文件到的路径：33[5;32m /app/dag/liangYue33[0mn')
    execute_cmd = 'scp -r {0} {1}@{2}:{3}'.format(curr_host_file, visit_user, visit_ip, output_path)
    child = pexpect.spawn(execute_cmd)
    try:
        child.expect(['[pP]assword','100%'], timeout=30)
        child.sendline(visit_pass_word)
        child.read()  # 这一步很重要,否则scp命令不生效
    except pexpect.TIMEOUT:
        pass


if __name__ == '__main__':
    com = ConnectionOracleMethod('test', 'test123', 'db_test', 'select * from tablefile where rownum <100')
    print(com.oracle_connection_fetch_many(30))