#!/usr/bin/env python3
|
# -*- coding: utf-8 -*-
|
"""从 MySQL delivery_media_snapshot_feedback 导出增量训练 JSONL。"""
|
import argparse
|
import json
|
import os
|
import sys
|
|
try:
|
import pymysql
|
except ImportError:
|
pymysql = None
|
|
|
def main():
|
parser = argparse.ArgumentParser()
|
parser.add_argument("--host", default=os.environ.get("MYSQL_HOST", "127.0.0.1"))
|
parser.add_argument("--port", type=int, default=int(os.environ.get("MYSQL_PORT", "3306")))
|
parser.add_argument("--user", default=os.environ.get("MYSQL_USER", "root"))
|
parser.add_argument("--password", default=os.environ.get("MYSQL_PASSWORD", ""))
|
parser.add_argument("--database", default=os.environ.get("MYSQL_DATABASE", "wuhuyancao"))
|
parser.add_argument("--ftp-prefix", default=os.environ.get("FTP_RESOURCE_PREFIX", "http://127.0.0.1/files"))
|
parser.add_argument("-o", "--output", default="data/feedback_export.jsonl")
|
args = parser.parse_args()
|
|
if pymysql is None:
|
print("请安装 pymysql", file=sys.stderr)
|
sys.exit(1)
|
|
media_folder = os.environ.get("COLLECTION_MEDIA_FOLDER", "/collection_media/")
|
conn = pymysql.connect(
|
host=args.host, port=args.port, user=args.user,
|
password=args.password, database=args.database, charset="utf8mb4",
|
)
|
sql = """
|
SELECT f.media_id, f.snapshot_type, f.ai_time_sec, f.manual_time_sec,
|
m.file_path_local, m.start_time, m.recorder_sn
|
FROM delivery_media_snapshot_feedback f
|
JOIN collection_media m ON m.id = f.media_id AND m.isdeleted = 0
|
WHERE f.isdeleted = 0
|
ORDER BY f.id
|
"""
|
groups = {}
|
with conn.cursor() as cur:
|
cur.execute(sql)
|
for row in cur.fetchall():
|
media_id, snap_type, ai_t, manual_t, path_local, start_time, recorder_sn = row
|
if media_id not in groups:
|
video_url = args.ftp_prefix.rstrip("/") + "/" + media_folder.strip("/") + "/" + (path_local or "").lstrip("/")
|
driver_date = ""
|
if start_time:
|
driver_date = f"{recorder_sn or 'unknown'}_{start_time.strftime('%Y%m%d')}"
|
groups[media_id] = {
|
"media_id": media_id,
|
"video_path": video_url,
|
"storefront_time_sec": None,
|
"handover_time_sec": None,
|
"driver_date": driver_date,
|
"split": "train",
|
"notes": "from_feedback",
|
}
|
if snap_type == 1:
|
groups[media_id]["storefront_time_sec"] = float(manual_t)
|
elif snap_type == 2:
|
groups[media_id]["handover_time_sec"] = float(manual_t)
|
conn.close()
|
|
os.makedirs(os.path.dirname(args.output) or ".", exist_ok=True)
|
count = 0
|
with open(args.output, "w", encoding="utf-8") as out:
|
for item in groups.values():
|
if item["storefront_time_sec"] is None or item["handover_time_sec"] is None:
|
continue
|
out.write(json.dumps(item, ensure_ascii=False) + "\n")
|
count += 1
|
print(f"导出 {count} 条 -> {args.output}")
|
|
|
if __name__ == "__main__":
|
main()
|
