Python implementation of a self-service number query tool

1：pd.read_sql_query()
Read custom data，return of something its original ownerDataFramespecification，pass (a bill or inspection etc)SQLQuery scripts including additions, deletions and modifications。
pd.read_sql_query(sql, con, index_col=None,coerce_float=True, params=None, parse_dates=None,chunksize=None)
sql：executablesqlscripts，text type
con：database connection
index_col:Select the column that returns the index of the result set,copies/copies列表
coerce_float:Very useful.，String in numeric form directly asfloatinput data type
parse_dates:Converts a column of date-type strings into thedatetimetype data，together withpd.to_datetimefunction is similar to。
params:towardsqlscripts中传入的参数，There are lists of official types，Tuples and Dictionaries。The syntax used to pass parameters is database driver related。
chunksize：If an integer value is provided，Then it will return agenerator，The number of lines per output is the size of the supplied value

read_sql_query（）acceptableSQLstatement，DELETE,INSERT INTO、UPDATEThe operation has no return value（But it will be executed in the database），The program throws theSourceCodeCloseError，and terminate the proceedings。SELECTwill return the result。If you want to keep running，cantryCatch this exception。
 
2：pd.read_sql_table()
Reading a table in a database，return of something its original ownerDataFramespecification（pass (a bill or inspection etc)表名）
import pandas as pd
pd.read_sql_table(table_name, con, schema=None,index_col=None, coerce_float=True, parse_dates=None, columns=None,chunksize=None)
 
3:pd.read_sql()
读数据库pass (a bill or inspection etc)SQLscripts或者表名
import pandas as pd
pd.read_sql(sql, con, index_col=None,coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

import cx_Oracle
# Pandas read/write manipulation of Oracle databases
import pandas as pd

# Avoid messy codes from encoding issues
import os
['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'


class Oracle_DB(object):
    def __init__(self):
        try:
            # Connect to oracle
            # Method 1: create_engine() provided by sqlalchemy
            # from sqlalchemy import create_engine
            # engine = create_engine('oracle+cx_oracle://username:password@ip:1521/ORCL')
            # # Method 2: cx_Oracle.connect()
             = cx_Oracle.connect('username', 'password', 'ip:1521/database')

        except cx_Oracle.Error as e:
            print("Error %d：%s" % ([0], [1]))
            exit()
            
    # Search for partial information
    def search_one(self, sql,sparm):
        try:
            # query to get data with sql statement
            # Substitute parameters: sparm -- query specified field parameters
            df = pd.read_sql_query(sql, ,params=sparm)

            ()

        except Exception as e:
            return "Error " + [0]

        return df

    # Search for all information
    def search_all(self, sql):
        try:

            # query to get data with sql statement

            df = pd.read_sql_query(sql, )

            ()

        except Exception as e:
            return "Error " + [0]

        return df

#Build ID - Numbering Dictionary
def buildid():
    sqlid = """select * from b_build_info"""
    db = Oracle_DB()  # Instantiate an object
    b_build_info = db.search_all(sqlid)
    ID_bUILDCODE = b_build_info.set_index("BUILDCODE")["ID"].to_dict()
    return ID_bUILDCODE
    
# Incoming list of data to be exported by text
def read_task_list():
    build_code=buildid()
    tasklist=[]
    is_first_line=True
    with open("./b_lst.txt") as lst:
        for line in lst:
            if is_first_line:
                is_first_line=False
                continue
            (build_code.get(('\n')))  # Key-value pair conversion
    return tasklist

def sql_d(lst):
    # Monthly data
    sql_d_energy_item_month = """select * from d_energy_item_month
           where recorddate >= to_date(:Start_time, 'yyyy-MM-dd')
           and recorddate < to_date(:End_time, 'yyyy-MM-dd')
           and  buildid  in {0}
           order by recorddate asc""".format(lst)

    # Monthly data
    sql_d_energy_month = """select d.*, from d_energy_month d join t_device_info t on  = 
           where  >= to_date(:Start_time, 'yyyy-MM-dd')
           and  < to_date(:End_time, 'yyyy-MM-dd')
           and  = '{0}'
           order by  asc""".format(lst)

    # Query the data of the day
    sql_energy_item_hour_cheak = """select * from d_energy_item_hour
            where trunc(sysdate)=trunc(recorddate)
            order by recorddate asc""".format(lst)

    sql_collection = [sql_d_energy_item_month, sql_d_energy_item_day, sql_d_energy_item_hour, sql_d_energy_month,
                      sql_d_energy_day, sql_d_energy_hour, sql_energy_hour_cheak]
                      # Omit part of the sql statement here
    return sql_collection

def db_extranction(lst,sparm,sql_type):   
    """sql_type - enter the serial number of the sql operation to be performed"""
    sql_=sql_d(lst)[sql_type]  # Output sql statements
    db = Oracle_DB()  # Instantiate an object
    res=db.search_one(sql_,sparm)
    # Data handling processing
    RES=Data_item_factory(res)  # Omitted here
    # res = db.search_all(sql_d_energy_item_month)
    print(RES)
    return RES

import threading
# Pandas read/write manipulation of Oracle databases
from tools.Data_Update_oracle import Oracle_DB
import pandas as pd
from concurrent import futures  

if __name__ == '__main__':
    #External incoming
    tasklist= read_task_list()
    print(tasklist)
    # Enter the time lookup range parameter, which can be manually modified
    sparm = {'Start_time':'2021-04-01','End_time':'2021-05-01'}
    lst = tuple(list(tasklist))
    
    # Business type serial number, can be manually modified
    sql_type=0
    
    # Extract all
    db_extranction(lst,sparm,sql_type)  

    # Multi-threaded batch extraction by field
    Method 1：utilizationthreadingmodularThreadClass constructors create threads
    #threads=[(target=db_extranction,args=(lst,sparm,sql_type)) for lst in tasklist]
    # [threads[i].start() for i in range(len(threads))]
    
    Method II：utilizationpython(used form a nominal expression)concurrentstorehouse，This is officially based on threading seal inside，先安装该storehouse
    # with (len(tasklist)) as executor:
    #     ([db_extranction(lst,sparm,sql_type) for lst in tasklist],tasklist)