取得資料後的步驟,當然是紀錄囉!
現在把資料放到Azure上
本篇程式碼:Day16_saveApiStockToDb.py
這個部分可以參考昨天的資料
address = "http://www.twse.com.tw/exchangeReport/STOCK_DAY_ALL?response=open_data"
# 取得資料
response = requests.get(address)
# 解析
data = response.text
mystr = StringIO(data)
df = pandas.read_csv(mystr, header=None)
# 建立新dataframe
new_headers = df.iloc[0] # 第一行當作header
new_headers = create_new_header(new_headers)
df = df[1:] # 拿掉第一行的資料
df.columns = new_headers # 設定資料欄位的名稱
為了方便我們帶參數,這邊製作了一個function
,叫做create_new_header
,把中文欄位名稱轉換成英文。
def create_new_header(orignal_headers):
new_headers = []
for column in orignal_headers:
data = str(column)
if data == "證券代號":
new_headers.append("stock_symbol")
elif data == "證券名稱":
new_headers.append("stock_name")
elif data == "成交股數":
new_headers.append("volume")
elif data == "成交金額":
new_headers.append("total_price")
elif data == "開盤價":
new_headers.append("open")
elif data == "最高價":
new_headers.append("high")
elif data == "最低價":
new_headers.append("low")
elif data == "收盤價":
new_headers.append("close")
elif data == "漲跌價差":
new_headers.append("spread")
elif data == "成交筆數":
new_headers.append("transactions_number")
return new_headers
接著就在寫一個叫save_data_to_azure_db
的function
,把我們的資料寫入Azure中。
(注:在這之前,可以把之前的資料清掉以方便練習,可使用TRUNCATE TABLE [dbo].[DailyPrice]
)
def save_data_to_azure_db(stock_data):
server = "ey-finance.database.windows.net"
database = "finance"
username = "我的帳號"
password = "我的密碼"
driver = "{ODBC Driver 17 for SQL Server}"
with pyodbc.connect(
f"DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}"
) as conn:
with conn.cursor() as cursor:
now = datetime.datetime.now().strftime("%Y-%m-%d")
# 把Dataframe 匯入到SQL Server:
for index, row in df.iterrows():
try:
cursor.execute(
"""INSERT INTO finance.dbo.DailyPrice
(StockID, Symbol, TradeDate, OpenPrice, HighPrice, LowPrice,
ClosePrice,Volumn)
values(?,?,?,?,?,?,?,?);""",
"",
row.stock_symbol,
now,
row.open,
row.high,
row.low,
row.close,
int(row.volume.replace(",", "")),
)
conn.commit()
except Exception as e:
print(e)
return True
return False
這樣執行完畢後,當天的資料就會入到資料庫囉~