Dbcafe dba works tools

1 Dbcafe dba works tools[편집]

1.1 dbcafe_dba_works.py[편집]

import pandas as pd
import json
import cx_Oracle

# 데이터베이스 연결 정보
USERNAME = 'dbcafe'
PASSWORD = ''
HOSTNAME = '127.0.0.1'
PORT = 1521
SERVICE_NAME = 'ORCL'

# DSN (Data Source Name) 구성
dsn_tns = cx_Oracle.makedsn(HOSTNAME, PORT, service_name=SERVICE_NAME)

# Create a sample DataFrame
# data = {
#     'Name': ['Alice', 'Bob', 'Charlie'],
#     'Age': [25, 30, 35],
#     'City': ['New York', 'Los Angeles', 'Chicago']
# }

# df = pd.DataFrame(data)

# Convert DataFrame to JSON
def dataframe_to_json(df):
    return df.to_json(orient='records')


# Generate HTML table with DataTables
        # <!-- Main Content -->
        # <div class="main-content">
        #     <h1>{title}</h1>
        #     <table id="dataTable" class="display">
        #         <thead>
        #             <tr>
        #                 <!-- Headers will be inserted here -->
        #             </tr>
        #         </thead>
        #         <tbody>
        #             <!-- Data rows will be inserted here -->
        #         </tbody>
        #     </table>
        # </div>            
        # <script>
        #     $(document).ready(function() {{
        #         // Convert JSON data to table format
        #         var jsonData = {json_data};
        #         var columns = Object.keys(jsonData[0]).map(function(key) {{
        #             return {{ title: key, data: key }};
        #         }});
                
        #         // Initialize DataTable
        #         $('#dataTable').DataTable({{
        #             data: jsonData,
        #             columns: columns
        #         }});
        #     }});
        # </script>       
    
def generate_html_with_datatables(title,data_table_name,json_data_name,json_data):
    html = f'''

            <h1>{title}</h1>
            <table id="{data_table_name}" class="display">
                <thead>
                    <tr>
                        <!-- Headers will be inserted here -->
                    </tr>
                </thead>
                <tbody>
                    <!-- Data rows will be inserted here -->
                </tbody>
            </table>
        
        <script>
            $(document).ready(function() {{
                // Convert JSON data to table format
                var {json_data_name} = {json_data};
                var columns = Object.keys({json_data_name}[0]).map(function(key) {{
                    return {{ title: key, data: key }};
                }});
                
                // Initialize DataTable
                $('#{data_table_name}').DataTable({{
                    data: {json_data_name},
                    columns: columns
                }});
            }});
        </script>             

    '''
    return html

def generate_html_header():
    html = f'''
    <!DOCTYPE html>
    <html>
    <head>
        <title>DataFrame as DataTable</title>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">
        <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
        <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
        <link rel="stylesheet" href="datagrid.css" />        

    </head>
    <body>
        <!-- Title Bar -->
        <div class="title-bar">
            <h1> DBCAFE - DBA Works </h1>
        </div>

        <!-- Side Menu -->
        <div class="side-menu">
            Home
            Table
            Settings
            About
        </div>
        <!-- Main Content -->
        <div class="main-content">
    '''
    return html


def generate_html_footer():
    html = f'''

    </div>            
    </body>
    </html>
    '''
    return html




# Oracle DB 연결 설정
def connect_to_oracle(username, password, hostname, port, service_name):
    dsn = cx_Oracle.makedsn(hostname, port, service_name=service_name)
    connection = cx_Oracle.connect(username, password, dsn)
    return connection

# 테이블의 컬럼 정보를 가져오는 함수
def get_table_columns_df(cursor, table_name):
    query = f"""
    SELECT OWNER,TABLE_NAME,COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE
    FROM ALL_TAB_COLUMNS
    WHERE TABLE_NAME = :table_name
    """
    cursor.execute(query, [table_name.upper()])
    columns = cursor.fetchall()
    
    # pandas DataFrame으로 변환
    df = pd.DataFrame(columns, columns=["OWNER","TABLE_NAME","COLUMN_NAME", "DATA_TYPE", "DATA_LENGTH", "NULLABLE"])
    return df

