<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>万得数据库浏览器</title>
<style>
* {
box-sizing: border-box;
font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
}
body {
margin: 0;
padding: 20px;
background-color: #f5f7fa;
color: #333;
}
.container {
max-width: 1200px;
margin: 0 auto;
background: white;
border-radius: 10px;
box-shadow: 0 0 20px rgba(0,0,0,0.1);
padding: 25px;
}
h1 {
color: #2c3e50;
text-align: center;
margin-bottom: 30px;
}
.search-box {
display: flex;
margin-bottom: 20px;
position: relative;
}
#search-input {
flex: 1;
padding: 12px 20px;
font-size: 16px;
border: 2px solid #3498db;
border-radius: 50px;
outline: none;
transition: all 0.3s;
}
#search-input:focus {
border-color: #2980b9;
box-shadow: 0 0 10px rgba(52, 152, 219, 0.3);
}
#search-button {
position: absolute;
right: 5px;
top: 5px;
background: #3498db;
color: white;
border: none;
padding: 8px 20px;
border-radius: 50px;
cursor: pointer;
font-weight: bold;
transition: background 0.3s;
}
#search-button:hover {
background: #2980b9;
}
.results-container {
display: none;
margin-top: 30px;
}
.table-card {
background: white;
border-radius: 8px;
box-shadow: 0 2px 10px rgba(0,0,0,0.05);
margin-bottom: 20px;
overflow: hidden;
border: 1px solid #e0e6ed;
}
.table-header {
background: #3498db;
color: white;
padding: 12px 20px;
display: flex;
justify-content: space-between;
align-items: center;
}
.table-name {
font-weight: bold;
font-size: 18px;
}
.export-btn {
background: #27ae60;
color: white;
border: none;
padding: 6px 15px;
border-radius: 4px;
cursor: pointer;
font-size: 14px;
transition: background 0.3s;
}
.export-btn:hover {
background: #219653;
}
.columns-table {
width: 100%;
border-collapse: collapse;
}
.columns-table th {
background: #f8f9fa;
text-align: left;
padding: 12px 15px;
border-bottom: 2px solid #e0e6ed;
}
.columns-table td {
padding: 10px 15px;
border-bottom: 1px solid #e0e6ed;
}
.columns-table tr:nth-child(even) {
background-color: #f8f9fa;
}
.no-results {
text-align: center;
padding: 30px;
color: #7f8c8d;
font-style: italic;
}
.loading {
display: none;
text-align: center;
padding: 20px;
}
.loading-spinner {
border: 4px solid #f3f3f3;
border-top: 4px solid #3498db;
border-radius: 50%;
width: 30px;
height: 30px;
animation: spin 1s linear infinite;
margin: 0 auto;
}
@keyframes spin {
0% { transform: rotate(0deg); }
100% { transform: rotate(360deg); }
}
.error-message {
color: #e74c3c;
text-align: center;
padding: 20px;
display: none;
}
.export-dictionary-btn {
background: linear-gradient(to right, #8e44ad, #9b59b6);
color: white;
border: none;
padding: 12px 30px;
border-radius: 50px;
cursor: pointer;
font-size: 16px;
font-weight: bold;
box-shadow: 0 4px 10px rgba(0,0,0,0.1);
transition: all 0.3s ease;
display: inline-block;
margin: 20px auto;
}
.export-dictionary-btn:hover {
transform: translateY(-2px);
box-shadow: 0 6px 15px rgba(0,0,0,0.2);
background: linear-gradient(to right, #9b59b6, #8e44ad);
}
</style>
</head>
<body>
<div class="container">
<h1>万得数据库浏览器</h1>
<div class="search-box">
<input type="text" id="search-input" placeholder="输入表名、字段名或注释进行搜索...">
<button id="search-button">搜索</button>
</div>
<div style="text-align: center;">
<button id="export-dictionary" class="export-dictionary-btn">
<i class="fas fa-book"></i> 导出数据库字典
</button>
</div>
<div class="loading">
<div class="loading-spinner"></div>
<p>正在搜索,请稍候...</p>
</div>
<div class="error-message" id="error-message"></div>
<div class="results-container" id="results-container">
<!-- 搜索结果将在这里动态显示 -->
</div>
</div>
<script>
// 在<script>标签内添加
document.getElementById('export-dictionary').addEventListener('click', function() {
// 显示加载提示
const loadingElement = document.querySelector('.loading');
loadingElement.style.display = 'block';
loadingElement.querySelector('p').textContent = '正在生成数据库字典,请稍候...';
// 发起导出请求
window.location.href = '/qwind/export/dictionary';
// 2秒后隐藏加载提示(实际导出时间可能更长)
setTimeout(() => {
loadingElement.style.display = 'none';
}, 2000);
});
document.addEventListener('DOMContentLoaded', function() {
const searchInput = document.getElementById('search-input');
const searchButton = document.getElementById('search-button');
const resultsContainer = document.getElementById('results-container');
const loadingElement = document.querySelector('.loading');
const errorMessage = document.getElementById('error-message');
// 搜索按钮点击事件
searchButton.addEventListener('click', performSearch);
// 回车键搜索
searchInput.addEventListener('keyup', function(event) {
if (event.key === 'Enter') {
performSearch();
}
});
function performSearch() {
const searchTerm = searchInput.value.trim();
if (!searchTerm) {
resultsContainer.style.display = 'none';
errorMessage.style.display = 'block';
errorMessage.textContent = '请输入搜索内容';
return;
}
// 显示加载动画
loadingElement.style.display = 'block';
resultsContainer.style.display = 'none';
errorMessage.style.display = 'none';
// 发送AJAX请求
fetch(`/qwind/search?q=${encodeURIComponent(searchTerm)}`)
.then(response => {
if (!response.ok) {
throw new Error('搜索失败');
}
return response.json();
})
.then(data => {
loadingElement.style.display = 'none';
if ('error' in data) {
errorMessage.style.display = 'block';
errorMessage.textContent = `错误: ${data.error}`;
return;
}
// 处理搜索结果
displayResults(data);
})
.catch(error => {
loadingElement.style.display = 'none';
errorMessage.style.display = 'block';
errorMessage.textContent = `请求失败: ${error.message}`;
});
}
function displayResults(data) {
resultsContainer.innerHTML = '';
if (Object.keys(data).length === 0) {
resultsContainer.innerHTML = `
<div class="no-results">
<p>没有找到匹配的表或字段</p>
</div>
`;
resultsContainer.style.display = 'block';
return;
}
for (const [tableName, columns] of Object.entries(data)) {
const tableCard = document.createElement('div');
tableCard.className = 'table-card';
tableCard.innerHTML = `
<div class="table-header">
<div class="table-name">${tableName}</div>
<button class="export-btn" data-table="${tableName}">导出数据</button>
</div>
<table class="columns-table">
<thead>
<tr>
<th width="40%">字段名</th>
<th width="60%">注释/字段名</th>
</tr>
</thead>
<tbody>
${columns.map(col => `
<tr>
<td>${col.name}</td>
<td>${col.comment}</td>
</tr>
`).join('')}
</tbody>
</table>
`;
resultsContainer.appendChild(tableCard);
}
// 添加导出按钮事件
document.querySelectorAll('.export-btn').forEach(btn => {
btn.addEventListener('click', function() {
const tableName = this.getAttribute('data-table');
window.location.href = `/qwind/export/${tableName}`;
});
});
resultsContainer.style.display = 'block';
}
});
</script>
</body>
</html>
from flask import Flask, render_template, request, send_file, jsonify
import cx_Oracle
import csv
import io
import os
from flask import send_file
from openpyxl import Workbook
import io
import pandas as pd
app = Flask(__name__)
# Oracle数据库配置
ORACLE_USER = os.getenv('ORACLE_USER', 'az_wasp')
ORACLE_PASSWORD = os.getenv('ORACLE_PASSWORD', 'cmf+2011')
ORACLE_DSN = os.getenv('ORACLE_DSN', '192.168.125.66/PDB_CMWADEV')
def get_db_connection():
return cx_Oracle.connect(ORACLE_USER, ORACLE_PASSWORD, ORACLE_DSN)
@app.route('/qwind')
def index():
"""显示搜索页面"""
return render_template('index.html')
@app.route('/qwind/search')
def search_tables():
"""根据查询条件搜索表和字段"""
search_term = request.args.get('q', '').strip().upper()
try:
with get_db_connection() as conn:
cursor = conn.cursor()
# 查询匹配的表和字段
query = """
SELECT
t.table_name,
c.column_name,
NVL(com.comments, c.column_name) AS column_comment
FROM all_tables@wd_dblink_20230908 t
JOIN all_tab_columns@wd_dblink_20230908 c ON t.table_name = c.table_name
LEFT JOIN all_col_comments@wd_dblink_20230908 com
ON c.table_name = com.table_name
AND c.column_name = com.column_name
WHERE (t.owner = 'WIND_FILESYNC' and c.owner = 'WIND_FILESYNC' and com.owner = 'WIND_FILESYNC') and
t.table_name LIKE :1
OR c.column_name LIKE :1
OR com.comments LIKE :1
ORDER BY t.table_name, c.column_id
"""
# 添加通配符进行模糊匹配
search_pattern = f'%{search_term}%'
cursor.execute(query, [search_pattern])
# 组织结果数据
results = {}
for row in cursor:
table_name, column_name, column_comment = row
if table_name not in results:
results[table_name] = []
results[table_name].append({
'name': column_name,
'comment': column_comment
})
return jsonify(results)
except Exception as e:
return jsonify({'error': str(e)}), 500
@app.route('/qwind/export/<table_name>')
def export_table(table_name):
"""导出表数据为CSV"""
try:
with get_db_connection() as conn:
cursor = conn.cursor()
# 获取字段注释作为列名
col_query = """
SELECT
col.column_name,
NVL(com.comments, col.column_name) AS column_comment
FROM all_tab_columns@wd_dblink_20230908 col
LEFT JOIN all_col_comments@wd_dblink_20230908 com
ON col.table_name = com.table_name
AND col.column_name = com.column_name
WHERE col.table_name = :1
and (col.owner = 'WIND_FILESYNC' and com.owner = 'WIND_FILESYNC')
ORDER BY col.column_id
"""
cursor.execute(col_query, [table_name.upper()])
columns = cursor.fetchall()
headers = [col[1] for col in columns]
col_names = [col[0] for col in columns]
# 构建数据查询(限制10万行)
data_query = f"""
SELECT {', '.join(col_names)}
FROM wind_filesync.{table_name}@wd_dblink_20230908
WHERE ROWNUM <= 100000
"""
print(data_query)
cursor.execute(data_query)
# 创建CSV内存文件
output = io.StringIO()
writer = csv.writer(output)
writer.writerow(headers) # 写入表头(字段注释)
writer.writerows(cursor) # 写入数据
# 返回CSV文件
output.seek(0)
return send_file(
io.BytesIO(output.getvalue().encode()),
mimetype='text/csv',
as_attachment=True,
attachment_filename=f'{table_name}_data.csv'
)
except Exception as e:
return f"Export failed: {str(e)}", 500
# 添加新路由
@app.route('/qwind/export/dictionary')
def export_dictionary():
"""导出数据库表和字段字典为Excel文件"""
try:
with get_db_connection() as conn:
cursor = conn.cursor()
# 执行提供的SQL查询
sql = """
SELECT t.table_name AS 表名,
t2.comments AS 表中文名,
t.column_name AS 字段名,
t.comments AS 字段中文名,
t4.data_type AS 字段类型,
t4.data_length AS 字段长度,
t3.num_rows AS "行数量(近似值)"
FROM ALL_COL_COMMENTS@WD_DBLINK_20230908 t
LEFT JOIN (SELECT * FROM ALL_TAB_COMMENTS@WD_DBLINK_20230908 WHERE owner = 'WIND_FILESYNC') t2
ON t.table_name = t2.table_name
LEFT JOIN (SELECT * FROM ALL_TABLES@WD_DBLINK_20230908 t WHERE t.owner = 'WIND_FILESYNC') t3
ON t.table_name = t3.table_name
LEFT JOIN (SELECT * FROM all_tab_columns@WD_DBLINK_20230908 WHERE owner = 'WIND_FILESYNC') t4
ON t.table_name = t4.table_name
AND t.column_name = t4.column_name
WHERE t.owner = 'WIND_FILESYNC'
AND T.TABLE_NAME NOT LIKE '%TB_OBJECT%'
AND T.TABLE_NAME NOT LIKE '%WINDHASH%'
AND T.TABLE_NAME NOT LIKE '%TEST%'
AND T.TABLE_NAME NOT LIKE '%==%'
"""
cursor.execute(sql)
# 创建Excel工作簿
wb = Workbook()
ws = wb.active
ws.title = "数据库字典"
# 添加表头
headers = [desc[0] for desc in cursor.description]
for col_num, header in enumerate(headers, 1):
ws.cell(row=1, column=col_num, value=header)
# 添加数据
for row_num, row_data in enumerate(cursor, 2):
for col_num, cell_value in enumerate(row_data, 1):
ws.cell(row=row_num, column=col_num, value=cell_value)
# 调整列宽
for col in ws.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = (max_length + 2) * 1.2
ws.column_dimensions[column].width = adjusted_width
# 保存到内存
virtual_workbook = io.BytesIO()
wb.save(virtual_workbook)
virtual_workbook.seek(0)
return send_file(
virtual_workbook,
mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
as_attachment=True,
attachment_filename='数据库字典.xlsx'
)
except Exception as e:
return f"导出字典失败: {str(e)}", 500
if __name__ == '__main__':
#app.config['APPLICATION_ROOT'] = '/qwind'
app.run(host='0.0.0.0', port=9093, debug=True)
我的前后端代码都放到了后端服务器,需要通过前端服务器的nginx进行访问,帮我配置nginx
最新发布