首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >使用Python数据插入的SQL死锁

问使用Python数据插入的SQL死锁
EN

Stack Overflow用户
提问于 2017-03-18 02:59:44
回答 1查看 1.1K关注 0票数 2

我目前正在尝试用python构建一个数据库接口来存储库存数据。此数据以元组列表的形式出现,每个元素由“日期、打开、高、低、关闭、卷”组成。date表示UNIX时间戳,必须与数据库中的滴答符号相结合。下面是一个典型处理输出(company_stock)的示例:

代码语言:javascript
复制
 [(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个值。

代码语言:javascript
复制
# 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语句(与其进行比较的实际表看起来相同):

代码语言:javascript
复制
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)的大容量插入。

背景信息:

  • 上面显示的SQL导出是python脚本处理股票数据响应的最后一部分。反过来,这个脚本由另一个脚本调用,该脚本由crontab计时,以便每天在特定时间运行。
  • 一旦crontab启动控制脚本,它将调用下标约500次,每次运行之间的睡眠时间约为20-25秒。
  • 我在日志中看到的错误是: error 1205 (HY000):超过了锁等待超时;尝试重新启动事务

问题:

  1. 如何优化查询或更改存储表以确保给定的date/ticker组合的唯一性?
  2. 这是问题吗,还是我在这里看不到其他问题?
  3. 任何进一步的建议也是受欢迎的。
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-03-18 04:57:30

如果希望确保数据的唯一性,那么只需在相关的独一无二和ticker字段中添加一个date索引即可。唯一索引防止插入重复值,因此不需要在插入之前检查数据是否存在。

由于您不想插入重复的数据,所以只需使用插入忽略而不是普通的insert来抑制重复插入错误。根据受影响行的数目,仍然可以检测和记录重复插入。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/42869985

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档