349 lines
13 KiB
Python
349 lines
13 KiB
Python
|
"""
|
|||
|
|
导出点击记录到CSV文件
|
|||
|
|
支持开发环境（dev）和生产环境（prod）
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
import os
|
|||
|
|
import sys
|
|||
|
|
import csv
|
|||
|
|
import argparse
|
|||
|
|
from pathlib import Path
|
|||
|
|
from datetime import datetime
|
|||
|
|
from loguru import logger
|
|||
|
|
from config import Config
|
|||
|
|
from db_manager import ClickManager
|
|||
|
|
|
|||
|
|
# 配置日志
|
|||
|
|
logger.remove()
|
|||
|
|
logger.add(
|
|||
|
|
sys.stdout,
|
|||
|
|
format="<green>{time:YYYY-MM-DD HH:mm:ss}</green> | <level>{level: <8}</level> | <level>{message}</level>",
|
|||
|
|
level="INFO"
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
|
|||
|
|
class ClickExporter:
|
|||
|
|
"""点击记录导出器"""
|
|||
|
|
|
|||
|
|
def __init__(self):
|
|||
|
|
"""初始化导出器"""
|
|||
|
|
self.config = Config
|
|||
|
|
self.click_manager = ClickManager()
|
|||
|
|
|
|||
|
|
logger.info("=" * 70)
|
|||
|
|
logger.info(f"点击记录导出器已初始化")
|
|||
|
|
logger.info(f"当前环境: {self.config.ENV}")
|
|||
|
|
logger.info(f"数据库配置:")
|
|||
|
|
logger.info(f" - Host: {self.config.MYSQL_HOST}:{self.config.MYSQL_PORT}")
|
|||
|
|
logger.info(f" - Database: {self.config.MYSQL_DATABASE}")
|
|||
|
|
logger.info(f" - User: {self.config.MYSQL_USER}")
|
|||
|
|
logger.info("=" * 70)
|
|||
|
|
logger.info("提示: 通过设置环境变量 ENV=production 切换到生产环境")
|
|||
|
|
logger.info("=" * 70)
|
|||
|
|
|
|||
|
|
def get_all_clicks(self, start_date: str = None, end_date: str = None,
|
|||
|
|
site_id: int = None, limit: int = None, join_mode: str = 'simple') -> list:
|
|||
|
|
"""
|
|||
|
|
查询点击记录
|
|||
|
|
|
|||
|
|
Args:
|
|||
|
|
start_date: 开始日期 YYYY-MM-DD
|
|||
|
|
end_date: 结束日期 YYYY-MM-DD
|
|||
|
|
site_id: 站点ID筛选
|
|||
|
|
limit: 限制数量
|
|||
|
|
join_mode: 查询模式 simple=仅点击表, full=联合三表
|
|||
|
|
|
|||
|
|
Returns:
|
|||
|
|
点击记录列表
|
|||
|
|
"""
|
|||
|
|
try:
|
|||
|
|
conn = self.click_manager.get_connection()
|
|||
|
|
cursor = conn.cursor()
|
|||
|
|
|
|||
|
|
# 根据模式构建不同的SQL查询
|
|||
|
|
if join_mode == 'full':
|
|||
|
|
# 联合三表查询
|
|||
|
|
sql = """
|
|||
|
|
SELECT
|
|||
|
|
c.id as click_id,
|
|||
|
|
c.site_id,
|
|||
|
|
c.site_url,
|
|||
|
|
c.click_time,
|
|||
|
|
c.user_ip,
|
|||
|
|
c.device_type,
|
|||
|
|
c.task_id as click_task_id,
|
|||
|
|
c.operator as click_operator,
|
|||
|
|
s.site_name,
|
|||
|
|
s.status as site_status,
|
|||
|
|
s.site_dimension,
|
|||
|
|
s.click_count as total_click_count,
|
|||
|
|
s.reply_count as total_reply_count,
|
|||
|
|
i.id as interaction_id,
|
|||
|
|
i.interaction_type,
|
|||
|
|
i.interaction_time,
|
|||
|
|
i.interaction_status,
|
|||
|
|
i.reply_content,
|
|||
|
|
i.response_received,
|
|||
|
|
i.response_content,
|
|||
|
|
i.is_successful as interaction_successful,
|
|||
|
|
i.proxy_ip as interaction_proxy_ip,
|
|||
|
|
i.fingerprint_id,
|
|||
|
|
i.error_message
|
|||
|
|
FROM ai_mip_click c
|
|||
|
|
LEFT JOIN ai_mip_site s ON c.site_id = s.id
|
|||
|
|
LEFT JOIN ai_mip_interaction i ON c.id = i.click_id
|
|||
|
|
WHERE 1=1
|
|||
|
|
"""
|
|||
|
|
else:
|
|||
|
|
# 简单查询，仅点击表
|
|||
|
|
sql = "SELECT * FROM ai_mip_click WHERE 1=1"
|
|||
|
|
|
|||
|
|
params = []
|
|||
|
|
|
|||
|
|
if start_date:
|
|||
|
|
sql += " AND c.click_time >= %s" if join_mode == 'full' else " AND click_time >= %s"
|
|||
|
|
params.append(f"{start_date} 00:00:00")
|
|||
|
|
|
|||
|
|
if end_date:
|
|||
|
|
sql += " AND c.click_time <= %s" if join_mode == 'full' else " AND click_time <= %s"
|
|||
|
|
params.append(f"{end_date} 23:59:59")
|
|||
|
|
|
|||
|
|
if site_id:
|
|||
|
|
sql += " AND c.site_id = %s" if join_mode == 'full' else " AND site_id = %s"
|
|||
|
|
params.append(site_id)
|
|||
|
|
|
|||
|
|
sql += " ORDER BY c.click_time DESC" if join_mode == 'full' else " ORDER BY click_time DESC"
|
|||
|
|
|
|||
|
|
if limit:
|
|||
|
|
sql += f" LIMIT {limit}"
|
|||
|
|
|
|||
|
|
logger.info(f"查询模式: {join_mode}")
|
|||
|
|
logger.info(f"执行查询: {sql[:200]}..." if len(sql) > 200 else f"执行查询: {sql}")
|
|||
|
|
logger.info(f"参数: {params}")
|
|||
|
|
|
|||
|
|
cursor.execute(sql, params if params else None)
|
|||
|
|
|
|||
|
|
# 获取列名
|
|||
|
|
columns = [desc[0] for desc in cursor.description]
|
|||
|
|
|
|||
|
|
# 获取所有记录
|
|||
|
|
rows = cursor.fetchall()
|
|||
|
|
|
|||
|
|
# 转换为字典列表
|
|||
|
|
clicks = []
|
|||
|
|
for row in rows:
|
|||
|
|
click_dict = {}
|
|||
|
|
for idx, col in enumerate(columns):
|
|||
|
|
value = row[idx]
|
|||
|
|
# 处理datetime类型
|
|||
|
|
if isinstance(value, datetime):
|
|||
|
|
value = value.strftime('%Y-%m-%d %H:%M:%S')
|
|||
|
|
click_dict[col] = value
|
|||
|
|
clicks.append(click_dict)
|
|||
|
|
|
|||
|
|
conn.close()
|
|||
|
|
|
|||
|
|
logger.success(f"查询成功，获取到 {len(clicks)} 条记录")
|
|||
|
|
return clicks
|
|||
|
|
|
|||
|
|
except Exception as e:
|
|||
|
|
logger.error(f"查询点击记录失败: {str(e)}")
|
|||
|
|
return []
|
|||
|
|
|
|||
|
|
def export_to_csv(self, clicks: list, output_file: str, encoding: str = 'utf-8-sig'):
|
|||
|
|
"""
|
|||
|
|
导出到CSV文件
|
|||
|
|
|
|||
|
|
Args:
|
|||
|
|
clicks: 点击记录列表
|
|||
|
|
output_file: 输出文件路径
|
|||
|
|
encoding: 文件编码，默认utf-8-sig（Excel兼容）
|
|||
|
|
"""
|
|||
|
|
if not clicks:
|
|||
|
|
logger.warning("没有数据可导出")
|
|||
|
|
return False
|
|||
|
|
|
|||
|
|
try:
|
|||
|
|
# 确保输出目录存在
|
|||
|
|
output_path = Path(output_file)
|
|||
|
|
output_path.parent.mkdir(parents=True, exist_ok=True)
|
|||
|
|
|
|||
|
|
# 定义字段中文映射
|
|||
|
|
field_mapping = {
|
|||
|
|
# 点击表字段
|
|||
|
|
'id': '点击ID',
|
|||
|
|
'click_id': '点击ID',
|
|||
|
|
'site_id': '站点ID',
|
|||
|
|
'site_url': '站点URL',
|
|||
|
|
'click_time': '点击时间',
|
|||
|
|
'user_ip': '用户IP',
|
|||
|
|
'user_agent': '浏览器标识',
|
|||
|
|
'referer_url': '来源页面',
|
|||
|
|
'device_type': '设备类型',
|
|||
|
|
'click_count': '点击次数',
|
|||
|
|
'is_valid': '是否有效',
|
|||
|
|
'task_id': '任务ID',
|
|||
|
|
'click_task_id': '任务ID',
|
|||
|
|
'operator': '操作者',
|
|||
|
|
'click_operator': '操作者',
|
|||
|
|
'created_at': '创建时间',
|
|||
|
|
# 站点表字段
|
|||
|
|
'site_name': '站点名称',
|
|||
|
|
'site_status': '站点状态',
|
|||
|
|
'status': '状态',
|
|||
|
|
'site_dimension': '站点维度',
|
|||
|
|
'total_click_count': '总点击数',
|
|||
|
|
'total_reply_count': '总回复数',
|
|||
|
|
# 互动表字段
|
|||
|
|
'interaction_id': '互动ID',
|
|||
|
|
'interaction_type': '互动类型',
|
|||
|
|
'interaction_time': '互动时间',
|
|||
|
|
'interaction_status': '互动状态',
|
|||
|
|
'reply_content': '回复内容',
|
|||
|
|
'response_received': '是否收到回复',
|
|||
|
|
'response_content': '对方回复内容',
|
|||
|
|
'interaction_successful': '互动是否成功',
|
|||
|
|
'is_successful': '是否成功',
|
|||
|
|
'interaction_proxy_ip': '代理IP',
|
|||
|
|
'proxy_ip': '代理IP',
|
|||
|
|
'fingerprint_id': '浏览器指纹ID',
|
|||
|
|
'error_message': '错误信息',
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
# 获取所有字段名（英文）
|
|||
|
|
fieldnames = list(clicks[0].keys())
|
|||
|
|
|
|||
|
|
# 转换为中文字段名
|
|||
|
|
chinese_fieldnames = [field_mapping.get(field, field) for field in fieldnames]
|
|||
|
|
|
|||
|
|
# 写入CSV
|
|||
|
|
with open(output_file, 'w', newline='', encoding=encoding) as csvfile:
|
|||
|
|
writer = csv.writer(csvfile)
|
|||
|
|
# 写入中文表头
|
|||
|
|
writer.writerow(chinese_fieldnames)
|
|||
|
|
# 写入数据行
|
|||
|
|
for click in clicks:
|
|||
|
|
row = [click.get(field, '') for field in fieldnames]
|
|||
|
|
writer.writerow(row)
|
|||
|
|
|
|||
|
|
logger.success(f"成功导出 {len(clicks)} 条记录到: {output_file}")
|
|||
|
|
logger.info(f"文件大小: {output_path.stat().st_size / 1024:.2f} KB")
|
|||
|
|
|
|||
|
|
return True
|
|||
|
|
|
|||
|
|
except Exception as e:
|
|||
|
|
logger.error(f"导出CSV失败: {str(e)}")
|
|||
|
|
return False
|
|||
|
|
|
|||
|
|
def print_summary(self, clicks: list):
|
|||
|
|
"""打印数据摘要"""
|
|||
|
|
if not clicks:
|
|||
|
|
return
|
|||
|
|
|
|||
|
|
logger.info("\n" + "=" * 70)
|
|||
|
|
logger.info("数据摘要")
|
|||
|
|
logger.info("=" * 70)
|
|||
|
|
logger.info(f"总记录数: {len(clicks)}")
|
|||
|
|
|
|||
|
|
# 统计设备类型
|
|||
|
|
device_stats = {}
|
|||
|
|
for click in clicks:
|
|||
|
|
device = click.get('device_type', 'unknown')
|
|||
|
|
device_stats[device] = device_stats.get(device, 0) + 1
|
|||
|
|
|
|||
|
|
logger.info(f"设备类型分布:")
|
|||
|
|
for device, count in device_stats.items():
|
|||
|
|
logger.info(f" - {device}: {count} 条")
|
|||
|
|
|
|||
|
|
# 时间范围
|
|||
|
|
if clicks:
|
|||
|
|
times = [click.get('click_time') for click in clicks if click.get('click_time')]
|
|||
|
|
if times:
|
|||
|
|
logger.info(f"时间范围: {min(times)} ~ {max(times)}")
|
|||
|
|
|
|||
|
|
logger.info("=" * 70)
|
|||
|
|
|
|||
|
|
|
|||
|
|
def main():
|
|||
|
|
"""主函数"""
|
|||
|
|
parser = argparse.ArgumentParser(
|
|||
|
|
description='导出点击记录到CSV文件（环境通过ENV环境变量控制）',
|
|||
|
|
formatter_class=argparse.RawDescriptionHelpFormatter,
|
|||
|
|
epilog="""
|
|||
|
|
使用示例:
|
|||
|
|
# 导出开发环境所有点击记录（默认，仅点击表）
|
|||
|
|
python export_clicks_to_csv.py -o clicks_dev.csv
|
|||
|
|
|
|||
|
|
# 导出联合三表的完整数据（包含站点信息和互动记录）
|
|||
|
|
python export_clicks_to_csv.py -o clicks_full.csv --join-mode full
|
|||
|
|
|
|||
|
|
# 导出生产环境点击记录（设置ENV环境变量）
|
|||
|
|
$env:ENV="production"; python export_clicks_to_csv.py -o clicks_prod.csv --join-mode full
|
|||
|
|
|
|||
|
|
# 按日期范围导出
|
|||
|
|
python export_clicks_to_csv.py -o clicks.csv --start-date 2026-01-01 --end-date 2026-01-31 --join-mode full
|
|||
|
|
|
|||
|
|
# 导出指定站点的记录
|
|||
|
|
python export_clicks_to_csv.py -o clicks.csv --site-id 123 --join-mode full
|
|||
|
|
|
|||
|
|
# 限制导出数量
|
|||
|
|
python export_clicks_to_csv.py -o clicks.csv --limit 1000 --join-mode full
|
|||
|
|
"""
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
parser.add_argument('-o', '--output', required=True,
|
|||
|
|
help='输出CSV文件路径')
|
|||
|
|
parser.add_argument('--start-date', help='开始日期 YYYY-MM-DD')
|
|||
|
|
parser.add_argument('--end-date', help='结束日期 YYYY-MM-DD')
|
|||
|
|
parser.add_argument('--site-id', type=int, help='站点ID筛选')
|
|||
|
|
parser.add_argument('--limit', type=int, help='限制导出数量')
|
|||
|
|
parser.add_argument('--join-mode', choices=['simple', 'full'], default='simple',
|
|||
|
|
help='查询模式: simple=仅点击表, full=联合三表（点击+站点+互动）')
|
|||
|
|
parser.add_argument('--encoding', default='utf-8-sig',
|
|||
|
|
help='文件编码，默认utf-8-sig（Excel兼容）')
|
|||
|
|
|
|||
|
|
args = parser.parse_args()
|
|||
|
|
|
|||
|
|
try:
|
|||
|
|
# 创建导出器
|
|||
|
|
exporter = ClickExporter()
|
|||
|
|
|
|||
|
|
# 查询点击记录
|
|||
|
|
logger.info("\n开始查询点击记录...")
|
|||
|
|
clicks = exporter.get_all_clicks(
|
|||
|
|
start_date=args.start_date,
|
|||
|
|
end_date=args.end_date,
|
|||
|
|
site_id=args.site_id,
|
|||
|
|
limit=args.limit,
|
|||
|
|
join_mode=args.join_mode
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
if not clicks:
|
|||
|
|
logger.warning("没有找到符合条件的记录")
|
|||
|
|
sys.exit(1)
|
|||
|
|
|
|||
|
|
# 打印摘要
|
|||
|
|
exporter.print_summary(clicks)
|
|||
|
|
|
|||
|
|
# 导出到CSV
|
|||
|
|
logger.info(f"\n开始导出到: {args.output}")
|
|||
|
|
success = exporter.export_to_csv(clicks, args.output, args.encoding)
|
|||
|
|
|
|||
|
|
if success:
|
|||
|
|
logger.success("\n导出完成！")
|
|||
|
|
sys.exit(0)
|
|||
|
|
else:
|
|||
|
|
logger.error("\n导出失败")
|
|||
|
|
sys.exit(1)
|
|||
|
|
|
|||
|
|
except KeyboardInterrupt:
|
|||
|
|
logger.warning("\n用户中断导出")
|
|||
|
|
sys.exit(130)
|
|||
|
|
except Exception as e:
|
|||
|
|
logger.error(f"导出失败: {str(e)}")
|
|||
|
|
sys.exit(1)
|
|||
|
|
|
|||
|
|
|
|||
|
|
if __name__ == '__main__':
|
|||
|
|
main()
|