MySQL数据加载报错求助：NameError: name 'close' is not defined

我用TradingView Lightweight Charts生成模拟股票数据并存储到MySQL服务器，但加载数据时遇到NameError错误，提示'close'未定义。

错误栈信息 #

connection handler failed
Traceback (most recent call last):
  File "C:\Users\dudas\AppData\Local\Programs\Python\Python39\lib\site-packages\websockets\legacy\server.py", line 236, in handler
    await self.ws_handler(self)
  File "C:\Users\dudas\AppData\Local\Programs\Python\Python39\lib\site-packages\websockets\legacy\server.py", line 1175, in _ws_handler
    return await cast(
  File "C:\xampp\htdocs\chart\price.py", line 32, in server
    price = result[close]
NameError: name 'close' is not defined

问题原因 #

1. 变量与字符串混淆：代码中result[close]里的close没有加引号，Python会将其视为未定义的变量，而非数据库字段名的字符串键。result[low]、result[high]存在同样问题。
2. SQL查询字段不匹配：原SQL仅查询了close字段，但后续试图获取low和high的值，会导致IndexError，因为查询结果只有一个元素。

修复后的代码 #

import asyncio
import websockets
import random
from datetime import datetime, timedelta
import json
import mysql.connector

# 建立数据库连接
cnx = mysql.connector.connect(user='root', password='',
                              host='localhost',
                              database='tradewisedb')
cursor = cnx.cursor()

# 不存在则创建数据表
cursor.execute("""
CREATE TABLE IF NOT EXISTS prices (
    time BIGINT,
    open FLOAT,
    high FLOAT,
    low FLOAT,
    close FLOAT
)
""")

# 模拟参数初始化
price = 100.0
trend = 1
volatility = 0.02
min_price = 50.0
max_price = 150.0
fluctuation_range = 0.02
post_jump_fluctuation = 0.005
interval = timedelta(minutes=1)

async def server(websocket, path):
    # 修复：同时查询close、low、high三个字段
    cursor.execute("SELECT close, low, high FROM prices ORDER BY time DESC LIMIT 1")
    result = cursor.fetchone()

    if result is not None:
        # 修复：用索引下标获取查询结果，避免未定义变量
        price = result[0]  # 对应查询的第一个字段close
        min_price = result[1]  # 对应查询的第二个字段low
        max_price = result[2]  # 对应查询的第三个字段high

    else:
        price = 100.0
        trend = 1
        volatility = 0.02
        min_price = 50.0
        max_price = 150.0
        fluctuation_range = 0.02
        post_jump_fluctuation = 0.005
        interval = timedelta(minutes=1)

    cursor.execute("SELECT * FROM prices ORDER BY time ASC LIMIT 1000")
    rows = cursor.fetchall()

    previous_data = [{
        'time': row[0],
        'open': row[1],
        'high': row[2],
        'low': row[3],
        'close': row[4]
    } for row in rows]

    await websocket.send(json.dumps(previous_data))

    while True:
        start_time = datetime.now()
        open_price = price
        high_price = price
        low_price = price

        start_time_timestamp = int(start_time.timestamp() * 1000)  # 毫秒级时间戳

        while datetime.now() - start_time < interval:
            # 随机触发"新闻"事件
            if random.random() < 0.01:
                trend *= -1
                volatility = fluctuation_range

            # 大波动后回归窄幅震荡
            else:
                volatility = post_jump_fluctuation

            # 随机价格波动
            price *= 1 + trend * volatility * random.uniform(-1, 1)

            # 限制价格区间
            if price > max_price:
                price = max_price
                trend = -1
            elif price < min_price:
                price = min_price
                trend = 1

            high_price = max(high_price, price)
            low_price = min(low_price, price)

            temp_data = {
                "time": start_time_timestamp,
                "open": open_price,
                "high": high_price,
                "low": low_price,
                "close": price
            }

            await websocket.send(json.dumps(temp_data))
            await asyncio.sleep(1)

        close_price = price

        data = {
            "time": start_time_timestamp,
            "open": open_price,
            "high": high_price,
            "low": low_price,
            "close": close_price
        }

        await websocket.send(json.dumps(data))

        # 插入数据到数据库
        add_price = ("INSERT INTO prices "
                   "(time, open, high, low, close) "
                   "VALUES (%s, %s, %s, %s, %s)")
        cursor.execute(add_price, (data['time'], data['open'], data['high'], data['low'], data['close']))
        cnx.commit()

start_server = websockets.serve(server, 'localhost', 8000)

asyncio.get_event_loop().run_until_complete(start_server)
asyncio.get_event_loop().run_forever()

# 关闭数据库连接
cursor.close()
cnx.close()

可选优化方案 #

如果习惯用字段名直接取值，可以将游标改为字典类型：

cursor = cnx.cursor(dictionary=True)

之后就能用result['close']、result['low']、result['high']的方式获取数据，代码可读性更强。

内容的提问来源于stack exchange，提问作者István Gábor Dudás