Board logo

標題: python 融資融券彙總 (全部)"資料 [打印本頁]
作者: lpk187    時間: 2016-9-22 16:22     標題: python 融資融券彙總 (全部)"資料

還不會製作執行檔！其內容僅供參考之
  1. #-*- coding:utf-8 -*-

  3. '''
  4. 本程序由 Python 3.52 寫成，為作者參考麻辣家族討論區 "python上市櫃三大法人買賣超日報資料下載" 討論內容練習
  5. 其內容大部份參考 zyzzyva大大、c_c_lai 大大 、koshi0413 之討論內容，感謝！
  6. 本程序會自動生成 Access 的 twsedata.mdb資料庫，及建立各所需之資料表，但只能下載"融資融券彙總 (全部)"資料
  7. ㄟ！沒有信用交易統計資料 ，只建立其資料表
  8. 本程序需要 pip 安裝 requests 、BeautifulSoup4、pypyodbc
  9. 以及需下載 pywin32 : https://sourceforge.net/projects/pywin32/files/pywin32/Build%20220/
  10. 使用方法：只在 input 時 ，輸入開始擷取日期及結束擷取日期即可，第一次執行時，會建構資料庫，而後只增加資料，
  11. 每日資料擷取到儲存至資料庫的時間約 21秒(依作者電腦執行計時 )
  12. 本程序僅供參考，不負責更新內容，不負責除錯(因為我也是新手，除錯對我來說太#%%$...)，搞壞使用者電腦，也不關作者的事！！
  13. 作者：麻辣家族討論區 lpk187
  14. 完成日期：2016/9/22
  15. '''

  17. import requests
  18. from bs4 import BeautifulSoup
  19. import random
  20. import os
  21. import win32com.client
  22. import pypyodbc
  23. import datetime
  24. import time

  26. # 隨機更改 headers
  27. def head_random():
  28.       for i in range(10):
  29.             hs = ['Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36',
  30.                    'Mozilla/5.0 (iPhone; U; CPU iPhone OS 3_0 like Mac OS X; en-us) AppleWebKit/528.18 (KHTML, like Gecko) Version/4.0 Mobile/7A341 Safari/528.16',
  31.                    'Mozilla/5.0 (Linux; U; Android 4.1.2; zh-tw; GT-I9300 Build/JZO54K) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile Safari/534.30',
  32.                    'Mozilla/5.0 (iPad; U; CPU OS 3_2 like Mac OS X; en-us) AppleWebKit/531.21.10 (KHTML, like Gecko) Version/4.0.4 Mobile/7B334b Safari/531.21.10'
  33.                    'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0)']
  34.             hes = {"User-Agent":random.choice(hs)}
  35.       return hes

  37. # 集台證所融資融券餘額全部資料
  38. def read_network_data(date,from_number):
  39.       url = 'http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php'
  40.       head =head_random()
  41.       payload = {"download":'',
  42.                  "qdate":date,
  43.                  "selectType":"ALL"}
  44.       res = requests.post(url, headers=head, data=payload)
  45.       soup=BeautifulSoup(res.text, "lxml")
  46.       tab = soup.select('tbody')[from_number] # 2個表格之2
  47.       regs=[] # 初始陣列
  48.    
  49.       for i in range(len(tab.select('tr')[0].select('td'))): # 定義2維陣列維度
  50.             regs.append([])

  52.       for tr in tab.select('tr'): #擷取表格資料
  53.             for i in range(len(tab.select('tr')[0].select('td'))):
  54.                   regs[i].append(tr.select('td')[i].text)
  55.       return regs

  57. def conn_db():
  58.       Conn = win32com.client.Dispatch("ADODB.Connection")
  59.       strMDB ='Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + os.getcwd() + '\\twsedata.mdb;'
  60.       Conn.ConnectionString = strMDB
  61.       Conn.Open()
  62.       return Conn

  64. def check_db_exists(regs):
  65.       if not os.path.exists('twsedata.mdb'):
  66.             pypyodbc.win_create_mdb( os.getcwd() + '\\twsedata.mdb')
  67.             Conn=conn_db()
  68.             sql = "CREATE TABLE 股票(股票代號  char(10) PRIMARY KEY,股票名稱  char(20));"
  69.             Conn.Execute(sql)
  70.             sql = "CREATE TABLE  融資融券信用交易統計(日期  datetime,項目  char(20),買進 INTEGER,買出 INTEGER,現金（券）償還 INTEGER,前日餘額 INTEGER,今日餘額 INTEGER);"
  71.             Conn.Execute(sql)
  72.       n = 0
  73.       Conn=conn_db()
  74.       for i in regs[0]:
  75.             try:
  76.                   Conn.Execute('select * from ' + i); # table does not exist
  77.             except Exception as e:
  78.                   sql="CREATE TABLE " + i + "(日期  datetime,"
  79.                   sql= sql + "資買進  INTEGER,"
  80.                   sql= sql + "資賣出 INTEGER,"
  81.                   sql= sql + "資現金償還 INTEGER,"
  82.                   sql= sql + "資前日餘額 INTEGER,"
  83.                   sql= sql + "資今日餘額 INTEGER,"
  84.                   sql= sql + "資限額 INTEGER,"
  85.                   sql= sql + "券買進  INTEGER,"
  86.                   sql= sql + "券賣出 INTEGER,"
  87.                   sql= sql + "券現金償還 INTEGER,"
  88.                   sql= sql + "券前日餘額 INTEGER,"
  89.                   sql= sql + "券今日餘額 INTEGER,"
  90.                   sql= sql + "券限額 INTEGER,"
  91.                   sql= sql + "資券互抵 INTEGER,"
  92.                   sql= sql + "註記 char(5));"
  93.                   Conn.Execute(sql)
  94.                   rs = win32com.client.Dispatch('ADODB.RecordSet')
  95.                   rs.ActiveConnection = Conn
  96.                   rs.Open('select * from 股票', Conn, 3, 3)
  97.                   rs.AddNew()
  98.                   rs.Fields.Item(0).Value = regs[0][n]
  99.                   rs.Fields.Item(1).Value = regs[1][n]
  100.                   rs.Update()
  101.             n += 1

  103. def add_data(regs,date):
  104.       Conn=conn_db()
  105.       for i in range(len(regs[0])):
  106.             rs = win32com.client.Dispatch('ADODB.RecordSet')
  107.             rs.ActiveConnection = Conn
  108.             sql='select * from ' + regs[0][i]
  109.             rs.Open(sql, Conn, 3, 3)
  110.             for j in range(2,len(regs)):
  111.                   if j==2:
  112.                         rs1 = win32com.client.Dispatch('ADODB.RecordSet')
  113.                         rs1.ActiveConnection = Conn
  114.                         sql="SELECT * FROM " + regs[0][i] + " WHERE 日期 = #" + date + "#;"
  115.                         rs1.Open(sql,Conn,3,3)
  116.                         if rs1.EOF:
  117.                               rs.AddNew()
  118.                               rs.Fields.Item(0).Value = date
  119.                               rs.Fields.Item(j-1).Value = regs[j][i]
  120.                               rs1.Close()
  121.                         else:
  122.                               rs1.Close()
  123.                               break
  124.                   rs.Fields.Item(j-1).Value = regs[j][i]
  125.             rs.Update()

  127. # 以下為程式的啟始點：
  128. StartDate=input('請輸入開始擷取日期(西元日期如：2016/1/1)：')
  129. EndDate=input('請輸入結束擷取日期(西元日期如：2016/1/31)：')
  130. #處理日期還真的很麻煩
  131. ts=time.time()
  132. StartDate = time.strptime(StartDate, "%Y/%m/%d")
  133. EndDate =time.strptime(EndDate, "%Y/%m/%d")
  134. StartDate = datetime.date(StartDate[0], StartDate[1], StartDate[2])
  135. EndDate =  datetime.date(EndDate[0], EndDate[1], EndDate[2])
  136. RangeDate = datetime.timedelta(days = 1)
  137. while StartDate <= EndDate:
  138.       yy,mm,dd=str(StartDate).split('-')
  139.       dat=datetime.datetime(int(yy), int(mm), int(dd))
  140.       dd=dat.strftime('%Y/%m/%d')
  141.       year=str(int(dd[0:4])-1911)
  142.       date=dd.replace(dd[0:4], year)
  143.       print('目前執行網路擷取',date ,'日的資料，請稍後...')
  144.       re=read_network_data(date,1)
  145.       if re[0][0]=='查無資料':
  146.             print(date,'日，可能為休市日，查無資料。')
  147.             StartDate = StartDate + RangeDate
  148.             continue
  149.       print('檢查是否有新股票加入，請稍後...')
  150.       check_db_exists(regs=re)
  151.       print('將資料寫入資料庫中，請稍後...')
  152.       add_data(regs=re,date=dd)
  153.       StartDate = StartDate + RangeDate
  154.       te = time.time()
  155.       print('截至目前，時間已耗費：', int(te - ts),'秒')

  157. print('執行完畢！')
