def insertData_上市公司代號(self, df):
try:
#實例化類對象，連接數據對象
ms = MSSQL(host=self.host,
user=self.user,
pwd=self.pwd,
db=self.db)
# rowdata = {'代號': 代號, '名稱': 名稱, 'ISIN_Code': ISIN_Code, '上市日': 上市日, '市場別': 市場別, '產業別': 產業別}
for index, row in df.iterrows():
sql = "if exists (select top 1 ID from dbo.W168_Company where ID='{id}') \
update dbo.W168_Company set Name='{name}', ISIN_Code='{ISIN_Code}', 上市日='{上市日}', 市場別='{市場別}', 產業別='{產業別}', UpdateTime=getdate() where ID='{id}' \
else \
insert into dbo.W168_Company (ID, Name, ISIN_Code, 上市日, 市場別, 產業別) values ('{id}', '{name}', '{ISIN_Code}', '{上市日}', '{市場別}', '{產業別}') "
dic = dict(id=row.name,
name=row["名稱"],
ISIN_Code=row["ISIN_Code"],
上市日=row["上市日"],
市場別=row["市場別"],
產業別=row["產業別"])
sql = sql.format(
**dic
) #在 function call 裡面，** 用來將一個 dictionary展開成為positional 或是keyword參數，送進給該function.
ms.ExecuteNonQuery(sql)
except Exception as e:
hyLog().writeLog(self._APName,
logtype='Sql',
funcname="Sql",
text=e + '\r\n' + sql)
raise
def insertData_大盤(self, df):
try:
#實例化類對象，連接數據對象
ms = MSSQL(host=self.host,
user=self.user,
pwd=self.pwd,
db=self.db)
for index, row in df.iterrows():
sql = "if exists (select top 1 ID from dbo.W168_Market_Daily where ID='TWII' and Date='{date}') \n \
update dbo.W168_Market_Daily set 成交張數={成交張數}, 成交金額_仟億={成交金額_仟億}, 成交筆數={成交筆數}, 指數={指數}, 漲跌點數={漲跌點數} where ID='TWII' and Date='{date}' \n \
else \n \
insert into dbo.W168_Market_Daily (ID, Date, 成交張數, 成交金額_仟億, 成交筆數, 指數, 漲跌點數) values ('TWII', '{date}', {成交張數}, {成交金額_仟億}, {成交筆數}, {指數}, {漲跌點數}) "
datestr = str(int(row.name[0:3]) +
1911) + row.name[3:] # 民國轉西元
dic = dict(date=datestr,
成交張數=int(row["成交股數"]) / 1000,
成交金額_仟億=int(row["成交金額"]) / 100000000,
成交筆數=row["成交筆數"],
指數=row["發行量加權股價指數"],
漲跌點數=row["漲跌點數"])
sql = sql.format(
**dic
) #在 function call 裡面，** 用來將一個 dictionary展開成為positional 或是keyword參數，送進給該function.
ms.ExecuteNonQuery(sql)
except Exception as e:
hyLog().writeLog(self._APName,
logtype='Sql',
funcname="Sql",
text=str(e) + '\r\n' + sql)
raise
def getData_上市公司代號(self):
try:
#實例化類對象，連接數據對象
ms = MSSQL(host=self.host,
user=self.user,
pwd=self.pwd,
db=self.db)
sql = "select ID from dbo.W168_Company"
data = ms.ExecuteQuery(sql)
from operator import itemgetter
rtn = list(map(itemgetter(0),
data)) # 取得 tuple array 中的第一個元素到 list
return rtn
except Exception as e:
hyLog().writeLog(self._APName,
logtype='Sql',
funcname="Sql",
text=str(e) + '\r\n' + sql)
raise
def create_stock_id_today(self, daystr):
try:
#實例化類對象，連接數據對象
ms = MSSQL(host=self.host,
user=self.user,
pwd=self.pwd,
db=self.db)
sql = "insert into [dbo].[W168_Stock_DailyData] (ID, Name, Date) \
select ID, Name, '{date}' as Date from [dbo].[W168_Company] \
where ID not in (Select ID from [dbo].[W168_Stock_DailyData] where Date='{date}')"
sql = sql.format(date=daystr)
ms.ExecuteNonQuery(sql)
except Exception as e:
hyLog().writeLog(self._APName,
logtype='Sql',
funcname="Sql",
text=str(e) + '\r\n' + sql)
raise
def insertData_法人買賣超資料(self, df, date):
try:
#實例化類對象，連接數據對象
ms = MSSQL(host=self.host,
user=self.user,
pwd=self.pwd,
db=self.db)
for index, row in df.iterrows():
##"證券代號","證券名稱","外陸資買賣超股數(不含外資自營商)","外資自營商買賣超股數","投信買賣超股數","自營商買賣超股數","自營商買賣超股數(自行買賣)","自營商買賣超股數(避險)","三大法人買賣超股數"
外 = row["外陸資買賣超股數(不含外資自營商)"] + row["外資自營商買賣超股數"]
#sql = "if exists (select top 1 ID from dbo.W168_Stock_DailyData where Date=%(date)s and ID=%(id)s) \
# update dbo.W168_Stock_DailyData set 外資=%(外資)s, 投信=%(投信)s, 自營商=%(自營商)s, 三大法人=%(三大法人)s where Date=%(date)s and ID=%(id)s \
#else \
# insert into dbo.W168_Stock_DailyData (Date, ID, Name, 外資, 投信, 自營商, 三大法人) values (%(date)s, %(id)s, %(name)s, %(外資)s, %(投信)s, %(自營商)s, %(三大法人)s) "
##dic = {}.fromkeys((date,id,name,外資, 投信, 自營商, 三大法人), (date, row["證券代號"]), row["證券名稱"], 外, row["投信買賣超股數"], row["自營商買賣超股數"], row["三大法人買賣超股數"])
#sql = "if exists (select top 1 ID from dbo.W168_Stock_DailyData where Date=:1 and ID=:2) \
# update dbo.W168_Stock_DailyData set 外資=:4, 投信=:5, 自營商=:6, 三大法人=:7 where Date=:1 and ID=:2 \
#else \
# insert into dbo.W168_Stock_DailyData (Date, ID, Name, 外資, 投信, 自營商, 三大法人) values (:1, :2, :3, :4, :5, :6, :7) "
##dic = {}.fromkeys((date,id,name,外資, 投信, 自營商, 三大法人), (date, row["證券代號"]), row["證券名稱"], 外, row["投信買賣超股數"], row["自營商買賣超股數"], row["三大法人買賣超股數"])
#dic = (date,row.name,row["證券名稱"],外,row["投信買賣超股數"],row["自營商買賣超股數"],row["三大法人買賣超股數"])
sql = "if exists (select top 1 ID from dbo.W168_Stock_DailyData where ID={id} and Date='{date}') \n \
update dbo.W168_Stock_DailyData set 外資={外資}, 投信={投信}, 自營商={自營商}, 三大法人={三大法人} where ID={id} and Date='{date}' \n \
else \n \
insert into dbo.W168_Stock_DailyData (ID, Name, Date, 外資, 投信, 自營商, 三大法人) \n \
values ('{id}', '{name}', '{date}', {外資}, {投信}, {自營商}, {三大法人}) "
dic = dict(date=date.strftime("%Y-%m-%d"),
id=row.name,
name=row["證券名稱"],
外資=外,
投信=row["投信買賣超股數"],
自營商=row["自營商買賣超股數"],
三大法人=row["三大法人買賣超股數"])
sql = sql.format(**dic)
ms.ExecuteNonQuery(sql)
except Exception as e:
hyLog().writeLog(self._APName,
logtype='Sql',
funcname="Sql",
text=str(e) + '\r\n' + sql)
raise
def insertData_個股日成交(self, df, id, daystr):
try:
#實例化類對象，連接數據對象
ms = MSSQL(host=self.host,
user=self.user,
pwd=self.pwd,
db=self.db)
for index, row in df.iterrows():
try:
#補資料時暫時的code (因為下載的個股資料是一個月的,所以一次存完, 以後逐天下載就要 mark 掉底下的 code)
#以上的說明要與 DB.py 的 insertData_個股日成交() 配合
datestr = str(int(row.name[0:3]) +
1911) + row.name[3:] # 民國轉西元
# if daystr != datestr:
# continue
hyLog().writeLog(self._APName,
text="\t\t\t儲存個股日成交 [({}),({})].".format(
str(id), datestr))
sql = "if exists (select top 1 ID from dbo.[W168_Stock_DailyData] where ID='{id}' and Date='{date}') \n \
update dbo.[W168_Stock_DailyData] set 成交張數={成交張數}, 成交金額_佰萬={成交金額_佰萬}, 開盤價={開盤價}, 最高價={最高價}, 最低價={最低價}, \n \
收盤價={收盤價}, 漲跌價差={漲跌價差}, 成交筆數={成交筆數} where ID='{id}' and Date='{date}' \n \
else \n \
insert into dbo.[W168_Stock_DailyData] (ID, Date, 成交張數, 成交金額_佰萬, 開盤價, 最高價, 最低價, 收盤價, 漲跌價差, 成交筆數) \n \
values ('{id}', '{date}', {成交張數}, {成交金額_佰萬}, {開盤價}, {最高價}, {最低價}, {收盤價}, {漲跌價差}, {成交筆數}) "
dic = dict(id=id,
date=datestr,
成交張數=int(row["成交股數"]) / 1000,
成交金額_佰萬=int(row["成交金額"]) / 1000000,
開盤價=row["開盤價"],
最高價=row["最高價"],
最低價=row["最低價"],
收盤價=row["收盤價"],
漲跌價差=row["漲跌價差"],
成交筆數=row["成交筆數"])
sql = sql.format(
**dic
) #在 function call 裡面，** 用來將一個 dictionary展開成為positional 或是keyword參數，送進給該function.
ms.ExecuteNonQuery(sql)
except Exception as e:
hyLog().writeLog(self._APName,
logtype='Sql',
funcname="Sql",
text=str(e) + '\r\n' + sql)
raise
except Exception as e:
hyLog().writeLog(self._APName,
logtype='Sql',
funcname="Sql",
text=str(e) + '\r\n' + sql)
raise