Dbcafe dba works tools
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 Dbcafe dba works tools[편집]
1.1 table_diff.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;
}