В настоящее время я пытаюсь создать интерфейс базы данных с помощью 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 тыс. строк. Так как это является частью экспорта функции обработки данных о запасах, которая вызывается примерно раз в 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()
Я подозреваю, что проверка уже существующих значений занимает слишком много времени по мере роста базы данных и в конечном итоге ломается из-за блокировки таблиц (?) при проверке уникальности комбинации даты и тикера. Поскольку я ожидаю, что эта база данных будет расти довольно быстро (около 1 миллиона строк в неделю), кажется, что требуется другое решение, чтобы гарантировать наличие только одной пары дата/тикер. Это оператор 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)
.
Контекстная информация:
- Экспорт SQL, показанный выше, является последней частью скрипта Python, обрабатывающего ответ с данными о запасах. Этот сценарий, в свою очередь, вызывается другим сценарием, который запускается crontab в определенное время каждый день.
- После того, как crontab запустит управляющий скрипт, он вызовет индекс примерно 500 раз с перерывом в 20-25 секунд между каждым запуском.
- Ошибка, которую я вижу в журналах: ОШИБКА 1205 (HY000): превышено время ожидания блокировки; попробуйте перезапустить транзакцию
Вопросы:
- Как оптимизировать запрос или изменить таблицу хранения, чтобы обеспечить уникальность данной комбинации
date
/ticker
? - Это вообще проблема или я не вижу здесь какой-то другой проблемы?
- Любые дополнительные советы также приветствуются.