我目前正在尝试用python构建一个数据库接口来存储库存数据。此数据以元组列表的形式出现,每个元素由“日期、打开、高、低、关闭、卷”组成。date表示UNIX时间戳,必须与数据库中的滴答符号相结合。下面是一个典型处理输出(company_stock)的示例:
[(1489780560, 'NYSE:F', 12.5, 12.505, 12.49, 12.495, 567726),
(1489780620, 'NYSE:F', 12.495, 12.5, 12.48, 12.48, 832487),
(1489780680, 'NYSE:F', 12.485, 12.49, 12.47, 12.475, 649818),
(1489780740, 'NYSE:F', 12.475, 12.48, 12.47, 12.47, 700579),
(1489780800, 'NYSE:F', 12.47, 12.48, 12.47, 12.48, 567798)]我使用pymysql包将这个列表插入到本地MySQL数据库(版本5.5)中。当代码运行并插入值时,数据库将崩溃--或者更确切地说是停止--在到达大约250 k行之后。由于相关,这是库存数据处理函数的导出部分,大约每20秒调用一次,并插入大约400个值。
# SQL Export
def tosql(company_stock, ticker, interval, amount_period, period):
try:
conn = pymysql.connect(host = "localhost", user = "root",
passwd = "pw", db = "db", charset = "utf8",
autocommit = True,
cursorclass = pymysql.cursors.DictCursor)
cur = conn.cursor()
# To temp table
query = "INSERT INTO stockdata_import "
query += "(date, tickersymbol, open, high, low, close, volume)"
query += "VALUES (%s, %s, %s, %s, %s, %s, %s)"
cur.executemany(query, company_stock)
# Duplicate Check with temp table and existing database storage
query = "INSERT INTO stockdata (date, tickersymbol, open, high, low, close, volume) "
query += "SELECT i.date, i.tickersymbol, i.open, i.high, i.low, "
query += "i.close, i.volume FROM stockdata_import i "
query += "WHERE NOT EXISTS(SELECT dv.date, dv.tickersymbol FROM "
query += "stockdata dv WHERE dv.date = i.date "
query += "AND dv.tickersymbol = i.tickersymbol)"
cur.execute(query)
print(": ".join([datetime.now().strftime("%d.%m.%Y %H:%M:%S"),
"Data stored in Vault. Ticker", str(ticker),
"Interval", str(interval),
"Last", str(amount_period), str(period)]))
finally:
# Clear temp import table and close connection
query = "DELETE from stockdata_import"
cur.execute(query)
cur.close()
conn.close()我怀疑对已经存在的值的检查花费了数据库增长的时间太长,并最终由于表(?)的锁而崩溃。同时检查日期/代码组合的唯一性。由于我预计这个数据库会增长得相当快(每周大约有100万行),因此似乎需要一种不同的解决方案来确保只有一个日期/代码对。这是导入表的SQL CREATE语句(与其进行比较的实际表看起来相同):
CREATE TABLE stockdata_import (id_stock_imp BIGINT(12) NOT NULL AUTO_INCREMENT,
date INT(10),
tickersymbol VARCHAR(16),
open FLOAT(12,4),
high FLOAT(12,4),
low FLOAT(12,4),
close FLOAT(12,4),
volume INT(12),
crawled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(id_stock_imp));我已经研究过为date/tickersymbol对设置一个约束,并在python中处理即将出现的异常,但我的研究表明,这会更慢,而且我甚至不确定这是否适用于pymysql游标函数executemany(query, data)的大容量插入。
背景信息:
问题:
date/ticker组合的唯一性?https://stackoverflow.com/questions/42869985
复制相似问题