force_sync.py

import sys
import os
import time
sys.path.append(os.path.join(os.getcwd(), 'backend'))
import akshare as ak
import pandas as pd
from sqlalchemy import text
import database as db

def force_sync(symbol):
    print(f"Syncing {symbol}...")
    try:
        df = ak.futures_zh_daily_sina(symbol=symbol)
        if df is not None and not df.empty:
            print(f"Got {len(df)} rows.")
            df = df.rename(columns={"hold": "open_interest", "settle": "settle"})
            df['date'] = pd.to_datetime(df['date']).dt.date
            
            data_to_insert = df[['date', 'open', 'high', 'low', 'close', 'volume', 'open_interest', 'settle']].to_dict(orient='records')
            
            with db.engine.connect() as conn:
                trans = conn.begin()
                try:
                    for row in data_to_insert:
                        # Ensure we use the symbol passed (e.g. JM0)
                        params = {**row, "symbol": symbol}
                        conn.execute(text("""
                            INSERT INTO futures_daily (symbol, date, open, high, low, close, volume, open_interest, settle)
                            VALUES (:symbol, :date, :open, :high, :low, :close, :volume, :open_interest, :settle)
                            ON CONFLICT (symbol, date) DO UPDATE SET
                                open = EXCLUDED.open,
                                high = EXCLUDED.high,
                                low = EXCLUDED.low,
                                close = EXCLUDED.close,
                                volume = EXCLUDED.volume,
                                open_interest = EXCLUDED.open_interest,
                                settle = EXCLUDED.settle
                        """), params)
                    trans.commit()
                    print("Saved to DB.")
                except Exception as e:
                    trans.rollback()
                    print(f"DB Error: {e}")
        else:
            print("No data from AkShare.")
    except Exception as e:
        print(f"Fetch Error: {e}")

# Check what symbols we need from DB
try:
    with db.engine.connect() as conn:
        rows = conn.execute(text("SELECT main_code FROM futures_main_contracts")).fetchall()
        symbols = [r[0] for r in rows]
        print(f"Found symbols: {symbols}")
        
        for s in symbols:
            force_sync(s)
            time.sleep(1) 
except Exception as e:
    print(f"Main Error: {e}")