1021 lines
22 KiB
Python
1021 lines
22 KiB
Python
import json
|
|
import os
|
|
|
|
import mysql.connector
|
|
from mysql.connector import Error
|
|
|
|
|
|
# ============================================================
|
|
# KONFIGURASI DATABASE MYSQL
|
|
# ============================================================
|
|
|
|
DB_CONFIG = {
|
|
"host": os.getenv("DB_HOST", "127.0.0.1"),
|
|
"port": int(os.getenv("DB_PORT", "3306")),
|
|
"user": os.getenv("DB_USER", "root"),
|
|
"password": os.getenv("DB_PASSWORD", ""),
|
|
"database": os.getenv("DB_NAME", "slearn_web_db"),
|
|
"charset": "utf8mb4"
|
|
}
|
|
|
|
|
|
# ============================================================
|
|
# HELPER JSON
|
|
# ============================================================
|
|
|
|
def _to_json(data):
|
|
"""
|
|
Mengubah list/dict menjadi JSON string
|
|
sebelum disimpan ke database.
|
|
"""
|
|
|
|
if data is None:
|
|
return None
|
|
|
|
if isinstance(data, str):
|
|
return data
|
|
|
|
return json.dumps(
|
|
data,
|
|
ensure_ascii=False
|
|
)
|
|
|
|
|
|
def _from_json(data, default=None):
|
|
"""
|
|
Membaca JSON string dari database.
|
|
"""
|
|
|
|
if default is None:
|
|
default = []
|
|
|
|
if data is None:
|
|
return default
|
|
|
|
if isinstance(data, (list, dict)):
|
|
return data
|
|
|
|
try:
|
|
return json.loads(data)
|
|
|
|
except (TypeError, json.JSONDecodeError):
|
|
return default
|
|
|
|
|
|
def _format_datetime(value):
|
|
"""
|
|
Mengubah datetime MySQL menjadi string ISO.
|
|
"""
|
|
|
|
if value is None:
|
|
return None
|
|
|
|
return value.isoformat(
|
|
timespec="seconds"
|
|
)
|
|
|
|
|
|
# ============================================================
|
|
# MEMBUAT KONEKSI DATABASE
|
|
# ============================================================
|
|
|
|
def get_db_connection():
|
|
|
|
try:
|
|
connection = mysql.connector.connect(
|
|
**DB_CONFIG
|
|
)
|
|
|
|
if not connection.is_connected():
|
|
raise ConnectionError(
|
|
"Koneksi ke database MySQL tidak aktif."
|
|
)
|
|
|
|
return connection
|
|
|
|
except Error as error:
|
|
raise ConnectionError(
|
|
f"Gagal terhubung ke MySQL: {error}"
|
|
) from error
|
|
|
|
|
|
# ============================================================
|
|
# TEST KONEKSI DATABASE
|
|
# ============================================================
|
|
|
|
def test_db_connection():
|
|
|
|
connection = None
|
|
cursor = None
|
|
|
|
try:
|
|
connection = get_db_connection()
|
|
cursor = connection.cursor()
|
|
|
|
cursor.execute(
|
|
"SELECT DATABASE();"
|
|
)
|
|
|
|
database_name = (
|
|
cursor.fetchone()[0]
|
|
)
|
|
|
|
cursor.execute(
|
|
"SHOW TABLES;"
|
|
)
|
|
|
|
tables = [
|
|
row[0]
|
|
for row in cursor.fetchall()
|
|
]
|
|
|
|
return {
|
|
"status": "success",
|
|
"database": database_name,
|
|
"tables": tables
|
|
}
|
|
|
|
finally:
|
|
if cursor is not None:
|
|
cursor.close()
|
|
|
|
if (
|
|
connection is not None
|
|
and connection.is_connected()
|
|
):
|
|
connection.close()
|
|
|
|
|
|
# ============================================================
|
|
# RINGKASAN DATABASE
|
|
# ============================================================
|
|
|
|
def get_database_summary():
|
|
|
|
connection = None
|
|
cursor = None
|
|
|
|
try:
|
|
connection = get_db_connection()
|
|
cursor = connection.cursor()
|
|
|
|
cursor.execute(
|
|
"SELECT COUNT(*) FROM analysis_batches"
|
|
)
|
|
|
|
total_batches = (
|
|
cursor.fetchone()[0]
|
|
)
|
|
|
|
cursor.execute(
|
|
"SELECT COUNT(*) FROM analysis_results"
|
|
)
|
|
|
|
total_results = (
|
|
cursor.fetchone()[0]
|
|
)
|
|
|
|
cursor.execute(
|
|
"""
|
|
SELECT COUNT(*)
|
|
FROM single_prediction_history
|
|
"""
|
|
)
|
|
|
|
total_single_predictions = (
|
|
cursor.fetchone()[0]
|
|
)
|
|
|
|
return {
|
|
"total_batches": int(
|
|
total_batches
|
|
),
|
|
"total_results": int(
|
|
total_results
|
|
),
|
|
"total_single_predictions": int(
|
|
total_single_predictions
|
|
)
|
|
}
|
|
|
|
finally:
|
|
if cursor is not None:
|
|
cursor.close()
|
|
|
|
if (
|
|
connection is not None
|
|
and connection.is_connected()
|
|
):
|
|
connection.close()
|
|
|
|
|
|
# ============================================================
|
|
# SIMPAN SATU BATCH ANALISIS CSV
|
|
# ============================================================
|
|
|
|
def save_analysis_batch(
|
|
batch_id,
|
|
file_name,
|
|
selected_column,
|
|
results
|
|
):
|
|
|
|
connection = None
|
|
cursor = None
|
|
|
|
versions = {item.get("model_version") for item in results}
|
|
if len(versions) != 1 or None in versions:
|
|
raise ValueError("Satu batch harus menggunakan satu versi model yang tercatat.")
|
|
model_version = versions.pop()
|
|
total_data = len(results)
|
|
|
|
total_positive = sum(
|
|
1
|
|
for item in results
|
|
if item["label"] == 1
|
|
)
|
|
|
|
total_negative = sum(
|
|
1
|
|
for item in results
|
|
if item["label"] == 0
|
|
)
|
|
|
|
try:
|
|
connection = get_db_connection()
|
|
cursor = connection.cursor()
|
|
|
|
connection.start_transaction()
|
|
|
|
query_batch = """
|
|
INSERT INTO analysis_batches (
|
|
batch_id,
|
|
file_name,
|
|
selected_column,
|
|
total_data,
|
|
total_positive,
|
|
total_negative, model_version
|
|
)
|
|
VALUES (
|
|
%s, %s, %s,
|
|
%s, %s, %s, %s
|
|
)
|
|
"""
|
|
|
|
cursor.execute(
|
|
query_batch,
|
|
(
|
|
batch_id,
|
|
file_name,
|
|
selected_column,
|
|
total_data,
|
|
total_positive,
|
|
total_negative, model_version
|
|
)
|
|
)
|
|
|
|
query_result = """
|
|
INSERT INTO analysis_results (
|
|
batch_id,
|
|
source_row_number,
|
|
original_text,
|
|
cleaning,
|
|
case_folding,
|
|
normalization,
|
|
tokenizing,
|
|
stopword_removal,
|
|
stemming,
|
|
label,
|
|
sentiment,
|
|
tfidf_top
|
|
)
|
|
VALUES (
|
|
%s, %s, %s, %s,
|
|
%s, %s, %s, %s,
|
|
%s, %s, %s, %s
|
|
)
|
|
"""
|
|
|
|
data_results = []
|
|
|
|
for item in results:
|
|
|
|
preprocessing = (
|
|
item["preprocessing"]
|
|
)
|
|
|
|
data_results.append(
|
|
(
|
|
batch_id,
|
|
item["source_row_number"],
|
|
item["teks"],
|
|
preprocessing["cleaning"],
|
|
preprocessing["case_folding"],
|
|
preprocessing["normalization"],
|
|
_to_json(
|
|
preprocessing["tokenizing"]
|
|
),
|
|
preprocessing["stopword_removal"],
|
|
preprocessing["stemming"],
|
|
item["label"],
|
|
item["sentimen"],
|
|
_to_json(
|
|
item["tfidf_top"]
|
|
)
|
|
)
|
|
)
|
|
|
|
cursor.executemany(
|
|
query_result,
|
|
data_results
|
|
)
|
|
|
|
connection.commit()
|
|
|
|
return {
|
|
"batch_id": batch_id,
|
|
"file_name": file_name,
|
|
"selected_column": selected_column,
|
|
"total_data": total_data,
|
|
"total_positive": total_positive,
|
|
"total_negative": total_negative, "model_version": model_version
|
|
}
|
|
|
|
except Exception:
|
|
|
|
if connection is not None:
|
|
connection.rollback()
|
|
|
|
raise
|
|
|
|
finally:
|
|
if cursor is not None:
|
|
cursor.close()
|
|
|
|
if (
|
|
connection is not None
|
|
and connection.is_connected()
|
|
):
|
|
connection.close()
|
|
|
|
|
|
# ============================================================
|
|
# MEMBACA DETAIL SATU BATCH CSV
|
|
# ============================================================
|
|
|
|
def get_analysis_batch(
|
|
batch_id
|
|
):
|
|
|
|
connection = None
|
|
cursor = None
|
|
|
|
try:
|
|
connection = get_db_connection()
|
|
|
|
cursor = connection.cursor(
|
|
dictionary=True
|
|
)
|
|
|
|
cursor.execute(
|
|
"""
|
|
SELECT
|
|
batch_id,
|
|
file_name,
|
|
selected_column,
|
|
total_data,
|
|
total_positive,
|
|
total_negative,
|
|
model_version,
|
|
created_at
|
|
FROM analysis_batches
|
|
WHERE batch_id = %s
|
|
LIMIT 1
|
|
""",
|
|
(
|
|
batch_id,
|
|
)
|
|
)
|
|
|
|
batch = cursor.fetchone()
|
|
|
|
if batch is None:
|
|
return None
|
|
|
|
cursor.execute(
|
|
"""
|
|
SELECT
|
|
id,
|
|
source_row_number,
|
|
original_text,
|
|
cleaning,
|
|
case_folding,
|
|
normalization,
|
|
tokenizing,
|
|
stopword_removal,
|
|
stemming,
|
|
label,
|
|
sentiment,
|
|
tfidf_top,
|
|
created_at
|
|
FROM analysis_results
|
|
WHERE batch_id = %s
|
|
ORDER BY
|
|
source_row_number ASC,
|
|
id ASC
|
|
""",
|
|
(
|
|
batch_id,
|
|
)
|
|
)
|
|
|
|
rows = cursor.fetchall()
|
|
|
|
results = []
|
|
|
|
for row in rows:
|
|
|
|
tokenizing = _from_json(
|
|
row["tokenizing"],
|
|
[]
|
|
)
|
|
|
|
tfidf_top = _from_json(
|
|
row["tfidf_top"],
|
|
[]
|
|
)
|
|
|
|
results.append(
|
|
{
|
|
"id": int(
|
|
row["id"]
|
|
),
|
|
|
|
"source_row_number": int(
|
|
row["source_row_number"]
|
|
),
|
|
|
|
"teks": (
|
|
row["original_text"]
|
|
),
|
|
|
|
"label": int(
|
|
row["label"]
|
|
),
|
|
|
|
"sentimen": (
|
|
row["sentiment"]
|
|
),
|
|
|
|
"preprocessing": {
|
|
"cleaning":
|
|
row["cleaning"]
|
|
or "",
|
|
|
|
"case_folding":
|
|
row["case_folding"]
|
|
or "",
|
|
|
|
"normalization":
|
|
row["normalization"]
|
|
or "",
|
|
|
|
"tokenizing":
|
|
tokenizing,
|
|
|
|
"stopword_removal":
|
|
row["stopword_removal"]
|
|
or "",
|
|
|
|
"stemming":
|
|
row["stemming"]
|
|
or ""
|
|
},
|
|
|
|
"tfidf_top":
|
|
tfidf_top
|
|
}
|
|
)
|
|
|
|
return {
|
|
"batch_id":
|
|
batch["batch_id"],
|
|
"model_version": batch.get("model_version"),
|
|
|
|
"file_name":
|
|
batch["file_name"],
|
|
|
|
"selected_column":
|
|
batch["selected_column"],
|
|
|
|
"total_data": int(
|
|
batch["total_data"]
|
|
),
|
|
|
|
"total_positive": int(
|
|
batch["total_positive"]
|
|
),
|
|
|
|
"total_negative": int(
|
|
batch["total_negative"]
|
|
),
|
|
|
|
"created_at":
|
|
_format_datetime(
|
|
batch["created_at"]
|
|
),
|
|
|
|
"results":
|
|
results
|
|
}
|
|
|
|
finally:
|
|
if cursor is not None:
|
|
cursor.close()
|
|
|
|
if (
|
|
connection is not None
|
|
and connection.is_connected()
|
|
):
|
|
connection.close()
|
|
|
|
|
|
# ============================================================
|
|
# MEMBACA SEMUA RIWAYAT BATCH CSV
|
|
# ============================================================
|
|
|
|
def get_analysis_history():
|
|
|
|
connection = None
|
|
cursor = None
|
|
|
|
try:
|
|
connection = get_db_connection()
|
|
|
|
cursor = connection.cursor(
|
|
dictionary=True
|
|
)
|
|
|
|
cursor.execute(
|
|
"""
|
|
SELECT
|
|
batch_id,
|
|
file_name,
|
|
selected_column,
|
|
total_data,
|
|
total_positive,
|
|
total_negative,
|
|
model_version,
|
|
created_at
|
|
FROM analysis_batches
|
|
ORDER BY
|
|
created_at DESC,
|
|
id DESC
|
|
"""
|
|
)
|
|
|
|
rows = cursor.fetchall()
|
|
|
|
history = []
|
|
|
|
for row in rows:
|
|
|
|
history.append(
|
|
{
|
|
"batch_id":
|
|
row["batch_id"],
|
|
"model_version": row.get("model_version"),
|
|
|
|
"file_name":
|
|
row["file_name"],
|
|
|
|
"selected_column":
|
|
row["selected_column"],
|
|
|
|
"total_data": int(
|
|
row["total_data"]
|
|
),
|
|
|
|
"total_positive": int(
|
|
row["total_positive"]
|
|
),
|
|
|
|
"total_negative": int(
|
|
row["total_negative"]
|
|
),
|
|
|
|
"created_at":
|
|
_format_datetime(
|
|
row["created_at"]
|
|
)
|
|
}
|
|
)
|
|
|
|
return history
|
|
|
|
finally:
|
|
if cursor is not None:
|
|
cursor.close()
|
|
|
|
if (
|
|
connection is not None
|
|
and connection.is_connected()
|
|
):
|
|
connection.close()
|
|
|
|
|
|
# ============================================================
|
|
# SIMPAN PREDIKSI SATU ULASAN
|
|
# ============================================================
|
|
|
|
def save_single_prediction(
|
|
result
|
|
):
|
|
"""
|
|
Menyimpan satu hasil dari fungsi analisis_teks()
|
|
ke tabel single_prediction_history.
|
|
"""
|
|
|
|
connection = None
|
|
cursor = None
|
|
|
|
preprocessing = (
|
|
result.get(
|
|
"preprocessing",
|
|
{}
|
|
)
|
|
)
|
|
|
|
try:
|
|
connection = get_db_connection()
|
|
cursor = connection.cursor()
|
|
|
|
query = """
|
|
INSERT INTO single_prediction_history (
|
|
original_text,
|
|
cleaning,
|
|
case_folding,
|
|
normalization,
|
|
tokenizing,
|
|
stopword_removal,
|
|
stemming,
|
|
jumlah_fitur_dikenali,
|
|
label,
|
|
sentiment,
|
|
tfidf_top, model_version
|
|
)
|
|
VALUES (
|
|
%s, %s, %s, %s,
|
|
%s, %s, %s, %s,
|
|
%s, %s, %s, %s
|
|
)
|
|
"""
|
|
|
|
values = (
|
|
result.get(
|
|
"teks",
|
|
""
|
|
),
|
|
|
|
preprocessing.get(
|
|
"cleaning",
|
|
""
|
|
),
|
|
|
|
preprocessing.get(
|
|
"case_folding",
|
|
""
|
|
),
|
|
|
|
preprocessing.get(
|
|
"normalization",
|
|
""
|
|
),
|
|
|
|
_to_json(
|
|
preprocessing.get(
|
|
"tokenizing",
|
|
[]
|
|
)
|
|
),
|
|
|
|
preprocessing.get(
|
|
"stopword_removal",
|
|
""
|
|
),
|
|
|
|
preprocessing.get(
|
|
"stemming",
|
|
""
|
|
),
|
|
|
|
int(
|
|
result.get(
|
|
"jumlah_fitur_dikenali",
|
|
0
|
|
)
|
|
),
|
|
|
|
int(
|
|
result["label"]
|
|
),
|
|
|
|
result["sentimen"],
|
|
|
|
_to_json(
|
|
result.get(
|
|
"tfidf_top",
|
|
[]
|
|
)
|
|
)
|
|
)
|
|
|
|
values = (*values, result["model_version"])
|
|
|
|
cursor.execute(
|
|
query,
|
|
values
|
|
)
|
|
|
|
prediction_id = (
|
|
cursor.lastrowid
|
|
)
|
|
|
|
connection.commit()
|
|
|
|
return int(
|
|
prediction_id
|
|
)
|
|
|
|
except Exception:
|
|
|
|
if connection is not None:
|
|
connection.rollback()
|
|
|
|
raise
|
|
|
|
finally:
|
|
if cursor is not None:
|
|
cursor.close()
|
|
|
|
if (
|
|
connection is not None
|
|
and connection.is_connected()
|
|
):
|
|
connection.close()
|
|
|
|
|
|
# ============================================================
|
|
# MEMBACA SEMUA RIWAYAT PREDIKSI SATU ULASAN
|
|
# ============================================================
|
|
|
|
def get_single_prediction_history():
|
|
|
|
connection = None
|
|
cursor = None
|
|
|
|
try:
|
|
connection = get_db_connection()
|
|
|
|
cursor = connection.cursor(
|
|
dictionary=True
|
|
)
|
|
|
|
cursor.execute(
|
|
"""
|
|
SELECT
|
|
id,
|
|
original_text,
|
|
jumlah_fitur_dikenali,
|
|
label,
|
|
sentiment,
|
|
model_version,
|
|
created_at
|
|
FROM single_prediction_history
|
|
ORDER BY
|
|
created_at DESC,
|
|
id DESC
|
|
"""
|
|
)
|
|
|
|
rows = cursor.fetchall()
|
|
|
|
history = []
|
|
|
|
for row in rows:
|
|
|
|
history.append(
|
|
{
|
|
"id": int(
|
|
row["id"]
|
|
),
|
|
|
|
"teks":
|
|
row["original_text"],
|
|
"model_version": row.get("model_version"),
|
|
|
|
"jumlah_fitur_dikenali":
|
|
int(
|
|
row[
|
|
"jumlah_fitur_dikenali"
|
|
]
|
|
or 0
|
|
),
|
|
|
|
"label": int(
|
|
row["label"]
|
|
),
|
|
|
|
"sentimen":
|
|
row["sentiment"],
|
|
|
|
"created_at":
|
|
_format_datetime(
|
|
row["created_at"]
|
|
)
|
|
}
|
|
)
|
|
|
|
return history
|
|
|
|
finally:
|
|
if cursor is not None:
|
|
cursor.close()
|
|
|
|
if (
|
|
connection is not None
|
|
and connection.is_connected()
|
|
):
|
|
connection.close()
|
|
|
|
|
|
# ============================================================
|
|
# MEMBACA DETAIL SATU PREDIKSI
|
|
# ============================================================
|
|
|
|
def get_single_prediction(
|
|
prediction_id
|
|
):
|
|
|
|
connection = None
|
|
cursor = None
|
|
|
|
try:
|
|
connection = get_db_connection()
|
|
|
|
cursor = connection.cursor(
|
|
dictionary=True
|
|
)
|
|
|
|
cursor.execute(
|
|
"""
|
|
SELECT
|
|
id,
|
|
original_text,
|
|
cleaning,
|
|
case_folding,
|
|
normalization,
|
|
tokenizing,
|
|
stopword_removal,
|
|
stemming,
|
|
jumlah_fitur_dikenali,
|
|
label,
|
|
sentiment,
|
|
tfidf_top,
|
|
model_version,
|
|
created_at
|
|
FROM single_prediction_history
|
|
WHERE id = %s
|
|
LIMIT 1
|
|
""",
|
|
(
|
|
prediction_id,
|
|
)
|
|
)
|
|
|
|
row = cursor.fetchone()
|
|
|
|
if row is None:
|
|
return None
|
|
|
|
tokenizing = _from_json(
|
|
row["tokenizing"],
|
|
[]
|
|
)
|
|
|
|
tfidf_top = _from_json(
|
|
row["tfidf_top"],
|
|
[]
|
|
)
|
|
|
|
return {
|
|
"id": int(
|
|
row["id"]
|
|
),
|
|
|
|
"teks":
|
|
row["original_text"],
|
|
"model_version": row.get("model_version"),
|
|
|
|
"label": int(
|
|
row["label"]
|
|
),
|
|
|
|
"sentimen":
|
|
row["sentiment"],
|
|
|
|
"jumlah_fitur_dikenali":
|
|
int(
|
|
row[
|
|
"jumlah_fitur_dikenali"
|
|
]
|
|
or 0
|
|
),
|
|
|
|
"preprocessing": {
|
|
"cleaning":
|
|
row["cleaning"]
|
|
or "",
|
|
|
|
"case_folding":
|
|
row["case_folding"]
|
|
or "",
|
|
|
|
"normalization":
|
|
row["normalization"]
|
|
or "",
|
|
|
|
"tokenizing":
|
|
tokenizing,
|
|
|
|
"stopword_removal":
|
|
row["stopword_removal"]
|
|
or "",
|
|
|
|
"stemming":
|
|
row["stemming"]
|
|
or ""
|
|
},
|
|
|
|
"tfidf_top":
|
|
tfidf_top,
|
|
|
|
"created_at":
|
|
_format_datetime(
|
|
row["created_at"]
|
|
)
|
|
}
|
|
|
|
finally:
|
|
if cursor is not None:
|
|
cursor.close()
|
|
|
|
if (
|
|
connection is not None
|
|
and connection.is_connected()
|
|
):
|
|
connection.close()
|
|
|
|
|
|
# ============================================================
|
|
# TEST LANGSUNG DARI TERMINAL
|
|
# ============================================================
|
|
|
|
if __name__ == "__main__":
|
|
|
|
hasil = test_db_connection()
|
|
|
|
summary = (
|
|
get_database_summary()
|
|
)
|
|
|
|
print(
|
|
"KONEKSI MYSQL BERHASIL"
|
|
)
|
|
|
|
print(
|
|
"Database :",
|
|
hasil["database"]
|
|
)
|
|
|
|
print(
|
|
"Tabel :",
|
|
hasil["tables"]
|
|
)
|
|
|
|
print(
|
|
"Batch :",
|
|
summary["total_batches"]
|
|
)
|
|
|
|
print(
|
|
"Hasil CSV:",
|
|
summary["total_results"]
|
|
)
|
|
|
|
print(
|
|
"Prediksi :",
|
|
summary[
|
|
"total_single_predictions"
|
|
]
|
|
) |