| import sqlite3 | |
| import json | |
| import os | |
| from pathlib import Path | |
| db_path = Path("data/agent.db") | |
| if not db_path.exists(): | |
| db_path = Path("agent.db") | |
| print(f"Connecting to {db_path}...") | |
| try: | |
| conn = sqlite3.connect(db_path, timeout=10) | |
| conn.row_factory = sqlite3.Row | |
| c = conn.cursor() | |
| c.execute("SELECT id, source_url FROM posts WHERE source_url LIKE '{%'") | |
| rows = c.fetchall() | |
| print(f"Found {len(rows)} bad posts with JSON strings in source_url.") | |
| updates = [] | |
| for r in rows: | |
| try: | |
| data = json.loads(r['source_url']) | |
| if 'url' in data: | |
| clean_url = data['url'] | |
| elif 'urls' in data and len(data['urls']) > 0: | |
| clean_url = data['urls'][0] | |
| else: | |
| continue | |
| updates.append((clean_url, r['id'])) | |
| except: | |
| pass | |
| if updates: | |
| c.executemany("UPDATE posts SET source_url = ? WHERE id = ?", updates) | |
| conn.commit() | |
| print(f"Migrated {len(updates)} posts successfully.") | |
| conn.close() | |
| except Exception as e: | |
| print(f"Error: {e}") | |