複製代碼
作者: c_c_lai    時間: 2016-9-22 20:11

回復 1# lpk187
太棒了！
先來試試看，謝謝囉！
作者: c_c_lai    時間: 2016-9-22 21:13

回復 1# lpk187
請問 pypyodbc 如何下再安裝？
我安裝了 Anaconda 內附的 pyodbc 結果還是一樣。
[attach]25299[/attach]
作者: lpk187    時間: 2016-9-22 22:00

本帖最後由 lpk187 於 2016-9-22 22:04 編輯

回復 3# c_c_lai


    pypyodbc 和pyodbc是不同的，要下載pypyodbc 還是要在CMD 中下載
pip install pypyodbc


[attach]25300[/attach]

pypyodbc 其用法我也不會，不過當時只看到一句指令就可以產生資料庫，所以我就下載了
其語法為    pypyodbc.win_create_mdb( os.getcwd() + '\\twsedata.mdb')
作者: lpk187    時間: 2016-9-22 22:33

以下是我執行的過程：
[attach]25305[/attach]

[attach]25306[/attach]

[attach]25307[/attach]

[attach]25308[/attach]
作者: koshi0413    時間: 2016-9-22 23:46

本帖最後由 koshi0413 於 2016-9-23 00:00 編輯

厲害，小弟學習先，有問題在發問~

對了，先提問一下  acc 的資料查詢速度  跟  sqlite3 比較一下，何者快呢？

小弟是程式新手，所以什麼都不懂，會亂發問，請見諒嘿~~
作者: lpk187    時間: 2016-9-23 00:26

回復 6# koshi0413


    查詢速度，應該和資料庫本身有很大的關係！Access本身，就只是個人電腦所使用的小小資料庫，
當然要處理像股市資訊的話，我不會推薦用Access ，雖然微軟號稱可達極限為2G，雖然我沒試過達到2G極限值的速度，
不過依我想大概算它10%好了，其速度大約就會有明顯的差異了。
還有各方模塊，都有其優勢，當我正在學習python時，有某個模塊的某函數比另外模塊的函數強時，我會用這個模塊，
當然，這只是指我目前正在學習階段的想法，(因為還不是很瞭解其函數用法)
作者: c_c_lai    時間: 2016-9-23 07:59

回復 4# lpk187
Thanks a lot!
[attach]25309[/attach]
作者: zyzzyva    時間: 2016-9-23 08:50

回復 1# lpk187
哇，最近也想研究一下資料庫，剛好L大就發了這篇，3Q~
作者: c_c_lai    時間: 2016-9-24 10:31

回復 5# lpk187
昨天測了一下， 從 2016/1/1 ~ 2016/9/22，
結果發現每筆資料只寫入到 2016/8/29。
這是正確嗎？
[attach]25326[/attach]
[attach]25327[/attach]
這實在是很棒的實用範例，再次謝謝你的分享！
作者: lpk187    時間: 2016-9-24 12:53

回復 10# c_c_lai


    [attach]25329[/attach]
作者: c_c_lai    時間: 2016-9-24 13:00

回復 11# lpk187
我再試試看 !
(試著先將資料庫移除、在執行)>
作者: c_c_lai    時間: 2016-9-25 10:16

本帖最後由 c_c_lai 於 2016-9-25 10:21 編輯

