62 lines
2.5 KiB
Python
62 lines
2.5 KiB
Python
import sys
|
|
import time
|
|
from kis_trader.database.db_manager import TradeDB
|
|
|
|
db = TradeDB()
|
|
try:
|
|
rows = db.conn.execute("SHOW TABLES").fetchall()
|
|
tables = [list(r.values())[0] for r in rows]
|
|
|
|
target_prefixes = (
|
|
'ws_ticks', 'ws_orderbook', 'ws_candles',
|
|
'ls_ws_ticks', 'ls_ws_orderbook', 'ls_ws_candles',
|
|
'ws_price_validation'
|
|
)
|
|
|
|
for table in tables:
|
|
if not table.startswith(target_prefixes):
|
|
continue
|
|
|
|
cols = [dict(r)["Field"] for r in db.conn.execute(f"SHOW COLUMNS FROM {table}").fetchall()]
|
|
time_col = None
|
|
for candidate in ('tick_time', 'ob_time', 'candle_time', 'timestamp', 'recv_ts', 'datetime'):
|
|
if candidate in cols:
|
|
time_col = candidate
|
|
break
|
|
|
|
if time_col:
|
|
cutoff = '20260808'
|
|
|
|
# Print count before deletion
|
|
count_q = f"SELECT COUNT(*) as cnt FROM {table} WHERE {time_col} < %s"
|
|
count = db.conn.execute(count_q, (cutoff,)).fetchone()['cnt']
|
|
|
|
if count > 0:
|
|
print(f"Deleting {count} rows from {table} (using column {time_col})")
|
|
deleted = 0
|
|
while True:
|
|
del_q = f"DELETE FROM {table} WHERE {time_col} < %s LIMIT 100000"
|
|
db.conn.execute(del_q, (cutoff,))
|
|
db.conn.commit()
|
|
|
|
cur_deleted = db.conn.cursor().rowcount
|
|
# PyMySQL execute might not return rowcount directly,
|
|
# we can use a separate cursor or just check if affected rows is 0
|
|
# Wait, db.conn is SQLAlchemy Engine or raw pymysql connection?
|
|
# It's an SQLAlchemy Engine in db_manager.py!
|
|
# Actually, SQLAlchemy execute() returns ResultProxy with rowcount
|
|
res = db.conn.execute(del_q, (cutoff,))
|
|
db.conn.commit()
|
|
if res.rowcount <= 0:
|
|
break
|
|
deleted += res.rowcount
|
|
print(f" ... deleted {deleted}/{count} rows in {table}")
|
|
time.sleep(0.1)
|
|
print(f"Finished deleting from {table}.")
|
|
else:
|
|
print(f"No rows to delete in {table} before {cutoff}")
|
|
else:
|
|
print(f"Warning: No time column found for {table}, columns: {cols}")
|
|
finally:
|
|
db.close()
|