Compressor/backend/app/db.py

import pymysql

from .config import settings


def get_connection():
    return pymysql.connect(
        host=settings.db_host,
        port=settings.db_port,
        user=settings.db_user,
        password=settings.db_password,
        database=settings.db_name,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
        connect_timeout=settings.db_connect_timeout,
        read_timeout=30,
        write_timeout=30,
        autocommit=True,
    )


_ANNOTATION_TABLE_DDL = """
CREATE TABLE IF NOT EXISTS wave_annotation (
    id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '标注ID',
    wave_file_id       BIGINT UNSIGNED NOT NULL COMMENT '所属波形文件ID（wave_file.id）',
    label              VARCHAR(8)      NOT NULL COMMENT '样本类型：正常 / 异常',
    period_start       INT             NOT NULL COMMENT '起始周期编号',
    period_end         INT             NOT NULL COMMENT '结束周期编号（含）',
    sample_index_start INT             NOT NULL COMMENT '起始采样点索引',
    sample_index_end   INT             NOT NULL COMMENT '结束采样点索引（含）',
    created_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    KEY idx_wave_file (wave_file_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '波形标注索引表';
"""


def ensure_annotation_table() -> None:
    with get_connection() as connection:
        with connection.cursor() as cursor:
            cursor.execute(_ANNOTATION_TABLE_DDL)