回復 11# lpk187
重新測試結果：
[attach]25339[/attach]
謝謝你！
如果要從資料庫讀取出資料又該如何撰寫，
譬如同步'寫入 pandas 裡？ 或是其他方式？
作者: lpk187    時間: 2016-9-25 11:19

回復 13# c_c_lai


    在python 中，我也還是在學習，所以知道的甚少
要從資料庫中拿出來，以Pywin32 ADODB的用法和VBA很類似

你可以參考http://wiki.alarmchang.com/index.php?title=Python 裡面有很多例子參考
甚至控制Excel都可以，就要看從資料庫中提取出來後要做什麼(因為我沒玩股票，也不知道這些數據該用什麼？這問題可能要請教各位大大了)
作者: koshi0413    時間: 2016-9-25 14:25

本帖最後由 koshi0413 於 2016-9-25 14:30 編輯

回復 13# c_c_lai

給您參考  SQLite3 導入 Excel
acc作法應該也差不多

注意!!! 這是一次全導入，所以資料量太大，會很慢
應該可以導入局部，只是小弟尚未用到，還沒深入
  1. Sub SQLite3_to_Excel()
  2. 印像中可以在 "SELECT  *  FROM  " & TableName & " "  加入只提取十行

  4. Dim cn, rs, f, SQLName, TableName, ix%, SheetsName

  6.     SQLName = "C:\Users\Koshi\Documents\Python Scripts\SQLite3\2330.sqlite"
  7.     TableName = "sql表格名稱"
  8.     SheetsName = "Excel工作表名稱"
  9.     ix = 10 '開始於第十行
  10.    
  11.     Sheets(SheetsName).Select
  12.     Set cn = CreateObject("adodb.connection")
  13.         cn.Open ("Driver={SQLite3 ODBC Driver};database=" & SQLName)     '開啟sqlite指定資料庫，路徑與檔案名稱要對
  14.         Set rs = cn.Execute("SELECT  *  FROM  " & TableName & " ")       '寫出SQL查詢語法
  15.             Sheets(SheetsName).Cells.Delete  '清除工作表資料
  16.             For f = 0 To rs.Fields.Count - 1
  17.                 Sheets(SheetsName).Cells(ix, f + 1).Value = rs.Fields(f).Name   '導入欄位名稱
  18.             Next
  19.             Sheets(SheetsName).Cells(ix + 1, 1).CopyFromRecordset (rs)        '導入欄位資料
  20.         cn.Close
  21.     Set rs = Nothing
  22.     Set cn = Nothing
  23. End Sub
複製代碼
作者: koshi0413    時間: 2016-9-25 16:07

回復  c_c_lai

給您參考  SQLite3 導入 Excel
acc作法應該也差不多

注意!!! 這是一次全導入，所以資 ...
koshi0413 發表於 2016-9-25 14:25


找到了 此代碼為 joey0415 大大寫的
http://forum.twbts.com/viewthread.php?tid=9984
作者: lpk187    時間: 2016-9-25 17:20

本帖最後由 lpk187 於 2016-9-25 17:22 編輯

回復 13# c_c_lai


研究了一下   pandas 取出 再導入到CSV 或Excel
  1. import pypyodbc
  2. import pandas as pd

  4. MDB = 'D:\\Python\\融資融券彙總\\twsedata.mdb'
  5. DRV = '{Microsoft Access Driver (*.mdb, *.accdb)}'
  6. con = pypyodbc.connect('DRIVER={};DBQ={}'.format(DRV, MDB))
  7. data = pd.read_sql('select * from 1220', con) # 取出
  8. data.to_excel('1220.xlsx', sheet_name='1220')  #導入到Excel
  9. data.to_csv('1220.csv') #導入到 CSV
  10. data
複製代碼
作者: c_c_lai    時間: 2016-9-26 15:42

回復 17# lpk187
測試結果如下：
[attach]25352[/attach]
從多個角度來測試，實在太棒了。
謝謝囉！
作者: c_c_lai    時間: 2016-9-27 10:16

回復 17# lpk187
請教如果換成 15# 樓 koshi0413 所說的 SQLite3
要如何連結？
作者: lpk187    時間: 2016-9-27 11:18

回復 19# c_c_lai


   不好意思！沒用過SQLite3，不知怎麼回答你
你可以參考http://www.runoob.com/sqlite/sqlite-python.html
以及https://docs.python.org/3.5/library/sqlite3.html
作者: c_c_lai    時間: 2016-9-27 12:57

回復 20# lpk187
非常感謝你的分享，內容很棒。
我再來自習一下。
作者: c_c_lai    時間: 2016-9-28 07:02

本帖最後由 c_c_lai 於 2016-9-28 07:05 編輯

回復 1# lpk187
請問大大：
rs.Open('select * from 股票', Conn, 3, 3)
它的意涵為何？
rs.Fields.Item(0).Value = regs[0][n]
rs.Fields.Item(1).Value = regs[1][n]
又為何意？
最近颱風天便抽點時間撰研，謝謝你！
作者: lpk187    時間: 2016-9-28 11:05

本帖最後由 lpk187 於 2016-9-28 11:07 編輯

回復 22# c_c_lai


   這是我網路找到的資料 ，給你參考
http://www.asjh.tn.edu.tw/xuploa ... hap02/recordset.htm
Python語法和VBA差不多

而rs.Fields.Item(0).Value = regs[0][n]

rs.Fields.Item(0).Value ==> Fields:        查詢結果中欄位物件的集合, (也就是一個暫存的序列) ，Item(0)序列中第一個項目(也可以用資料表的項目名稱：例如：Item('股票代號'))
regs[0][n] ：為爬回的表格的陣列
在這之前有句 rs.AddNew()  也就是新增一列 ，所以rs.Fields 就像是一個一維陣列，既然是一維陣列，所以它還只是一個暫存物件 所以最後，還要rs.Update()才會存入資料庫的資料表中
作者: c_c_lai    時間: 2016-9-28 13:46

回復 23# lpk187
Python 好像無法一行一行 Debug？
所以才會有這麼多的問題，不好意思！
謝謝你的指導！
作者: zyzzyva    時間: 2016-9-28 14:26

