Блог

Я работаю над настройкой базы данных MYSQL для хранения данных фондового рынка. Ниже приведена структура данных для базы данных.

Я пытался сделать это, используя инструкции из этого руководства, но у меня возникли проблемы с фактическим обновлением таблицы «daily_price». Он зависает на вводе data_vendor_id, который он должен получить как ‘id’ из таблицы «data_vendor». Я предполагаю, что таблица data_vendor не получает полных данных, потому что, когда я смотрю на нее в PHP моего администратора, она начинается с идентификатора 3, но я не могу заставить ее собирать все данные.

код:

 for row in nyse.itertuples(index=False):
    try:
        cursor.execute("INSERT INTO security (ticker, name, sector, industry, exchange) VALUES (%s, %s, %s, %s, %s)", 
        row)
    except:
        # Assume that the exception is because sector
        # and/or industry are missing
        cursor.execute("INSERT INTO security (exchange_id,ticker, name) VALUES (%s, %s, %s)", row[:3])
conn.commit()

sql = "INSERT INTO data_vendor (name, website_url) VALUES "   
    "('YahooFinance', 'https://finance.yahoo.com')"
cursor.execute(sql)
conn.commit()

YAHOO_VENDOR_ID = 1

all_tickers = pd.read_sql("SELECT ticker, id FROM security", conn)
ticker_index = dict(all_tickers.to_dict('split')['data'])
tickers = list(ticker_index.keys())

start = dt.datetime(2018, 8, 8)
end = dt.datetime.now()

for ticker in tickers:
    # Download data
    df = pdr.get_data_yahoo(ticker, start, end)
    
    # Write to daily_price
    for row in df.itertuples():
        values = [YAHOO_VENDOR_ID, ticker_index[ticker]]   [row[0].to_pydatetime()]   list(row[1:])
        sql_two = "INSERT INTO daily_price (data_vendor_id,ticker_id, price_date, open_price, high_price, low_price, close_price, volume, adj_close_price) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
        print(df)
        print(sql_two)
        print(tuple(values))
        #print(sql_two % values)
        cursor.execute(sql_two, tuple(values))
  

Сообщение об ошибке:

 Traceback (most recent call last):
  File "C:Users17409AppDataLocalProgramsPythonPython38IndicatorsSentdexTutorialSnP500List(database).py", line 74, in <module>
    cursor.execute(sql_two, tuple(values))...
...    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`stock_prices`.`daily_price`, CONSTRAINT `fk_data_vendor_id` FOREIGN KEY (`data_vendor_id`) REFERENCES `data_vendor` (`id`))')
  

Также я распечатал, как выглядит первый раунд ввода при запуске

 df = 
                 High        Low       Open      Close     Volume  Adj Close
Date                                                                        
2018-08-08  68.150002  67.339996  67.739998  67.379997  1681900.0  66.196304
2018-08-09  67.620003  66.610001  67.480003  66.690002  1727800.0  65.518425
2018-08-10  66.870003  65.930000  66.820000  66.260002  2165900.0  65.095970
2018-08-13  66.989998  65.669998  66.440002  65.940002  2983100.0  64.781593
2018-08-14  67.260002  66.050003  66.050003  66.750000  3110100.0  65.577370
...               ...        ...        ...        ...        ...        ...
2020-08-18  98.709999  97.110001  97.709999  97.860001  2008700.0  97.860001
2020-08-19  99.400002  94.529999  95.230003  97.959999  2830800.0  97.959999
2020-08-20  98.169998  97.120003  97.389999  97.779999  1428300.0  97.779999
2020-08-21  98.320000  97.269997  97.870003  98.300003  1269300.0  98.300003
2020-08-24  99.209999  97.139999  98.779999  97.599998  1154541.0  97.599998

[515 rows x 6 columns]
sql_two = 

INSERT INTO daily_price (data_vendor_id,ticker_id, price_date, open_price, high_price, low_price, close_price, volume, adj_close_price) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
tuple(values) = 

(1, 185250, datetime.datetime(2018, 8, 8, 0, 0), 68.1500015258789, 67.33999633789062, 67.73999786376953, 67.37999725341797, 1681900.0, 66.19630432128906)

  

Исправьте это, удалив таблицы и удалив ограничение внешнего ключа. Исправьте заполнение кода для соответствия.

Другой ошибкой, с которой я столкнулся, было несоответствие столбцов, поэтому я определил значения по умолчанию для всех элементов в базе данных.

Обновленный код:

 for row in nyse.itertuples(index=False):
    try:
        cursor.execute("INSERT INTO security (exchange_id, ticker, name, sector, industry, exchange) VALUES (%s, %s, %s, %s, %s, %s)", 
        row)
    except:
        # Assume that the exception is because sector
        # and/or industry are missing
        cursor.execute("INSERT INTO security (exchange_id,ticker, name) VALUES (%s, %s, %s)", row[:3])
conn.commit()

for rowe in nasdaq.itertuples(index=False):
    try:
        cursor.execute("INSERT INTO security (exchange_id, ticker, name, sector, industry, exchange) VALUES (%s, %s, %s, %s, %s, %s)", 
        rowe)
    except:
        # Assume that the exception is because sector
        # and/or industry are missing
        cursor.execute("INSERT INTO security (exchange_id,ticker, name) VALUES (%s, %s, %s)", row[:3])
conn.commit()



sql = "INSERT INTO data_vendor (name, website_url) VALUES "   
    "('YahooFinance', 'https://finance.yahoo.com')"
cursor.execute(sql)
conn.commit()

YAHOO_VENDOR_ID = 1
FINHUB_VENDOR_ID = 2

all_tickers = pd.read_sql("SELECT ticker, id FROM security", conn)
ticker_index = dict(all_tickers.to_dict('split')['data'])
tickers = list(ticker_index.keys())

start = dt.datetime(2018, 8, 8)
end = dt.datetime.now()

for ticker in tickers:
    # Download data
    df = pdr.get_data_yahoo(ticker, start, end)
    
    # Write to daily_price
    for row in df.itertuples():
        values = [ticker_index[ticker]]   [row[0].to_pydatetime()]   list(row[1:])
        sql_two = "INSERT INTO daily_price (ticker_id, price_date, open_price, high_price, low_price, close_price, volume, adj_close_price) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
        
        cursor.execute(sql_two, tuple(values))