59 lines
2.0 KiB
Python
59 lines
2.0 KiB
Python
import pymysql
|
|
|
|
conn = pymysql.connect(host='localhost', user='root', password='', database='tracerstudy_db', port=3306, charset='utf8mb4')
|
|
cursor = conn.cursor()
|
|
|
|
# 1. Update Database Schema for tracer_responses
|
|
print("=== UPDATING DATABASE SCHEMA ===")
|
|
|
|
# Check if 'nim' column exists in tracer_responses
|
|
cursor.execute("SHOW COLUMNS FROM tracer_responses LIKE 'nim'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE tracer_responses ADD COLUMN nim VARCHAR(50) NULL AFTER alumni_id, ADD INDEX (nim);")
|
|
print("Added 'nim' column to tracer_responses.")
|
|
else:
|
|
print("'nim' column already exists in tracer_responses.")
|
|
|
|
# Widen columns that can overflow
|
|
alter_cols = [
|
|
("jumlah_perusahaan", "TEXT NULL"),
|
|
("dapet_kerja_6_bulan", "VARCHAR(255) NULL"),
|
|
("tingkatan_tempat_kerja", "VARCHAR(255) NULL"),
|
|
("telepon", "VARCHAR(50) NULL"),
|
|
("nik", "VARCHAR(50) NULL"),
|
|
("npwp", "VARCHAR(50) NULL"),
|
|
("status_saat_ini", "VARCHAR(255) NULL"),
|
|
("kerja_tidak_sesuai_1", "TEXT NULL"),
|
|
("kerja_tidak_sesuai_2", "TEXT NULL"),
|
|
] + [(f"alasan_tidak_sesuai_{i}", "TEXT NULL") for i in range(1, 12)]
|
|
|
|
for col, col_type in alter_cols:
|
|
try:
|
|
cursor.execute(f"ALTER TABLE tracer_responses MODIFY COLUMN {col} {col_type};")
|
|
print(f"Modified column '{col}' to {col_type}")
|
|
except Exception as e:
|
|
print(f"Failed to modify '{col}': {e}")
|
|
|
|
# 2. Truncate all tables
|
|
print("\n=== TRUNCATING DATA TABLES ===")
|
|
cursor.execute('SET FOREIGN_KEY_CHECKS = 0')
|
|
|
|
tables = ['clustering_result', 'tracer_responses', 'kompetensi', 'pekerjaan', 'alumni']
|
|
for table in tables:
|
|
cursor.execute(f'TRUNCATE TABLE {table}')
|
|
print(f"Truncated: {table}")
|
|
|
|
cursor.execute('SET FOREIGN_KEY_CHECKS = 1')
|
|
conn.commit()
|
|
|
|
# 3. Verification
|
|
print("\n=== VERIFIKASI SETELAH TRUNCATE ===")
|
|
for table in tables:
|
|
row = cursor.fetchone()
|
|
count = row[0] if row else 0
|
|
print(f" {table}: {count} rows")
|
|
|
|
cursor.close()
|
|
conn.close()
|
|
print("\nSemua data berhasil dibersihkan dan skema database berhasil diperbarui!")
|