回復 24# c_c_lai
pycharm跟visual studio有逐行執行的功能，可以試試看。
作者: lpk187    時間: 2016-9-28 15:30

回復 24# c_c_lai

習慣了VBA的區域變數視窗，以及逐步執行來除錯，剛使用python 真的很不習慣，
有時，比如說顯示100行出錯，結果是某一行的變數內容寫錯，真的很不容易除錯
就如 zyzzyva 大大說的，也可以用visual studio來寫，而且功能也蠻多的
作者: c_c_lai    時間: 2016-9-28 17:28

本帖最後由 c_c_lai 於 2016-9-28 17:29 編輯

回復 26# lpk187
回復 25# zyzzyva
[attach]25377[/attach]
真傷腦筋！
作者: lpk187    時間: 2016-9-28 21:10

回復 19# c_c_lai

這次是以SQLite3 資料庫寫成的，其用法，和 ADODB有些許的差異，因SQLite3，是python 內建函數，所以不用另裝
其中在資料擷取方面，因擷取下來的資料，有空白 以及 千分位符號 "," 若不處理這些，python 會產生許多錯誤，這些問題也已處理
  1. #-*- coding:utf-8 -*-



  5. import requests
  6. from bs4 import BeautifulSoup
  7. import random
  8. import os
  9. import sqlite3
  10. import datetime
  11. import time

  13. # 隨機更改 headers
  14. def head_random():
  15.       for i in range(10):
  16.             hs = ['Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36',
  17.                    'Mozilla/5.0 (iPhone; U; CPU iPhone OS 3_0 like Mac OS X; en-us) AppleWebKit/528.18 (KHTML, like Gecko) Version/4.0 Mobile/7A341 Safari/528.16',
  18.                    'Mozilla/5.0 (Linux; U; Android 4.1.2; zh-tw; GT-I9300 Build/JZO54K) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile Safari/534.30',
  19.                    'Mozilla/5.0 (iPad; U; CPU OS 3_2 like Mac OS X; en-us) AppleWebKit/531.21.10 (KHTML, like Gecko) Version/4.0.4 Mobile/7B334b Safari/531.21.10'
  20.                    'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0)']
  21.             hes = {"User-Agent":random.choice(hs)}
  22.       return hes

  24. # 集台證所融資融券餘額全部資料
  25. def read_network_data(date,from_number):
  26.       url = 'http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php'
  27.       head =head_random()
  28.       payload = {"download":'',
  29.                  "qdate":date,
  30.                  "selectType":"ALL"}
  31.       res = requests.post(url, headers=head, data=payload)
  32.       soup=BeautifulSoup(res.text, "lxml")
  33.       tab = soup.select('tbody')[from_number] # 2個表格之2
  34.       regs=[] # 初始陣列
  35.    
  36.       for i in range(len(tab.select('tr')[0].select('td'))): # 定義2維陣列維度
  37.             regs.append([])
  38.       n=0
  39.       for tr in tab.select('tr'): #擷取表格資料
  40.             for i in range(len(tab.select('tr')[0].select('td'))):
  41.                   if n==0 or n==1 or n==15:      #把文字串中的空白文字去除掉
  42.                         strs=tr.select('td')[i].text
  43.                         strs=str(strs.replace(' ',''))
  44.                         regs[i].append(strs)
  45.                   else:    # 把數字中的千分符號 "," 去除掉
  46.                         ints=tr.select('td')[i].text
  47.                         ints=str(ints.replace(',',''))
  48.                         regs[i].append(ints)
  49.                   n +=1
  50.             n=0
  51.       return regs


  54. def check_db_exists(regs):
  55.       if not os.path.exists('twsedata.db'):   # 檢查twsedata.db是否存在(和此程式碼相同的資料夾)
  56.             conn = sqlite3.connect('twsedata.db')  # 如果沒有，就建立一個空白資料庫，(sqlite3.connect) 也是連結語句，有資料庫就連結，沒有就建立
  57.             sql = "CREATE TABLE 股票(股票代號  char(10) PRIMARY KEY,股票名稱  char(20));"
  58.             conn.execute(sql) #在新資料庫建立空白資料表 (股票)
  59.             sql = "CREATE TABLE  融資融券信用交易統計( \
  60.                                                         日期  datetime, \
  61.                                                         項目  char(20), \
  62.                                                         買進 INTEGER, \
  63.                                                         買出 INTEGER, \
  64.                                                         現金（券）償還 INTEGER, \
  65.                                                         前日餘額 INTEGER, \
  66.                                                         今日餘額 INTEGER);"
  67.             conn.execute(sql) #在新資料建立 空白資料表(融資融券信用交易統計)
  68.             conn.commit() # 儲存
  69.             conn.close() #關閉
  70.             
  71.       n=0
  72.       conn = sqlite3.connect('twsedata.db') # conn和上一個 if ..中的 conn是不一樣的 ，上一個是沒有資料庫時才作用，為局部變數而此處，則為以下使用的全局變數
  73.       for i in regs[0]:  #以爬回來的資料陣列的第一列為迴圈
  74.             try:      #先嘗試查詢 i 變數的資料表是否存在，若是不存在會發出 Exception 的錯誤訊息
  75.                   c = conn.cursor()
  76.                   sql = "select * from [" + i + "]"
  77.                   c.execute(sql) # table does not exist
  78.                   c.close()
  79.             except Exception as e:  #當try 發出錯誤訊息(也就是讀不到  i 資料表)，此時建立一個 i 資料表
  80.                   c = conn.cursor()
  81.                   sql="CREATE TABLE [" + i + "](日期  DATETIME PRIMARY KEY, \
  82.                                                                               資買進  INTEGER, \
  83.                                                                               資賣出 INTEGER, \
  84.                                                                               資現金償還 INTEGER, \
  85.                                                                               資前日餘額 INTEGER, \
  86.                                                                               資今日餘額 INTEGER, \
  87.                                                                               資限額 INTEGER, \
  88.                                                                               券買進  INTEGER, \
  89.                                                                               券賣出 INTEGER, \
  90.                                                                               券現金償還 INTEGER, \
  91.                                                                               券前日餘額 INTEGER, \
  92.                                                                               券今日餘額 INTEGER, \
  93.                                                                               券限額 INTEGER, \
  94.                                                                               資券互抵 INTEGER, \
  95.                                                                               註記 char(5));"
  96.                   c.execute(sql) #建立 i 資料表
  97.                   sql="INSERT INTO 股票(股票代號, 股票名稱) \
  98.                   VALUES ('" + regs[0][n] + "','" + regs[1][n] + "');"
  99.                   c.execute(sql)  # 在股票資料表中寫入股票代號、股票名稱
  100.                   c.close() # 關閉 c 物件
  101.             n += 1
  102.       conn.commit()
  103.       conn.close()
  104. def add_data(regs,date):
  105.       conn = sqlite3.connect('twsedata.db')
  106.       for i in range(len(regs[0])):
  107.             try: #檢查某日期是否存在於資料表中，當某日期存在時，跳離這次的循環，這裡指的僅是這次，不是跳離 for
  108.                   c = conn.cursor()
  109.                   sql="SELECT * FROM [" + regs[0][i] + "] WHERE 日期 = '" + date + "';"
  110.                   c.execute(sql)
  111.                   c.close()
  112.             except Exception as e: #當日期不存在時，寫入資料到資料表中
  113.                   c = conn.cursor()
  114.                   # 日期格式，在sqlite3中，是以文字型態存入，雖然有提供DATETIME，但非以日期型態存入，日後提出，嗯！需再參閱sqlite3的日期處理
  115.                   
  116.                   sql="INSERT INTO [" + regs[0][i] + "](日期,資買進,資賣出,資現金償還,資前日餘額, \
  117.                                                                                          資今日餘額,資限額,券買進,券賣出,券現金償還, \
  118.                                                                                          券前日餘額,券今日餘額,券限額,資券互抵,註記) \
  119.                                                                        VALUES('" + date + "'," + regs[2][i] + "," + regs[3][i] + ", \
  120.                                                                                           " + regs[4][i] + ",  " + regs[5][i] + "," + regs[6][i] + ", \
  121.                                                                                           " + regs[7][i] + "," + regs[8][i] + ",  " + regs[9][i] + ", \
  122.                                                                                           " + regs[10][i] + "," + regs[11][i] + "," + regs[12][i] + ", \
  123.                                                                                           " + regs[13][i] + "," + regs[14][i]+ ",'" + regs[15][i] + "') ;"
  124.                   c.execute(sql)
  125.                   c.close()
  126.             else:
  127.                    break
  128.       conn.commit()
  129.       conn.close()

  131. # 以下為程式的啟始點：
  132. StartDate=input('請輸入開始擷取日期(西元日期如：2016/1/1)：')
  133. EndDate=input('請輸入結束擷取日期(西元日期如：2016/1/31)：')
  134. #處理日期還真的很麻煩
  135. ts=time.time()
  136. StartDate = time.strptime(StartDate, "%Y/%m/%d")
  137. EndDate =time.strptime(EndDate, "%Y/%m/%d")
  138. StartDate = datetime.date(StartDate[0], StartDate[1], StartDate[2])
  139. EndDate =  datetime.date(EndDate[0], EndDate[1], EndDate[2])
  140. RangeDate = datetime.timedelta(days = 1)
  141. while StartDate <= EndDate:
  142.       yy,mm,dd=str(StartDate).split('-')
  143.       dat=datetime.datetime(int(yy), int(mm), int(dd))
  144.       dd=dat.strftime('%Y/%m/%d')
  145.       year=str(int(dd[0:4])-1911)
  146.       date=dd.replace(dd[0:4], year)
  147.       print('目前執行網路擷取',date ,'日的資料，請稍後...')
  148.       re=read_network_data(date,1)
  149.       if re[0][0]=='查無資料':
  150.             print(date,'日，可能為休市日，查無資料。')
  151.             StartDate = StartDate + RangeDate
  152.             continue
  153.       print('檢查是否有新股票加入，請稍後...')
  154.       check_db_exists(regs=re)
  155.       print('將資料寫入資料庫中，請稍後...')
  156.       add_data(regs=re,date=dd)
  157.       StartDate = StartDate + RangeDate
  158.       te = time.time()
  159.       print('截至目前，時間已耗費：', int(te - ts),'秒')

  161. print('執行完畢！')
