Python_讀取股票歷年經營績效_以遠東新1402為例_03_DataFrame寫入MSSQL

好吧，我很承認我很廢，腦子都在想一些有的沒的，因為本人不太愛用CSV，喜歡把資料匯入到資料庫裡面進行運算，不自覺的手癢不小心就把遠東新的經營績效資料寫入資料庫，至於為何要寫入資料庫呢？因為我是DBA呀。XD

# -*- coding: utf-8 -*-
"""
Created on Wed Jan  2 11:47:20 2019

@author: ryoliu
"""

import requests
from bs4 import BeautifulSoup
import pandas as pd
import pyodbc

url = 'https://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=1402'
headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) \
           Chrome/65.0.3325.181 Safari/537.36'}
r = requests.get(url, headers = headers)
r.encoding = 'utf-8'
soup = BeautifulSoup(r.text, 'html.parser')
rows = soup.find('div', {"id": "divFinDetail"}).find('table', {"class": "solid_1_padding_4_0_tbl"})
dfs = pd.read_html(str(rows))
df=pd.DataFrame(dfs[0]).iloc[2:19,0:3]
df.columns= ['年度','股本','財報評分']

connStr = pyodbc.connect('DRIVER={ODBC Driver 11 for SQL Server};\
                          SERVER=RYOLIU-PC;\
                          DATABASE=PythonDB;\
                          Trusted_Connection=yes')
cursor = connStr.cursor()

for index,row in df.iterrows():
    cursor.execute("INSERT INTO dbo.tab1402([年度],\
                                            [股本],\
                                            [財報評分])\
    values (?, ?,?)", row['年度'],\
    row['股本'],\
    row['財報評分'])
    connStr.commit()
cursor.close()
connStr.close()

建立資料庫語法

CREATE TABLE tab1402
(
[年度]     VARCHAR(50),
[股本]    VARCHAR(50),
[財報評分] VARCHAR(50)
)

執行結果如下：