python定时拉取BTC价格，并保存到数据库

数据库使用MYSQL：

CREATE TABLE `btc` (`id` int NOT NULL AUTO_INCREMENT,`instType` varchar(10) NOT NULL,`instId` varchar(50) NOT NULL,`last` decimal(20,10) DEFAULT NULL,`lastSz` decimal(20,10) DEFAULT NULL,`askPx` decimal(20,10) DEFAULT NULL,`askSz` decimal(20,10) DEFAULT NULL,`bidPx` decimal(20,10) DEFAULT NULL,`bidSz` decimal(20,10) DEFAULT NULL,`open24h` decimal(20,10) DEFAULT NULL,`high24h` decimal(20,10) DEFAULT NULL,`low24h` decimal(20,10) DEFAULT NULL,`volCcy24h` decimal(20,10) DEFAULT NULL,`vol24h` decimal(20,10) DEFAULT NULL,`sodUtc0` decimal(20,10) DEFAULT NULL,`sodUtc8` decimal(20,10) DEFAULT NULL,`ts` bigint unsigned NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=49317 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Python代码如下：

import requestsimport pymysqlimport time# 数据库连接信息host = "xxx.xxx.xxx.xxx"port = 3306database = "xxxxx"user = "xxxxx"passwd = "xxxxx"g_connection = Noneg_url = 'https://www.okx.com/api/v5/market/ticker?instId=BTC-USDT'def getConn():    global g_connection    if g_connection is not None and g_connection.open:        return g_connection    g_connection = pymysql.connect(        host=host,        port=port,        database=database,        charset="utf8",        user=user,        passwd=passwd    )    return g_connection# 定义每秒执行的函数def run():    # 请求接口获取价格数据    global connection    try:        response = requests.get(g_url)        if response.status_code != 200:            print('请求失败')            return        # 解析数据        data = response.json()        ticker = data['data'][0]    except Exception as e:        print('HTTP请求错误:', e)        return    # 数据库插入操作    try:        # 连接数据库        connection = getConn()        # 执行插入操作        cursor = connection.cursor()        sql = "INSERT INTO btc (instType, instId, last, lastSz, askPx, askSz, bidPx, bidSz, " \              "open24h, high24h, low24h, volCcy24h, vol24h, sodUtc0, sodUtc8, ts) " \              "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"        values = ('SPOT', ticker['instId'], ticker['last'], ticker['lastSz'], ticker['askPx'], ticker['askSz'],                  ticker['bidPx'], ticker['bidSz'], ticker['open24h'], ticker['high24h'], ticker['low24h'],                  ticker['volCcy24h'], ticker['vol24h'], ticker['sodUtc0'], ticker['sodUtc8'], int(time.time()))        cursor.execute(sql, values)        connection.commit()        cursor.close()    except Exception as e:        print('插入失败:', e)        connection.rollback()        connection.close()    finally:        pass# 无限循环while True:    start_time = time.time()  # 记录开始时间    run()  # 执行每秒执行的函数    end_time = time.time()  # 记录结束时间    cost_time = end_time - start_time  # 计算执行时间    time.sleep(max(1 - cost_time, 0))  # 计算 sleep 时间