複製代碼
作者: c_c_lai    時間: 2016-9-29 07:44

回復 28# lpk187
測試後結果，如畫面：
[attach]25379[/attach]
我也對改用 Sqlite 3 資料庫感到興趣，
我再來好好研究一番，謝謝你！
作者: c_c_lai    時間: 2016-9-29 07:58

回復 28# lpk187
經檢查資料庫裡的資料錄為零。
[attach]25381[/attach]
作者: lpk187    時間: 2016-9-29 09:32

本帖最後由 lpk187 於 2016-9-29 09:33 編輯

回復 30# c_c_lai

剛測試了一下，在Jupyther Notebook 上執行。
執行後沒錯誤產生，但卻是沒有資料存進去，
但一般執行卻沒有問題。
你試試一般執行，看結果如何
[attach]25388[/attach]
作者: koshi0413    時間: 2016-9-29 10:05

應該是不會有導入資料的問題，颱風天小弟就把sqlite3從0~500MB了
其中會發生錯誤沒錯，有時是網頁資料格式不一，有時是網頁資料空格，不然就是多了什麼文字讓循環錯誤(字串的處理很重要，大都錯在這個，加個判別式即可)
記得同一資料庫(就算是不同表格)，不要用2支程式跑
Sqlite3好像處理會來不及，程式就中斷了
以上是小弟的個人經驗∼
PS：小弟用Jupyter 跑的
作者: lpk187    時間: 2016-9-29 12:49

本帖最後由 lpk187 於 2016-9-29 12:53 編輯

回復 30# c_c_lai

一個很奇怪的問題，我也搞不懂！
以下面這段程式碼來說，照理說，當
try:
       c = conn.cursor()
       sql="SELECT * FROM [" + regs[0] + "] WHERE 日期 = '" + date + "';"
       c.execute(sql)
       c.close()
