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 requests
import pymysql
import time

# 数据库连接信息
host = "xxx.xxx.xxx.xxx"
port = 3306
database = "xxxxx"
user = "xxxxx"
passwd = "xxxxx"

g_connection = None
g_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 时间