consolidator

1
import oracledb as ora
2
import pandas as pd
3

4
class OraWorker(object):
5

6
    def __init__(self,user:str,password:str,dsn:str,client:str) -> None:
7
        try:
8
            ora.init_oracle_client(lib_dir=client)
9
            self.connection = ora.connect(dsn=dsn,user=user,password=password)
10
            self.cursor = self.connection.cursor()
11
        except: raise
12
    
13
    def ora_version(self)->str:
14
        try:
15
            res = []
16
            for row in self.cursor.execute("SELECT * FROM v$version"):
17
                res.append(f"{row}")
18
            # for row in self.cursor.execute("SELECT * FROM v$instance"):
19
            #     res.append(f"{row}")
20
            # for row in self.cursor.execute("select sysdate as dt, 1 as ver from dual"):
21
            #     res.append(f"{row[0]}")
22
            return "\n".join(res)
23
        except :raise
24

25
    def product_exists(self,product:str)->bool:
26
        try:
27
            # print(product)
28
            self.cursor.execute("select count(1) from ione.items where state='A' and code_name=:code",[product])
29
            result=self.cursor.fetchone()[0]
30
            return result==1
31
        except: raise
32
        
33
    def product_check(self,product:str,nomenclature:str)->tuple:
34
        try:
35
            sql="""select  
36
                    (select nvl(max(m.scp_product_name),'нет') from scm_fact.product_map_sap_scp m where m.sap_product_code=:NOM) nom_to_up,
37
                    (select count(1) from ione.items where state='A' and code_name=:UP) exists_in_up
38
                    from dual"""
39
            self.cursor.execute(sql,[nomenclature,product])
40
            record = self.cursor.fetchone()
41
            correct = 1 if record[0]==product else 0
42
            return (record[0],record[1],correct)
43
        except Exception as exp : return(f"{exp}",0,0)
44
    
45
    def close(self):
46
        try:
47
            self.cursor.close()
48
            self.connection.close()
49
        except: raise