print(df.groupby('col1').size().reset_index(name='count').sort_values('c..

[17기_윤영민]

dba_auto.py

def   table_def(table_name):
    import cx_Oracle
    import pandas as pd

    dsn = cx_Oracle.makedsn('localhost', 1521, 'xe')
    db = cx_Oracle.connect('c##scott', 'tiger', dsn)
    cursor = db.cursor()   
    
    query = f"""SELECT A.COLUMN_ID AS "NO"
            , B.COMMENTS AS "논리명"
            , A.COLUMN_NAME AS "물리명"
            , A.DATA_TYPE AS "자료 형태"
            , A.DATA_LENGTH AS "길이"
            , DECODE(A.NULLABLE, 'N', 'No', 'Y', 'Yes') AS "Null 허용"
            , A.DATA_DEFAULT AS "기본값"
            , B.COMMENTS AS "코멘트"
            FROM  ALL_TAB_COLUMNS A
            LEFT JOIN ALL_COL_COMMENTS B
            ON A.OWNER = B.OWNER
            AND A.TABLE_NAME = B.TABLE_NAME
            AND A.COLUMN_NAME = B.COLUMN_NAME 
            WHERE A.TABLE_NAME = '{table_name}'  AND A.OWNER='C##SCOTT'
            ORDER BY A.COLUMN_ID"""
    
    cursor.execute(query )  

    row = cursor.fetchall()   

    colnames = cursor.description

    col = [ ] 
    for  i   in   colnames:
        col.append( i[0].lower()  )

    df = pd.DataFrame(row , columns = col )
    return ( df )




def easy_dba() :
    while True :
        print(""" ====== dba작업을 편하게 수행하기 위한 스크립트 총 모음 =====
              0. 종료
              1. 테이블 스페이스 사용량 확인
              2. 현재 DB에 락(lock) 발생유무확인
              3. 오라클과 연동
              4. alert log file에서 에러번호 갯수 확인
              5. """)
        num=int(input('\n번호 입력 : '))
        if num==0 :
            print('종료')
            break;
        elif num==1 :
            import  cx_Oracle  # 오라클과 파이썬을 연동하기 위한 모듈
            import  pandas  as  pd  # 오라클의 테이블을 파이썬에서 출력하기 위한 모듈
            dsn = cx_Oracle.makedsn( 'localhost' , 1521, 'xe')  # 오라클 접속 정보로 dsn 생성
            db = cx_Oracle.connect('c##scott','tiger', dsn) # 유져이름과 패스워드로 접속
                                                                                  # 인스턴스 생성 
            cursor  = db.cursor()        # 오라클의 데이터를 올리기 위한 메모리를 생성
            cursor.execute(""" select   t.tablespace_name,
                                        round(((t.total_size - f.free_size) / t.total_size),2) * 100 usedspace
                                        from (select tablespace_name, sum(bytes)/1024/1024 total_size
                                                  from dba_data_files
                                                  group by tablespace_name) t,
                                            (select  tablespace_name, sum(bytes)/1024/1024 free_size
                                                 from dba_free_space
                                                 group by tablespace_name) f
                                        where t.tablespace_name = f.tablespace_name(+)   """)  #  쿼리문으로 수행된 결과가 
                                                                           # 메모리 cursor 에 올라갑니다.
            row = cursor.fetchall()      # 메모리에 있는 데이터를 전부 fetch 해서 row 에 담고
            space =pd.DataFrame(row)  #  담긴 데이터 row 로 판다스 데이터 프레임을 생성 
            print ('\n',space,'\n\n')
        elif num==2 :
            continue
        elif num==3 :
            import  cx_Oracle  # 오라클과 파이썬을 연동하기 위한 모듈
            import  pandas  as  pd #오라클의 테이블을 파이썬에서 출력하기 위한 모듈
            dsn = cx_Oracle.makedsn( 'localhost' , 1521, 'xe') #오라클 접속 정보로 dsn 생성
            db = cx_Oracle.connect('c##scott','tiger', dsn) #유져이름과 패스워드로 접속인스턴스 생성
            cursor  = db.cursor() # 오라클의 데이터를 올리기 위한 메모미를 생성
            cursor.execute(""" select  * 
                                   from  emp     
                                   """) # 쿼리문으로 수행된 결과가 메모리 cursor에 올라간다.
            row = cursor.fetchall() #메모리에 있는 데이터를 전부 fetch해서 row에 넣는다
             
            colname = cursor.description
            col=[]
            for  i   in   colname:
                col.append( i[0].lower() )

            emp = pd.DataFrame (list(row), columns=col) 
            #print(emp)
            print('연동 완료\n\n')
        elif num==4 :
            jobs = open("C:\\app\\ITWILL\\product\\18.0.0\\diag\\rdbms\\xe\\xe\\trace\\alert_xe.log",  encoding='cp949', errors='ignore')

            data = jobs.read()
            data2= data.split()
            k = [  ]                             
            for  i  in  data2:               
                if  'ora-' in  i.lower():     
                    k.append(i)       
                      
            import   pandas   as   pd       

            df = pd.DataFrame(  k,  columns=['col1'] ) 
            from   pandasql  import   sqldf    
            pysqldf  =  lambda   q :  sqldf( q,  globals() )

            q = """  select  col1, count(*)
                        from  df
                        group by  col1
                        order by 2 desc;   """

            print(df.groupby('col1').size().reset_index(name='count').sort_values('count', ascending=False))
        

import dba_auto as a

emp=a.table_def('EMP')
#emp.to_excel("c:\\aaa2\\emp_df.xlsx")
a.easy_dba()

4번 누를시 잘 안나온다.

[17기_윤영민]

만약 import 가 안될 경우
아래 코드를 실행


import sys

print(sys.path)
sys.path.append('C:\\Users\\ITWILL\\.spyder-py3')