DBを構築し、PythonでDB管理のGUIを作成する方法

フォルダ構成図

project_folder/
│
├── app.py
├── example.db
|── .env
├── templates/
│   ├── index.html
│   ├── show_data.html
│   └── filter_results.html
├── static/
│   └── css/
│       └── styles.css
└── uploads/ (optional, for storing uploaded CSV files)

app.py

import os
import sqlite3
from openai import OpenAI
import pandas as pd
from dotenv import load_dotenv
import numpy as np
import time
from flask import Flask, render_template, request, redirect, url_for, flash, make_response

load_dotenv()

app = Flask(__name__)
app.secret_key = 'supersecretkey'

# データベース設定
DATABASE_PATH = 'example.db'

# set up LLM
# 回答用
model = os.environ["ADA2_DEPLOY_NAME"]
answer_tokens = 4096
client = OpenAI(
    api_key=os.environ["OPENAI_API_KEY"],
    base_url=os.environ["OPENAI_API_BASE"],
    api_version=os.environ["OPENAI_API_VERSION"],
)

def vectlize(text):
    vec = client.Embedding.create(
        model=model,
        input=text,
    )['data'][0]['embedding']
    return np.array(vec)

# コサイン類似度計算用
def cos_sim(v1, v2):
    return np.dot(v1, v2) / (np.linalg.norm(v1) * np.linalg.norm(v2))

max_context = 7000  # トークン数上限回避のため、contextのmaxを設定

# データベースからデータを読み込む関数
def load_db_data():
    conn = sqlite3.connect(DATABASE_PATH)
    cursor = conn.cursor()
    cursor.execute("SELECT inquiry, response, embedding FROM records WHERE response IS NOT NULL")
    rows = cursor.fetchall()
    conn.close()
    data = pd.DataFrame(rows, columns=["問合せ", "回答", "embedding"])
    data['embedding'] = data['embedding'].apply(eval).apply(np.array)
    return data

page_summary_system_prompt = """
        あなたは世界中で信頼されているQAシステムです。
        事前知識ではなく、常に提供されたコンテキスト情報を使用してクエリに回答してください。
        従うべきいくつかのルール:
        ・ 回答内で指定されたコンテキストを直接参照しないでください。
        ・「コンテキストに基づいて、...」や「コンテキスト情報は...」、またはそれに類するような記述は避けてください。
"""

page_summary_user_prompt = """
        コンテキスト情報は以下の通りです。
        ---------------------
        {context_str}
        ---------------------
        事前知識ではなくコンテキスト情報を考慮して、クエリに答えてください。
        Query: {query_str}
        Answer: 
"""

def simirarity_search(text_embedding, question_embedding):
    return cos_sim(text_embedding, question_embedding)

def query_return(query, system_prompt, gpt_model, access_time, ip, n_of_reference_docs):
    start = time.time()
    question_embedding = vectlize(query)
    n_context = n_of_reference_docs
    
    db = load_db_data()  # データベースからデータを読み込む
    # 質問文との類似度検索
    db['質問文との類似度'] = db['embedding'].apply(lambda x: simirarity_search(x, question_embedding))
    db = db.sort_values('質問文との類似度', ascending=False)

    # コンテキスト情報作成
    context_str = '\n'.join(db['回答'][0:n_context].tolist())
    user_message = page_summary_user_prompt.format(context_str=context_str[0:max_context], query_str=query)
    message = [
        {"role": "system", "content": page_summary_system_prompt + system_prompt},
        {"role": "user", "content": user_message}
    ]
    
    response = client.ChatCompletion.create(
        model=gpt_model,
        messages=message,
        max_tokens=answer_tokens,
        temperature=0,
    )

    sim_question_list = list(db.head(n_context)['問合せ'].values)
    sim_answer_list = list(db.head(n_context)['回答'].values)
    sim_score_list = list(db.head(n_context)['質問文との類似度'])
    source_nodes_list = db[['問合せ', '回答', '質問文との類似度']].head(n_context).values.tolist()
    end = time.time()
    time_diff = end - start

    # ログの作成 （log_rowは保存などを行うためのもので、ここではprintします）
    log_row = [ip, access_time, query, response.choices[0].message.content, gpt_model, time_diff] + sim_question_list + sim_answer_list + sim_score_list
    print(log_row)  # 実際にはファイルに保存するなどの処理を行います。

    return {
        'final_answer': response.choices[0].message.content,
        'source_node': source_nodes_list,
    }