找不到此日期，應該會產生錯誤，傳給except Exception as e: 執行才對，但
在Jupyter NoteBook 卻是不會產生錯誤(以一般的pyhton 會傳回錯誤訊息)
我試著去SQLiteStudio中打上檢詢錯誤的日期，雖然找不到，但也沒有產生錯誤，
所以我改了程式碼，你試看看，請複製以下代碼
其中也修正擷取網路資料的迴圈，現在擷取變很快
  1. #-*- coding:utf-8 -*-
  2. import requests
  3. from bs4 import BeautifulSoup
  4. import random
  5. import os
  6. import sqlite3
  7. import datetime
  8. import time

  10. # 隨機更改 headers
  11. def head_random():
  12.     for i in range(10):
  13.             hs = ['Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36',
  14.                    'Mozilla/5.0 (iPhone; U; CPU iPhone OS 3_0 like Mac OS X; en-us) AppleWebKit/528.18 (KHTML, like Gecko) Version/4.0 Mobile/7A341 Safari/528.16',
  15.                    'Mozilla/5.0 (Linux; U; Android 4.1.2; zh-tw; GT-I9300 Build/JZO54K) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile Safari/534.30',
  16.                    'Mozilla/5.0 (iPad; U; CPU OS 3_2 like Mac OS X; en-us) AppleWebKit/531.21.10 (KHTML, like Gecko) Version/4.0.4 Mobile/7B334b Safari/531.21.10'
  17.                    'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0)']
  18.             hes = {"User-Agent":random.choice(hs)}
  19.     return hes

  21. # 集台證所融資融券餘額全部資料
  22. def read_network_data(date,from_number):
  23.     url = 'http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php'
  24.     head =head_random()
  25.     payload = {"download":'',
  26.             "qdate":date,
  27.             "selectType":"ALL"}
  28.     res = requests.post(url, headers=head, data=payload)
  29.     soup=BeautifulSoup(res.text, "lxml")
  30.     tab = soup.select('tbody')[from_number] # 2個表格之2
  31.     regs=[] # 初始陣列
  32.       
  33.     le=len(tab.select('tr')[0].select('td'))
  34.     for i in range(le): # 定義2維陣列維度
  35.         regs.append([])
  36.     for tr in tab.select('tr'): #擷取表格資料
  37.         for i in range(le):
  38.             if i==0 or i==1 or i==15:      #把文字串中的空白文字去除掉
  39.                 strs=tr.select('td')[i].text
  40.                 strs=str(strs.replace(' ',''))
  41.                 regs[i].append(strs)
  42.             else:    # 把數字中的千分符號 "," 去除掉
  43.                 ints=tr.select('td')[i].text
  44.                 ints=str(ints.replace(',',''))
  45.                 regs[i].append(ints)
  46.     return regs

  48. def check_db_exists(regs):
  49.     if not os.path.exists('twsedata.db'):   # 檢查twsedata.db是否存在(和此程式碼相同的資料夾)
  50.         conn = sqlite3.connect('twsedata.db')  # 如果沒有，就建立一個空白資料庫，(sqlite3.connect) 也是連結語句，有資料庫就連結，沒有就建立
  51.         sql = "CREATE TABLE 股票(股票代號  char(10) PRIMARY KEY,股票名稱  char(20));"
  52.         conn.execute(sql) #在新資料庫建立空白資料表 (股票)
  53.         sql = "CREATE TABLE  融資融券信用交易統計( \
  54.                                 日期  datetime, \
  55.                                 項目  char(20), \
  56.                                 買進 INTEGER, \
  57.                                 買出 INTEGER, \
  58.                                 現金（券）償還 INTEGER, \
  59.                                 前日餘額 INTEGER, \
  60.                                 今日餘額 INTEGER);"
  61.         conn.execute(sql) #在新資料建立 空白資料表(融資融券信用交易統計)
  62.         conn.commit() # 儲存
  63.         conn.close() #關閉
  64.             
  65.     n=0
  66.     conn = sqlite3.connect('twsedata.db') # conn和上一個 if ..中的 conn是不一樣的 ，上一個是沒有資料庫時才作用，為局部變數而此處，則為以下使用的全局變數
  67.     for i in regs[0]:  #以爬回來的資料陣列的第一列為迴圈
  68.         try:      #先嘗試查詢 i 變數的資料表是否存在，若是不存在會發出 Exception 的錯誤訊息
  69.             c = conn.cursor()
  70.             sql = "select * from [" + i + "]"
  71.             c.execute(sql) # table does not exist
  72.             c.close()
  73.         except Exception as e:  #當try 發出錯誤訊息(也就是讀不到  i 資料表)，此時建立一個 i 資料表
  74.             c = conn.cursor()
  75.             sql="CREATE TABLE [" + i + "](日期  DATETIME PRIMARY KEY, \
  76.                                         資買進  INTEGER, \
  77.                                         資賣出 INTEGER, \
  78.                                         資現金償還 INTEGER, \
  79.                                         資前日餘額 INTEGER, \
  80.                                         資今日餘額 INTEGER, \
  81.                                         資限額 INTEGER, \
  82.                                         券買進  INTEGER, \
  83.                                         券賣出 INTEGER, \
  84.                                         券現金償還 INTEGER, \
  85.                                         券前日餘額 INTEGER, \
  86.                                         券今日餘額 INTEGER, \
  87.                                         券限額 INTEGER, \
  88.                                         資券互抵 INTEGER, \
  89.                                         註記 char(5));"
  90.             c.execute(sql) #建立 i 資料表
  91.             sql="INSERT INTO 股票(股票代號, 股票名稱) \
  92.                 VALUES ('" + regs[0][n] + "','" + regs[1][n] + "');"
  93.             c.execute(sql)  # 在股票資料表中寫入股票代號、股票名稱
  94.             c.close() # 關閉 c 物件
  95.         n += 1
  96.     conn.commit()
  97.     conn.close()
  98. def add_data(regs,date):
  99.     conn = sqlite3.connect('twsedata.db')
  100.     for i in range(len(regs[0])):
  101.         try: #檢查某日期是否存在於資料表中，當某日期存在時，跳離這次的循環，這裡指的僅是這次，不是跳離 for
  102.             c = conn.cursor()
  103.             sql="INSERT INTO [" + regs[0][i] + "](日期,資買進,資賣出,資現金償還,資前日餘額, \
  104.                                                 資今日餘額,資限額,券買進,券賣出,券現金償還, \
  105.                                                 券前日餘額,券今日餘額,券限額,資券互抵,註記) \
  106.                                         VALUES('" + date + "'," + regs[2][i] + "," + regs[3][i] + ", \
  107.                                                 " + regs[4][i] + ",  " + regs[5][i] + "," + regs[6][i] + ", \
  108.                                                 " + regs[7][i] + "," + regs[8][i] + ",  " + regs[9][i] + ", \
  109.                                                 " + regs[10][i] + "," + regs[11][i] + "," + regs[12][i] + ", \
  110.                                                 " + regs[13][i] + "," + regs[14][i]+ ",'" + regs[15][i] + "') ;"

  112.             c.execute(sql)
  113.         except Exception as e: #當日期不存在時，寫入資料到資料表中
  114.             pass
  115.         else:
  116.             c.close()
  117.             
  118.     conn.commit()
  119.     conn.close()

  121. # 以下為程式的啟始點：
  122. StartDate=input('請輸入開始擷取日期(西元日期如：2016/1/1)：')
  123. EndDate=input('請輸入結束擷取日期(西元日期如：2016/1/31)：')
  124. #處理日期還真的很麻煩
  125. ts=time.time()
  126. StartDate = time.strptime(StartDate, "%Y/%m/%d")
  127. EndDate =time.strptime(EndDate, "%Y/%m/%d")
  128. StartDate = datetime.date(StartDate[0], StartDate[1], StartDate[2])
  129. EndDate =  datetime.date(EndDate[0], EndDate[1], EndDate[2])
  130. RangeDate = datetime.timedelta(days = 1)
  131. while StartDate <= EndDate:
  132.     yy,mm,dd=str(StartDate).split('-')
  133.     dat=datetime.datetime(int(yy), int(mm), int(dd))
  134.     dd=dat.strftime('%Y/%m/%d')
  135.     year=str(int(dd[0:4])-1911)
  136.     date=dd.replace(dd[0:4], year)
  137.     print('目前執行網路擷取',date ,'日的資料，請稍後...')
  138.     re=read_network_data(date,1)
  139.     if re[0][0]=='查無資料':
  140.         print(date,'日，可能為休市日，查無資料。')
  141.         StartDate = StartDate + RangeDate
  142.         continue
  143.     print('檢查是否有新股票加入，請稍後...')
  144.     check_db_exists(regs=re)
  145.     print('將資料寫入資料庫中，請稍後...')
  146.     add_data(regs=re,date=dd)
  147.     StartDate = StartDate + RangeDate
  148.     te = time.time()
  149.     print('截至目前，時間已耗費：', int(te - ts),'秒')

  151. print('執行完畢！')
