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 FLOAT(8,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 '买卖方向',
    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;


-- 滑点分析
SELECT
    symbol,
    COUNT(*) as trade_count,
    AVG(ABS(executed_price - order_price) / order_price) * 100
        as avg_slippage_pct
FROM trades
WHERE status = 'filled'
GROUP BY symbol;


-- 胜率统计
SELECT
    symbol,
    COUNT(*) as total_trades,
    SUM(CASE WHEN pnl > 0 THEN 1 ELSE 0 END) as winning_trades,
    ROUND(SUM(CASE WHEN pnl > 0 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2)
        as win_rate_pct
FROM (
    SELECT symbol,
           CASE WHEN side = 'sell'
               THEN amount * price
               ELSE -amount * price
           END as pnl
    FROM trades
    WHERE status = 'filled'
) t
GROUP BY symbol;