# 두 테이블의 컬럼 정보를 비교하는 함수
def compare_columns_df(df1, df2, table1_name, table2_name):
    # 컬럼 이름으로 비교
    df1_cols = df1[['OWNER','TABLE_NAME','COLUMN_NAME', 'DATA_TYPE', 'DATA_LENGTH', 'NULLABLE']]
    df2_cols = df2[['OWNER','TABLE_NAME','COLUMN_NAME', 'DATA_TYPE', 'DATA_LENGTH', 'NULLABLE']]
    
    # 두 DataFrame에서 서로 다른 컬럼 찾기
    diff_table1 = df1_cols[~df1_cols.isin(df2_cols.to_dict(orient="list")).all(axis=1)]
    diff_table2 = df2_cols[~df2_cols.isin(df1_cols.to_dict(orient="list")).all(axis=1)]


    # # Convert DataFrame to JSON
    # json_data = dataframe_to_json(df)

    # # Generate HTML with DataTables
    # html_content = generate_html_with_datatables(json_data)

    # Save HTML content to a file
    # with open('dataframe_datatable.html', 'w') as file:
    #     file.write(html_content)

    # print("HTML file has been generated and saved as 'dataframe_datatable.html'.")

    with open('dataframe_datatable.html', 'w') as file:
        file.write(generate_html_header())        
        

    # 결과 출력
    if not diff_table1.empty:
        table_title     = f"테이블 {table1_name}에만 존재하는 컬럼들:"
        data_table_name = "DataTable"
        json_data_name  = "jsonData"

        print(f"\n테이블 {table1_name}에만 존재하는 컬럼들:")
        print(diff_table1)
        # Convert DataFrame to JSON
        json_data = dataframe_to_json(diff_table1)

        # Generate HTML with DataTables
        # html_content = generate_html_with_datatables(table_title,json_data)
        html_content = generate_html_with_datatables(table_title,data_table_name,json_data_name,json_data)         
        # Save HTML content to a file
        with open('dataframe_datatable.html', 'a') as file:
            file.write(html_content)
    else:
        print(f"\n테이블 {table1_name}와 {table2_name}의 모든 컬럼이 동일합니다.")
        table_title = f"테이블 {table1_name}와 {table2_name}의 모든 컬럼 동일."
        data_table_name = "DataTable"
        json_data_name  = "jsonData"        
        # Convert DataFrame to JSON
        json_data = dataframe_to_json(diff_table1)

        # Generate HTML with DataTables
        html_content = generate_html_with_datatables(table_title,data_table_name,json_data_name,json_data)         
                 
        # Save HTML content to a file
        with open('dataframe_datatable.html', 'a') as file:
            file.write(html_content)

    if not diff_table2.empty:
        print(f"\n테이블 {table2_name}에만 존재하는 컬럼들:")
        table_title = f"테이블 {table2_name}에만 존재하는 컬럼들:"
        data_table_name = "DataTable2"
        json_data_name  = "jsonData2"                
        print(diff_table2)
        # Convert DataFrame to JSON
        json_data = dataframe_to_json(diff_table2)

        # Generate HTML with DataTables
        html_content = generate_html_with_datatables(table_title,data_table_name,json_data_name,json_data)         
    #         
        # Save HTML content to a file
        with open('dataframe_datatable.html', 'a') as file:
            file.write(html_content)

    else:
        print(f"\n테이블 {table2_name}와 {table1_name}의 모든 컬럼이 동일합니다.")
        table_title = f"테이블 {table2_name}와 {table1_name}의 모든 컬럼 동일."
        data_table_name = "DataTable2"
        json_data_name  = "jsonData2"                
        json_data = dataframe_to_json(diff_table2)

        # Generate HTML with DataTables
        html_content = generate_html_with_datatables(table_title,data_table_name,json_data_name,json_data)         

        # Save HTML content to a file
        with open('dataframe_datatable.html', 'a') as file:
            file.write(html_content)

    # footer
    with open('dataframe_datatable.html', 'a') as file:
        file.write(generate_html_footer())        

# 메인 함수
def main():
    # 데이터베이스 연결 정보
    username = "dbcafe"
    password = "clcl"
    hostname = "127.0.0.1"
    port = "1521"
    service_name = "ORCL"

    # 비교할 테이블 이름
    table1 = "TB_TEST"
    table2 = "TB_TEST2"

    # Oracle DB 연결
    connection = connect_to_oracle(username, password, hostname, port, service_name)
    cursor = connection.cursor()

    # 테이블의 컬럼 정보 가져오기
    df1 = get_table_columns_df(cursor, table1)
    df2 = get_table_columns_df(cursor, table2)

    # 컬럼 비교
    compare_columns_df(df1, df2, table1, table2)





    # 연결 종료
    cursor.close()
    connection.close()



if __name__ == "__main__":
    main()

1.2 datagrid.css[편집]

body {
    font-family: Arial, sans-serif;
    margin: 0;
    padding: 0;
    display: flex;
    background-color: #1e1e1e;
    color: #d3d3d3;
}

/* Style for title bar */
.title-bar {
    height: 60px;
    background-color: #444;
    color: white;
    display: flex;
    align-items: center;
    justify-content: center;
    position: fixed;
    top: 0;
    left: 0;
    width: 100%;
    z-index: 1000;
    box-shadow: 0 2px 5px rgba(0, 0, 0, 0.2);
}

.title-bar h1 {
    margin: 0;
    font-size: 24px;
}
/* Style for side menu */
.side-menu {
    height: 100vh;
    width: 200px;
    background-color: #333;
    color: white;
    padding-top: 80px;
    position: fixed;
    top: 0;
    left: 0;
    overflow-x: hidden;
}

.side-menu a {
    padding: 10px 15px;
    text-decoration: none;
    font-size: 18px;
    color: white;
    display: block;
}

.side-menu a:hover {
    background-color: #575757;
}

/* Main content styling with dark theme */
.main-content {
    margin-left: 200px; /* Same width as the side menu */
    padding: 20px;
    padding-top: 80px; /* Space for title bar */
    width: calc(100% - 200px);
    background-color: #333;
    min-height: 100vh;
}


/* Dark theme for table */
table {
    width: 100%;
    border-collapse: collapse;
    background-color: #3a3a3a;
    color: #d3d3d3;
}

th, td {
    padding: 8px;
    text-align: left;
    border: 1px solid #555;
}

th {
    background-color: #4d4d4d;
}

/* Customize h1 for dark theme */
h1 {
    color: #ffffff;
}

Dbcafe dba works tools

DB CAFE

1 Dbcafe dba works tools[편집]

1.1 dbcafe_dba_works.py[편집]

1.2 datagrid.css[편집]