複製代碼
作者: lpk187    時間: 2016-9-29 13:00

[attach]25390[/attach]
作者: c_c_lai    時間: 2016-9-29 14:31

回復 34# lpk187
我將 pandas 的處理稍加修改執行後，
產生錯誤訊息，是哪裡不對？
它一直說 OperationalError: near "0050": syntax error
[attach]25391[/attach]
作者: c_c_lai    時間: 2016-9-29 16:12

回復 33# lpk187
將它存成 .db 以及 .sqlite 兩種不同 Extension Name，
結果如下： ( .sqlite  速度較快，檔案亦較小)
[attach]25394[/attach]
作者: c_c_lai    時間: 2016-9-29 17:33

回復 33# lpk187
[attach]25397[/attach]
作者: lpk187    時間: 2016-9-29 18:41

回復 37# c_c_lai


   這應該不會影響查詢吧！
作者: koshi0413    時間: 2016-9-29 18:51

回復 37# c_c_lai

c大是不是手動把資料記錄清除過，最前面那行不可逆的，像 l大說的  不影響查詢
作者: c_c_lai    時間: 2016-9-29 18:56

回復 38# lpk187
[attach]25399[/attach]
作者: koshi0413    時間: 2016-9-29 19:26

本帖最後由 koshi0413 於 2016-9-29 19:31 編輯

回復 40# c_c_lai

對咧   泥的主鍵呢??????????????
怪了，小弟也沒這情況也@@
不過通常小弟只建立索引   沒建立主鍵    這方面可以請教 j大

等等，剛才看了一下自己的， 也一樣，c大  直接去點各股表格看看
作者: lpk187    時間: 2016-9-29 21:18

回復 40# c_c_lai

不好意思，不是很明白你的意思是什麼？可以舉列說明嗎？因為我沒在操作設票，所以對這，不明白
作者: c_c_lai    時間: 2016-9-30 07:41

本帖最後由 c_c_lai 於 2016-9-30 07:43 編輯

回復 42# lpk187
不好意思，我是說程式中如何去建立索引檔 (Primary Key, Secondary Key 等)，
在下一次執行時，便能判斷是否為重複 (Duplicate) Key 值，而去執行 Update 動作，
反之執行 Insert 的 動作。如此便能正常地去維護與了解資料庫的運用了。
Python 正還在學習中，諸多提問還請多多指教！
作者: lpk187    時間: 2016-9-30 09:40

回復 43# c_c_lai

在原來程序中，建立資料表時，就都有建立主鍵 (Primary Key)，所以是不會有重複值的，
sql = "CREATE TABLE 股票(股票代號  char(10) PRIMARY KEY,....
sql="CREATE TABLE [" + i + "](日期  DATETIME PRIMARY KEY, \
若有必要建立外來鍵(Secondary Key)，可以改成
sql="CREATE TABLE [" + i + "] (日期 DATETIME PRIMARY KEY  REFERENCES [0050] (日期), \
另外SQLite3就有建立索引

[attach]25405[/attach]
作者: lpk187    時間: 2016-9-30 09:58

本帖最後由 lpk187 於 2016-9-30 10:03 編輯

回復 35# c_c_lai

在sqlite3中 用 sql語法，有些許不同，就是資料表要用 [ ] 中括號
  1. import sqlite3 as lit
  2. import pandas as pd

  4. conn = sqlite3.connect('twsedata.sqlite')
  5. data=pd.read_sql('select * from [0050]',conn)
  6. conn.close()
  7. data
