MySQL 日志与数据存储

import MySQLdb


def test_mysqlclient():
    """使用 mysqlclient 操作 MySQL"""

    # 1. 连接数据库
    conn = MySQLdb.connect(
        host='localhost',
        port=3306,
        user='your_username',
        passwd='your_password',
        db='mysql'
    )

    # 2. 创建游标
    # 游标的作用：将集合操作转换为逐行访问
    cur = conn.cursor()

    # 3. 创建表
    cur.execute('''
        CREATE TABLE IF NOT EXISTS price (
            timestamp TIMESTAMP NOT NULL,
            BTCUSD DECIMAL(11,2),
            PRIMARY KEY (timestamp)
        )
    ''')

    # 4. 插入数据
    cur.execute('''
        INSERT INTO price VALUES(
            '2019-07-14 14:12:17',
            11234.56
        )
    ''')

    # 5. 提交更改
    conn.commit()

    # 6. 查询数据
    cur.execute('''
        SELECT BTCUSD
        FROM price
        WHERE timestamp > NOW() - INTERVAL 60 MINUTE
    ''')

    # 7. 获取结果
    results = cur.fetchall()
    for row in results:
        print(f"BTC/USD: {row[0]}")

    # 8. 关闭连接
    conn.close()


test_mysqlclient()

from peewee import *


# 连接数据库
db = MySQLDatabase(
    'mysql',
    user='your_username',
    passwd='your_password'
)


class Price(Model):
    """价格数据模型 —— 一个 Python class 映射一张数据库表"""
    timestamp = DateTimeField(primary_key=True)
    BTCUSD = FloatField()

    class Meta:
        database = db


def test_peewee():
    """使用 peewee ORM 操作 MySQL"""

    # 1. 创建表（如果不存在）
    db.connect()
    db.create_tables([Price], safe=True)

    # 2. 插入数据 —— 像操作 Python 对象一样
    price = Price(
        timestamp='2019-06-07 13:17:18',
        BTCUSD=12345.67
    )
    price.save()

    # 3. 查询数据 —— 用 Python 语法，不用写 SQL
    recent_prices = (Price
                     .select()
                     .where(Price.timestamp > '2019-06-01')
                     .order_by(Price.timestamp.desc())
                     .limit(10))

    for p in recent_prices:
        print(f"{p.timestamp}: BTC/USD = {p.BTCUSD}")

    db.close()


test_peewee()

-- ============ 行情数据表 ============
CREATE TABLE market_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(10) NOT NULL COMMENT '交易对',
    `open` DECIMAL(15, 2) COMMENT '开盘价',
    high DECIMAL(15, 2) COMMENT '最高价',
    low DECIMAL(15, 2) COMMENT '最低价',
    `close` DECIMAL(15, 2) COMMENT '收盘价',
    volume DECIMAL(20, 8) COMMENT '成交量',
    `timestamp` DATETIME NOT NULL COMMENT 'K线时间',
    INDEX idx_symbol_time (symbol, `timestamp`)
) COMMENT 'OHLCV 行情数据';


-- ============ 交易记录表 ============
CREATE TABLE trades (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(64) UNIQUE COMMENT '交易所订单ID',
    symbol VARCHAR(10) COMMENT '交易对',
    side ENUM('buy', 'sell') COMMENT '买卖方向',
    order_price DECIMAL(15, 2) COMMENT '下单价格',
    price DECIMAL(15, 2) COMMENT '成交价格',
    amount DECIMAL(15, 8) COMMENT '成交数量',
    fee DECIMAL(15, 8) COMMENT '手续费',
    status VARCHAR(20) COMMENT '订单状态',
    created_at DATETIME COMMENT '创建时间',
    executed_at DATETIME COMMENT '成交时间',
    INDEX idx_created (created_at),
    INDEX idx_symbol_status (symbol, status)
) COMMENT '交易记录';


-- ============ 持仓快照表 ============
CREATE TABLE positions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(10) COMMENT '交易对',
    amount DECIMAL(15, 8) COMMENT '持仓数量',
    avg_price DECIMAL(15, 2) COMMENT '平均成本',
    updated_at DATETIME COMMENT '更新时间',
    UNIQUE KEY uk_symbol (symbol)
) COMMENT '当前持仓快照';

-- 每日盈亏（PnL）计算
SELECT
    DATE(executed_at) as trade_date,
    SUM(CASE WHEN side = 'sell'
        THEN amount * price
        ELSE -amount * price
    END) as pnl
FROM trades
WHERE status = 'filled'
GROUP BY DATE(executed_at)
ORDER BY trade_date DESC;


-- 滑点分析（price 为成交价，order_price 为下单价）
SELECT
    symbol,
    COUNT(*) as trade_count,
    AVG(ABS(price - order_price) / order_price) * 100
        as avg_slippage_pct
FROM trades
WHERE status = 'filled'
GROUP BY symbol;


-- 胜率统计：将每笔卖出与其前一笔成交（买入）配对，卖出额更高记为盈利
-- 简化假设：同一 symbol 的成交按买入→卖出交替出现；需 MySQL 8.0+（窗口函数）
SELECT
    symbol,
    COUNT(*) as closed_trades,
    SUM(is_win) as winning_trades,
    ROUND(AVG(is_win) * 100, 2) as win_rate_pct
FROM (
    SELECT
        symbol,
        side,
        CASE
            WHEN side = 'sell'
             AND amount * price > LAG(amount * price) OVER (
                 PARTITION BY symbol ORDER BY executed_at)
            THEN 1 ELSE 0
        END as is_win
    FROM trades
    WHERE status = 'filled'
) t
WHERE side = 'sell'
GROUP BY symbol;