StockAnalytics/SP500Prediction/ETLPipelines/InsertData.py at master · yschan8137/StockAnalytics · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
# Function to insert prices to database
def insert_price(conn, row, ticker):
	# Query
	query = """insert into stock.stockprice (ticker, tradedate, openprice, high, low,
            closeprice, volume) values (%s, %s, %s, %s, %s, %s, %s)"""
	# row must be a row from a pandas dataframe from yfinance
	data = (ticker, row['Date'], row['Open'], row['High'], row['Low'],
		    row['Close'], row['Volume'])
	cur = conn.cursor()
	cur.execute(query, data)
	conn.commit()


# Function to insert meta data to database
def insert_meta(conn, meta_dict):
	# Query
	query = """insert into stock.stockmeta (ticker, companyname,
	        gicssector, gicssubindustry, countrystockmarket, indexcomponent)
	        values (%s, %s, %s, %s, %s, %s)"""
	# Take meta data store in dictionary
	data = (meta_dict['ticker'], meta_dict['companyname'],
		    meta_dict['gicssector'], meta_dict['gicssubindustry'],
		    meta_dict['countrystockmarket'], meta_dict['indexcomponent'])
	cur = conn.cursor()
	cur.execute(query, data)
	conn.commit()

# Function to insert share data to database
def insert_share(conn, ticker, share_num):
	# Query
	query = """insert into stock.stockshareoutstanding (ticker,
	           shareoutstanding) values (%s, %s)"""
	data = (ticker, share_num)
	cur = conn.cursor()
	cur.execute(query, data)
	conn.commit()

# Function to insert prediction to database
def insert_pred(conn, table, ticker, row):
	# Query
	query = """insert into stock.{} (ticker, tradedate,
	           closeprice)""".format(table)
	query += """values (%s, %s, %s)"""
	data = (ticker, row['ds'], row['yhat'])
	cur = conn.cursor()
	cur.execute(query, data)
	conn.commit()

# Function to insert training result from Prediction_Dynamic_StockPrice
def insert_result(conn, ticker, rsqu, start_year, package):
	# Query
	query = """insert into stock.pred_dy_trainresult (ticker, rsquare,
	           start_year, package) values (%s, %s, %s, %s)"""
	data = (ticker, rsqu, start_year, package)
	cur = conn.cursor()
	cur.execute(query, data)
	conn.commit()

# Function to insert S&P 500 Divisor
def insert_divisor(conn, tradedate, divisor):
	query = """ insert into stock.sp500divisor (tradedate, divisor)
	            values (%s, %s)"""
	data = (tradedate, divisor)
	cur = conn.cursor()
	cur.execute(query, data)
	conn.commit()

# Function to insert pred_dy_sp500
def insert_sp500_db(conn, row):
	query = """ insert into stock.pred_dy_sp500 (tradedate, sp500_testdata,
	            sp500_pred, sp500_predMod) values (%s, %s, %s, %s) """
	data = (row['tradedate'], row['sp500_test'], row['sp500_pred'],
		    row['sp500_predMod'])
	cur = conn.cursor()
	cur.execute(query, data)
	conn.commit()