複製代碼
作者: c_c_lai    時間: 2016-9-30 12:34

回復 45# lpk187
沒錯！ 須加上 [ ... ] 才行。
如此 pandas 的匯入也解決了。
謝謝囉！
[attach]25406[/attach]
作者: c_c_lai    時間: 2016-9-30 12:36

回復 44# lpk187
這部分我再試試看，謝謝費心！
作者: koshi0413    時間: 2016-9-30 14:43

回復  lpk187
沒錯！ 須加上 [ ... ] 才行。
如此 pandas 的匯入也解決了。
謝謝囉！
c_c_lai 發表於 2016-9-30 12:34

c大
可否請教一下，在python取出資料sql  是要在 python直接畫圖嘛？速度較快？還是單純練習呢？
因為看過泥的文章是用vba畫

ps:目前小弟的想法是   python抓取資料至sqlite3，vba從sqlite3取出資料
單純交流一下想法
作者: c_c_lai    時間: 2016-9-30 15:04

回復 44# lpk187
經比對後始發現不無重複鍵值；而是第一次執行時
部位依次完全寫入，再第二次執行時發現從 Rec#104後
又有新的紀錄寫入，才覺得訝異。
經大大再次分析解說，再次資料比對才發現其為新值。

現在的問題是： 第一次初始資料寫入時違和會是不完整？
[attach]25407[/attach]
作者: c_c_lai    時間: 2016-9-30 15:22

c大
可否請教一下，在python取出資料sql  是要在 python直接畫圖嘛？速度較快？還是單純練習呢？
因為看 ...
koshi0413 發表於 2016-9-30 14:43

其實你提到的 ：
"目前的想法是   python 抓取資料至 sqlite3，vba 從 sqlite3 取出資料"
在實務上這種方式，只能當作是 『儲備作業』，提供作為 「歷史資料查詢」；
『線上即時作業』是 Excel 直接與券商 DDE 連結攫取資訊，直接進行繪圖分析；
除非 Python 也提供類似如此之功能 (連結券商DDE)；接下來透過
Candlestick OHLC graphs with Matplotlib 來處理。
作者: koshi0413    時間: 2016-9-30 15:35

其實你提到的 ：
"目前的想法是   python 抓取資料至 sqlite3，vba 從 sqlite3 取出資料"
在實務上這種 ...
c_c_lai 發表於 2016-9-30 15:22


原來如此，小弟是看長期月線的，所以是要統計歷史資料沒有錯
線上即時作業 小弟沒辨法，原來是操作手法的不同

故，泥用python取出sql內的表格，只是單純要畫圖？
印像中   台灣的 線上即時作業 都是用 excel （國外聽說改掉了）
用vba才可以即時互動   python應該還不行~~不過也不一定，說不定多google  就有高手作出來了呢

ps:咳咳~~大家上班不上班的在聊這個，哈哈哈
作者: lpk187    時間: 2016-9-30 15:47

回復 49# c_c_lai


   你執行的情況，真的很奇怪！我執行了3次從2016/1/1到2016/9/29 也都沒有得到像你一樣的結果
你再把資料庫刪掉，觀察第一次是不是會不完整，從2016/1/1到2016/9/29共有170筆資料，看是不是會不夠
我程式裡面，只有插入INSERT INTO...並沒有修改的語句而且當有重覆值時，並不會執行任何動作
except Exception as e:
            pass
再麻煩你了
作者: lpk187    時間: 2016-9-30 15:52

由於 sqlite3 在日期時間 是以文字串存入，而非以日期時間格式存入(如：#2016/01/06#)
而sqlite3的date函數的日期格式必須像'2016-01-06'年月日需以'-'隔開 ，若以'2016/01/06' 斜線 '/'隔開，以SQL語法會無法計算
所以在存入sqlite3資料庫的日期，必須先處理

在 dd=dat.strftime('%Y/%m/%d')的下行需加入另一句
sqldate=dat.strftime('%Y-%m-%d')

而這句add_data(regs=re,date=dd)必須修改為
add_data(regs=re,date=sqldate)

在以後的日期處理，就會容易處理了

sqlite3在日期查詢方面，例如說要查詢 '2016-01-01' 到 '2016-01-31'
在SQL語法方面 原來的用法 WHERE 日期 BETWEEN '2016-01-01' AND '2016-01-31'
而sqlite3 卻不能以 BETWEEN 去處理，就像前面說的，日期時間 是以文字串存入，文字串不能用在日期時間處理

所以sqlite3的SQL語法為
"select * from [0050] where julianday(日期)>=julianday('2016-01-01') and julianday(日期)<=julianday('2016-01-31')"
作者: c_c_lai    時間: 2016-9-30 17:00

回復 52# lpk187
我的是 179 Records。
[attach]25409[/attach]
作者: fx790320    時間: 2016-10-8 20:11

太棒了!  謝謝大大的分享~~
作者: xyz66217    時間: 2018-12-27 14:09

請問各位大大，最近不想從證交所抓資料，改向鉅亨網，不過在網頁解析上沒經驗(之前是請教別人後，再依樣劃葫蘆慢慢改出自已想要的)
原程式部份檔如附件，

  若改由鉅亨網下載資料的話，從網路上看文章，其中改成以下，不過好像沒辦法送出查詢，想請教是哪裡出問題??
          = "    cnyes.com/twstock/ps_historyprice/" + (cell21)+".htm"
    classSelectTextStart = (str(yy-1)+"/"+str(mm)+"/"+str(dd))
    classSelectTextEND = (str(yy)+"/"+str(mm)+"/"+str(dd))
  
    browser.find_element_by_id("ctl00_ContentPlaceHolder1_startText").send_keys(classSelectTextStart)
    browser .find_element_by_id("ctl00_ContentPlaceHolder1_endText").send_keys(classSelectTextEND)
    browser.find_element_by_id("ctl00_ContentPlaceHolder1_submitBut").send_keys(Keys.ENTER)
    time.sleep(10)
作者: abner021    時間: 2019-11-10 13:26

小弟是程式新手，所以什麼都不懂，會亂發問，請見諒嘿~~



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)