def init_db():
    conn = sqlite3.connect(DATABASE_PATH)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            inquiry TEXT NOT NULL,
            response TEXT NOT NULL,
            created_at TEXT NOT NULL,
            inquiry_type TEXT,
            embedding TEXT
        )
    ''')

    # テーブルスキーマ情報を取得してチェック
    cursor.execute("PRAGMA table_info(records)")
    columns = [column_info[1] for column_info in cursor.fetchall()]  # カラム名のリストを取得

    # 必要なカラムが存在しない場合のみ追加
    if 'created_at' not in columns:
        cursor.execute('ALTER TABLE records ADD COLUMN created_at TEXT NOT NULL DEFAULT "2023-01-01"')
    if 'inquiry_type' not in columns:
        cursor.execute('ALTER TABLE records ADD COLUMN inquiry_type TEXT')
    if 'embedding' not in columns:
        cursor.execute('ALTER TABLE records ADD COLUMN embedding TEXT')

    conn.commit()
    conn.close()

def run_query(query, parameters=()):
    conn = sqlite3.connect(DATABASE_PATH)
    cursor = conn.cursor()
    result = cursor.execute(query, parameters)
    conn.commit()
    conn.close()
    return result

def log_action(action):
    with open('action_log.txt', 'a') as log_file:
        log_file.write(f'{datetime.now()}: {action}\n')

@app.route('/')
def index():
    conn = sqlite3.connect(DATABASE_PATH)
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM records LIMIT 10')
    rows = cursor.fetchall()
    conn.close()

    backups = [f for f in os.listdir() if f.startswith('backup') and f.endswith('.db')]
    return render_template('index.html', rows=rows, backups=backups)

@app.route('/add', methods=['POST'])
def add():
    inquiry = request.form['inquiry']
    response = request.form['response']
    created_at = datetime.now().strftime("%Y-%m-%d")
    inquiry_type = request.form['inquiry_type']
    embedding = vectlize(inquiry).tolist()
    run_query('INSERT INTO records (inquiry, response, created_at, inquiry_type, embedding) VALUES (?, ?, ?, ?, ?)', (inquiry, response, created_at, inquiry_type, str(embedding)))
    log_action(f'レコード追加: {inquiry} - {response}')
    flash('レコードが追加されました')
    return redirect(url_for('index'))

@app.route('/delete/<int:id>', methods=['GET'])
def delete(id):
    run_query('DELETE FROM records WHERE id = ?', (id,))
    log_action(f'レコード削除: ID {id}')
    flash('レコードが削除されました')
    return redirect(url_for('index'))

@app.route('/update/<int:id>', methods=['POST'])
def update(id):
    inquiry = request.form['inquiry']
    response = request.form['response']
    created_at = request.form['created_at']
    inquiry_type = request.form['inquiry_type']
    embedding = vectlize(inquiry).tolist()
    run_query('UPDATE records SET inquiry = ?, response = ?, created_at = ?, inquiry_type = ?, embedding = ? WHERE id = ?', (inquiry, response, created_at, inquiry_type, str(embedding), id))
    log_action(f'レコード更新: ID {id}')
    flash('レコードが更新されました')
    return redirect(url_for('index'))

@app.route('/filter', methods=['GET'])
def filter():
    search_term_inquiry = request.args.get('search_term_inquiry', '')
    search_term_response = request.args.get('search_term_response', '')
    filter_start_date = request.args.get('filter_start_date', '')
    filter_end_date = request.args.get('filter_end_date', '')
    filter_inquiry_type = request.args.get('filter_inquiry_type', '')

    query = "SELECT * FROM records WHERE 1=1"
    parameters = []
    
    if search_term_inquiry:
        query += " AND inquiry LIKE ?"
        parameters.append(f'%{search_term_inquiry}%')
    if search_term_response:
        query += " AND response LIKE ?"
        parameters.append(f'%{search_term_response}%')
    if filter_start_date:
        query += " AND created_at >= ?"
        parameters.append(filter_start_date)
    if filter_end_date:
        query += " AND created_at <= ?"
        parameters.append(filter_end_date)
    if filter_inquiry_type:
        query += " AND inquiry_type LIKE ?"
        parameters.append(f'%{filter_inquiry_type}%')
    
    conn = sqlite3.connect(DATABASE_PATH)
    cursor = conn.cursor()
    cursor.execute(query, parameters)
    rows = cursor.fetchall()
    conn.close()
    
    return render_template('filter_results.html', rows=rows)

@app.route('/import_csv', methods=['POST'])
def import_csv():
    files = request.files.getlist('file')  # 複数ファイルを取得
    for file in files:
        if file and file.filename.endswith('.csv'):
            file_path = os.path.join('uploads', file.filename)
            file.save(file_path)
            with open(file_path, 'r', encoding='utf-8') as csvfile:
                reader = csv.reader(csvfile)
                next(reader)  # Skip header row
                for row in reader:
                    if len(row) >= 4:
                        inquiry = row[0]
                        response = row[1]
                        created_at = row[2]
                        inquiry_type = row[3]
                        embedding = vectlize(inquiry).tolist()
                        run_query('INSERT INTO records (inquiry, response, created_at, inquiry_type, embedding) VALUES (?, ?, ?, ?, ?)', (inquiry, response, created_at, inquiry_type, str(embedding)))
            log_action(f'CSVインポート: {file.filename}')
    flash('CSVファイルがインポートされました')
    return redirect(url_for('index'))

@app.route('/export_csv', methods=['GET'])
def export_csv():
    filter_start_date = request.args.get('filter_start_date', '')
    filter_end_date = request.args.get('filter_end_date', '')
    search_term_inquiry = request.args.get('search_term_inquiry', '')
    search_term_response = request.args.get('search_term_response', '')
    filter_inquiry_type = request.args.get('filter_inquiry_type', '')

    query = "SELECT * FROM records WHERE 1=1"
    parameters = []
    
    if search_term_inquiry:
        query += " AND inquiry LIKE ?"
        parameters.append(f'%{search_term_inquiry}%')
    if search_term_response:
        query += " AND response LIKE ?"
        parameters.append(f'%{search_term_response}%')
    if filter_start_date:
        query += " AND created_at >= ?"
        parameters.append(filter_start_date)
    if filter_end_date:
        query += " AND created_at <= ?"
        parameters.append(filter_end_date)
    if filter_inquiry_type:
        query += " AND inquiry_type LIKE ?"
        parameters.append(f'%{filter_inquiry_type}%')
    
    conn = sqlite3.connect(DATABASE_PATH)
    cursor = conn.cursor()
    cursor.execute(query, parameters)
    rows = cursor.fetchall()
    conn.close()

    si = StringIO()
    si.write('\ufeff')  # BOMの追加
    writer = csv.writer(si)
    writer.writerow(['Inquiry', 'Response', 'Created_At', 'Inquiry_Type'])
    writer.writerows(rows)

    output = make_response(si.getvalue())
    output.headers['Content-Disposition'] = 'attachment; filename=export_utf8.csv'
    output.headers['Content-type'] = 'text/csv; charset=utf-8'
    log_action('CSVエクスポート')
    return output

@app.route('/backup', methods=['GET'])
def backup():
    backup_db()
    flash('データベースバックアップが作成されました')
    return redirect(url_for('index'))

@app.route('/rollback', methods=['POST'])
def rollback():
    backup_file = request.form['backup_file']
    if os.path.exists(backup_file):
        shutil.copy(backup_file, DATABASE_PATH)
        flash('データベースがロールバックされました')
    else:
        flash('指定されたバックアップファイルが見つかりませんでした')
    return redirect(url_for('index'))

@app.route('/show_all_data', methods=['GET'])
def show_all_data():
    filter_start_date = request.args.get('filter_start_date', '')
    filter_end_date = request.args.get('filter_end_date', '')
    search_term_inquiry = request.args.get('search_term_inquiry', '')
    search_term_response = request.args.get('search_term_response', '')
    filter_inquiry_type = request.args.get('filter_inquiry_type', '')

    query = "SELECT * FROM records WHERE 1=1"
    parameters = []
    
    if search_term_inquiry:
        query += " AND inquiry LIKE ?"
        parameters.append(f'%{search_term_inquiry}%')
    if search_term_response:
        query += " AND response LIKE ?"
        parameters.append(f'%{search_term_response}%')
    if filter_start_date:
        query += " AND created_at >= ?"
        parameters.append(filter_start_date)
    if filter_end_date:
        query += " AND created_at <= ?"
        parameters.append(filter_end_date)
    if filter_inquiry_type:
        query += " AND inquiry_type LIKE ?"
        parameters.append(f'%{filter_inquiry_type}%')
    
    conn = sqlite3.connect(DATABASE_PATH)
    cursor = conn.cursor()
    cursor.execute(query, parameters)
    rows = cursor.fetchall()
    conn.close()

    return render_template('show_data.html', rows=rows)

@app.route('/refresh_db', methods=['GET'])
def refresh_db():
    conn = sqlite3.connect(DATABASE_PATH)
    cursor = conn.cursor()
    conn.commit()
    conn.close()
    flash('データベースが更新されました')
    return redirect(url_for('index'))

@app.route('/remove_duplicates', methods=['POST'])
def remove_duplicates():
    conn = sqlite3.connect(DATABASE_PATH)
    cursor = conn.cursor()
    cursor.execute('''
        DELETE FROM records
        WHERE rowid NOT IN (
            SELECT MAX(rowid)
            FROM records
            GROUP BY inquiry, response
        )
    ''')
    conn.commit()
    conn.close()
    flash('重複データが削除されました')
    return redirect(url_for('index'))

def backup_db():
    conn = sqlite3.connect(DATABASE_PATH)
    shutil.copy(DATABASE_PATH, f"backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}.db")
    conn.close()

if __name__ == '__main__':
    os.makedirs('uploads', exist_ok=True)
    init_db()
    app.run(debug=True)

filter_results.html

<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Inquiry</th>
            <th>Response</th>
            <th>Created At</th>
            <th>Inquiry Type</th>
        </tr>
    </thead>
    <tbody>
        {% for row in rows %}
        <tr>
            <td>{{ row[0] }}</td>
            <td>{{ row[1] }}</td>
            <td>{{ row[2] }}</td>
            <td>{{ row[3] }}</td>
            <td>{{ row[4] }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

index.html

<!doctype html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQLite Web App</title>
    <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" rel="stylesheet">
    <link href="{{ url_for('static', filename='css/styles.css') }}" rel="stylesheet">
    <link href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" rel="stylesheet">
</head>
<body>
    <div class="container-fluid py-4">
        <h1 class="my-4 text-center" style="color: #0000ff;">SQLite Web App</h1>

        <form id="search-filter-form" class="mb-4">
            <div class="form-group">
                <input type="text" class="form-control filter-input" id="search-term-inquiry" name="search_term_inquiry" placeholder="検索キーワード（問い合わせ内容）">
            </div>
            <div class="form-group mb-4">
                <input type="text" class="form-control filter-input" id="search-term-response" name="search_term_response" placeholder="検索キーワード（回答内容）">
            </div>
            <button type="button" class="btn btn-primary mb-4" style="background-color: #0000ff; width: 5cm;" onclick="searchRecords()">検索</button>

            <div class="form-group d-flex align-items-center">
                <input type="text" id="filter-start-date" class="form-control datepicker" name="filter_start_date" placeholder="開始日" style="width: 5cm;">
                <span class="mx-2">〜</span>
                <input type="text" id="filter-end-date" class="form-control datepicker" name="filter_end_date" placeholder="終了日" style="width: 5cm;">
            </div>

            <div class="form-group">
                <input type="text" class="form-control" id="filter-inquiry-type" name="filter_inquiry_type" placeholder="問い合わせ種類" style="width: calc(10cm - 4cm);">
            </div>
            <button type="button" class="btn btn-primary mb-4" style="background-color: #0000ff; width: 5cm;" onclick="filterRecords()">フィルター</button>
        </form>

        <form method="POST" action="/add" class="mb-4">
            <div class="form-group">
                <input type="text" class="form-control filter-input" name="inquiry" placeholder="問い合わせ内容" required>
            </div>
            <div class="form-group">
                <input type="text" class="form-control filter-input" name="response" placeholder="回答内容" required>
            </div>
            <div class="form-group">
                <input type="text" class="form-control filter-input" name="inquiry_type" placeholder="問い合わせ種類" required>
            </div>
            <button type="submit" class="btn btn-primary mb-4" style="background-color: #0000ff; width: 5cm;">レコード追加</button>
        </form>

        <form method="POST" action="/import_csv" enctype="multipart/form-data" class="mb-4">
            <div class="form-group">
                <input type="file" class="form-control-file" name="file" accept=".csv" multiple>
            </div>
            <button type="submit" class="btn btn-primary mb-4" style="background-color: #0000ff; width: 5cm;">CSVをインポート</button>
        </form>

        <form method="GET" action="/export_csv" class="mb-4">
            <button type="submit" class="btn btn-primary mb-4" style="background-color: #0000ff; width: 5cm;">CSVにエクスポート</button>
        </form>

        <h2 class="my-4 text-secondary">レコード</h2>
        <table class="table table-bordered table-striped table-hover">
            <thead class="thead-dark">
                <tr>
                    <th>ID</th>
                    <th>問い合わせ内容</th>
                    <th class="response-short">回答内容</th>
                    <th>問い合わせ種類</th>
                    <th>日時</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
                {% for row in rows %}
                <tr>
                    <td>{{ row[0] }}</td>
                    <td>{{ row[1] }}</td>
                    <td class="response-short">{{ row[2] }}</td>
                    <td>{{ row[4] }}</td>
                    <td>{{ row[3] }}</td>
                    <td>
                        <div class="btn-group">
                            <a href="/delete/{{ row[0] }}" class="btn btn-danger" style="background-color: #0000ff;">削除</a>
                            <button type="button" class="btn btn-success" onclick="showUpdateModal('{{ row[0] }}', '{{ row[1] }}', '{{ row[2] }}', '{{ row[3] }}', '{{ row[4] }}')" style="background-color: #0000ff;">更新</button>
                        </div>
                    </td>
                </tr>
                {% endfor %}
            </tbody>
        </table>

        <form method="GET" action="/show_all_data" class="mb-4">
            <button type="button" class="btn btn-primary mb-4" style="background-color: #0000ff; width: 5cm;" onclick="showAllData()">データの表示</button>
        </form>

        <form method="POST" action="/remove_duplicates" class="mb-4">
            <button type="submit" class="btn btn-primary mb-4" style="background-color: #0000ff; width: 5cm;">重複データ削除</button>
        </form>

        <h2 class="my-4 text-secondary">バックアップファイル</h2>
        <a href="/backup" class="btn btn-dark btn-block mt-4" style="background-color: #0000ff; width: 5cm;">DBをバックアップ</a>

        <h2 class="my-4 text-secondary">ロールバック</h2>
        <form method="POST" action="/rollback">
            <div class="form-group">
                <label for="backup_file">バックアップファイルを選択</label>
                <select class="form-control" id="backup_file" name="backup_file">
                    {% for backup in backups %}
                    <option value="{{ backup }}">{{ backup }}</option>
                    {% endfor %}
                </select>
            </div>
            <button type="submit" class="btn btn-primary mb-4" style="background-color: #0000ff; width: 5cm;">ロールバック</button>
        </form>

        <!-- 更新モーダル -->
        <div class="modal fade" id="updateModal" tabindex="-1" role="dialog" aria-labelledby="updateModalLabel" aria-hidden="true">
            <div class="modal-dialog" role="document">
                <div class="modal-content">
                    <form method="POST" action="/update">
                        <div class="modal-header">
                            <h5 class="modal-title" id="updateModalLabel">レコードを更新</h5>
                            <button type="button" class="close" data-dismiss="modal" aria-label="閉じる">
                                <span aria-hidden="true">&times;</span>
                            </button>
                        </div>
                        <div class="modal-body">
                            <input type="hidden" id="update-id" name="id">
                            <div class="form-group">
                                <label for="update-inquiry">問い合わせ内容</label>
                                <input type="text" class="form-control" id="update-inquiry" name="inquiry" required>
                            </div>
                            <div class="form-group">
                                <label for="update-response">回答内容</label>
                                <input type="text" class="form-control" id="update-response" name="response" required>
                            </div>
                            <div class="form-group">
                                <label for="update-inquiry-type">問い合わせ種類</label>
                                <input type="text" class="form-control" id="update-inquiry-type" name="inquiry_type" required>
                            </div>
                            <div class="form-group">
                                <label for="update-date">日時</label>
                                <input type="text" class="form-control datepicker" id="update-date" name="created_at" required>
                            </div>
                        </div>
                        <div class="modal-footer">
                            <button type="button" class="btn btn-secondary" data-dismiss="modal">閉じる</button>
                            <button type="submit" class="btn btn-primary" style="background-color: #0000ff;">変更を保存</button>
                        </div>
                    </form>
                </div>
            </div>
        </div>

        <!-- データ表示モーダル -->
        <div class="modal fade" id="dataModal" tabindex="-1" role="dialog" aria-labelledby="dataModalLabel" aria-hidden="true">
            <div class="modal-dialog modal-lg" role="document">
                <div class="modal-content">
                    <div class="modal-header">
                        <h5 class="modal-title" id="dataModalLabel">データの表示</h5>
                        <button type="button" class="close" data-dismiss="modal" aria-label="閉じる">
                            <span aria-hidden="true">&times;</span>
                        </button>
                    </div>
                    <div class="modal-body">
                        <table class="table table-bordered table-striped table-hover">
                            <thead class="thead-dark">
                                <tr>
                                    <th>ID</th>
                                    <th>問い合わせ内容</th>
                                    <th>回答内容</th>
                                    <th>日時</th>
                                    <th>問い合わせ種類</th>
                                </tr>
                            </thead>
                            <tbody id="dataTableBody">
                                <!-- データ行がここに表示される -->
                            </tbody>
                        </table>

                        <!-- ページネーションリンク -->
                        <nav aria-label="Page navigation">
                            <ul class="pagination justify-content-center" id="pagination">
                                <!-- ページネーションリンクがここに表示される -->
                            </ul>
                        </nav>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-dismiss="modal">閉じる</button>
                    </div>
                </div>
            </div>
        </div>

        <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.2/dist/js/bootstrap.bundle.min.js"></script>
        <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
        <script>
            $(function() {
                $(".datepicker").datepicker({
                    dateFormat: "yy-mm-dd"
                });
            });

            function showUpdateModal(id, inquiry, response, created_at, inquiry_type) {
                $('#update-id').val(id);
                $('#update-inquiry').val(inquiry);
                $('#update-response').val(response);
                $('#update-inquiry-type').val(inquiry_type);
                $('#update-date').val(created_at);
                $('#updateModal').modal('show');
            }

            function searchRecords() {
                let inquiry = $('#search-term-inquiry').val();
                let response = $('#search-term-response').val();
                $.ajax({
                    url: '/filter',
                    method: 'GET',
                    data: {
                        search_term_inquiry: inquiry,
                        search_term_response: response,
                        filter_start_date: '',
                        filter_end_date: '',
                        filter_inquiry_type: ''
                    },
                    success: function(data) {
                        $('#searchResultsContent').html(data);
                        $('#searchResultsModal').modal('show');
                    }
                });
            }

            function filterRecords() {
                let start_date = $('#filter-start-date').val();
                let end_date = $('#filter-end-date').val();
                let inquiry_type = $('#filter-inquiry-type').val();
                $.ajax({
                    url: '/filter',
                    method: 'GET',
                    data: {
                        search_term_inquiry: '',
                        search_term_response: '',
                        filter_start_date: start_date,
                        filter_end_date: end_date,
                        filter_inquiry_type: inquiry_type
                    },
                    success: function(data) {
                        $('#searchResultsContent').html(data);
                        $('#searchResultsModal').modal('show');
                    }
                });
            }

            function showAllData(page = 1) {
                $.ajax({
                    url: "/show_all_data",
                    type: "GET",
                    data: { page: page },
                    success: function(data) {
                        const rows = data.rows;
                        const dataTableBody = $('#dataTableBody');
                        dataTableBody.empty();
                        rows.forEach(function(row) {
                            const tr = `<tr>
                                <td>${row[0]}</td>
                                <td>${row[1]}</td>
                                <td>${row[2]}</td>
                                <td>${row[3]}</td>
                                <td>${row[4]}</td>
                            </tr>`;
                            dataTableBody.append(tr);
                        });

                        const pagination = $('#pagination');
                        pagination.empty();
                        if (data.page > 1) {
                            pagination.append(`<li class="page-item"><a class="page-link" href="javascript:void(0);" onclick="showAllData(${data.page - 1})">&laquo;</a></li>`);
                        }
                        for (let p = 1; p <= Math.ceil(data.total_rows / data.per_page); p++) {
                            const activeClass = p === data.page ? 'active' : '';
                            pagination.append(`<li class="page-item ${activeClass}"><a class="page-link" href="javascript:void(0);" onclick="showAllData(${p})">${p}</a></li>`);
                        }
                        if (data.page < Math.ceil(data.total_rows / data.per_page)) {
                            pagination.append(`<li class="page-item"><a class="page-link" href="javascript:void(0);" onclick="showAllData(${data.page + 1})">&raquo;</a></li>`);
                        }

                        $('#dataModal').modal('show');
                    }
                });
            }
        </script>
    </div>
</body>
</html>

show_data.html

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>データの表示</title>
    <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container">
        <h2 class="my-4">データの表示</h2>

        <table class="table table-bordered table-striped table-hover">
            <thead class="thead-dark">
                <tr>
                    <th>ID</th>
                    <th>問い合わせ内容</th>
                    <th>回答内容</th>
                    <th>日時</th>
                    <th>問い合わせ種類</th>
                </tr>
            </thead>
            <tbody>
                {% for row in rows %}
                <tr>
                    <td>{{ row[0] }}</td>
                    <td>{{ row[1] }}</td>
                    <td>{{ row[2] }}</td>
                    <td>{{ row[3] }}</td>
                    <td>{{ row[4] }}</td>
                </tr>
                {% endfor %}
            </tbody>
        </table>

        <!-- ページネーションリンク -->
        <nav aria-label="Page navigation">
            <ul class="pagination">
                {% if page > 1 %}
                <li class="page-item">
                    <a class="page-link" href="{{ url_for('show_all_data', page=page-1) }}" aria-label="Previous">
                        <span aria-hidden="true">&laquo;</span>
                    </a>
                </li>
                {% endif %}
                {% for p in range(1, (total_rows // per_page) + 2) %}
                <li class="page-item {% if p == page %}active{% endif %}"><a class="page-link" href="{{ url_for('show_all_data', page=p) }}">{{ p }}</a></li>
                {% endfor %}
                {% if page < (total_rows // per_page) + 1 %}
                <li class="page-item">
                    <a class="page-link" href="{{ url_for('show_all_data', page=page+1) }}" aria-label="Next">
                        <span aria-hidden="true">&raquo;</span>
                    </a>
                </li>
                {% endif %}
            </ul>
        </nav>
    </div>

    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.2/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

.env

ADA2_DEPLOY_NAME=your_model_name
OPENAI_API_KEY=your_openai_api_key
OPENAI_API_BASE=your_openai_api_endpoint
OPENAI_API_VERSION=your_openai_api_version

styles.css

body {
    background-color: #ffffff;
    font-family: 'Arial', sans-serif;
}

.container-fluid {
    background: #fff;
    padding: 20px;
    border-radius: 10px;
    box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
}

h1, h2 {
    font-weight: bold;
    text-align: center;
    color: #0000ff; /* タイトルを青色に変更 */
    margin-bottom: 20px;
}

.table th, .table td {
    vertical-align: middle;
    border-color: #000000; /* 枠線を黒くする */
}

.btn {
    margin-bottom: 5px;
    border-width: 2px;
    border-color: #000000; /* 枠線を黒くする */
    background-color: #0000ff; /* ボタンの色を青色に変更 */
    text-align: left;        /* ボタンのテキストを左寄せに */
    padding: 5px 10px;
    width: 5cm;              /* 幅を5cmに設定 */
}

.btn-primary, .btn-secondary, .btn-success, .btn-danger, .btn-warning, .btn-info, .btn-dark {
    border-color: #000000; /* 枠線を黒くする */
}

.filter-input {
    width: 60%; /* 横幅を調整 */
}

/* フィルターデータ表示用 */
.datepicker {
    width: 5cm; /* 幅を5cmに設定 */
}

/* レスポンス内容の列の幅をさらに縮小 */
.response-short {
    max-width: 150px; /* 現在の幅の3/4に調整 */
    word-wrap: break-word;
}