- ©«¤l
- 552
- ¥DÃD
- 3
- ºëµØ
- 0
- ¿n¤À
- 578
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-2-8
- ³Ì«áµn¿ý
- 2024-7-9
|
python ¿Ä¸ê¿Ä¨é·JÁ` (¥þ³¡)"¸ê®Æ
ÁÙ¤£·|»s§@°õ¦æÀÉ¡I¨ä¤º®e¶È¨Ñ°Ñ¦Ò¤§- #-*- coding:utf-8 -*-
- '''
- ¥»µ{§Ç¥Ñ Python 3.52 ¼g¦¨¡A¬°§@ªÌ°Ñ¦Ò³Â»¶®a±Ú°Q½×°Ï "python¤W¥«Âd¤T¤jªk¤H¶R½æ¶W¤é³ø¸ê®Æ¤U¸ü" °Q½×¤º®e½m²ß
- ¨ä¤º®e¤j³¡¥÷°Ñ¦Ò zyzzyva¤j¤j¡Bc_c_lai ¤j¤j ¡Bkoshi0413 ¤§°Q½×¤º®e¡A·PÁ¡I
- ¥»µ{§Ç·|¦Û°Ê¥Í¦¨ Access ªº twsedata.mdb¸ê®Æ®w¡A¤Î«Ø¥ß¦U©Ò»Ý¤§¸ê®Æªí¡A¦ý¥u¯à¤U¸ü"¿Ä¸ê¿Ä¨é·JÁ` (¥þ³¡)"¸ê®Æ
- £°¡I¨S¦³«H¥Î¥æ©ö²Îp¸ê®Æ ¡A¥u«Ø¥ß¨ä¸ê®Æªí
- ¥»µ{§Ç»Ýn pip ¦w¸Ë requests ¡BBeautifulSoup4¡Bpypyodbc
- ¥H¤Î»Ý¤U¸ü pywin32 : https://sourceforge.net/projects/pywin32/files/pywin32/Build%20220/
- ¨Ï¥Î¤èªk¡G¥u¦b input ®É ¡A¿é¤J¶}©lÂ^¨ú¤é´Á¤Îµ²§ôÂ^¨ú¤é´Á§Y¥i¡A²Ä¤@¦¸°õ¦æ®É¡A·|«Øºc¸ê®Æ®w¡A¦Ó«á¥u¼W¥[¸ê®Æ¡A
- ¨C¤é¸ê®ÆÂ^¨ú¨ìÀx¦s¦Ü¸ê®Æ®wªº®É¶¡¬ù 21¬í(¨Ì§@ªÌ¹q¸£°õ¦æp®É )
- ¥»µ{§Ç¶È¨Ñ°Ñ¦Ò¡A¤£t³d§ó·s¤º®e¡A¤£t³d°£¿ù(¦]¬°§Ú¤]¬O·s¤â¡A°£¿ù¹ï§Ú¨Ó»¡¤Ó#%%$...)¡A·dÃa¨Ï¥ÎªÌ¹q¸£¡A¤]¤£Ãö§@ªÌªº¨Æ¡I¡I
- §@ªÌ¡G³Â»¶®a±Ú°Q½×°Ï lpk187
- §¹¦¨¤é´Á¡G2016/9/22
- '''
- import requests
- from bs4 import BeautifulSoup
- import random
- import os
- import win32com.client
- import pypyodbc
- import datetime
- import time
- # ÀH¾÷§ó§ï headers
- def head_random():
- for i in range(10):
- hs = ['Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36',
- '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',
- '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',
- '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'
- 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0)']
- hes = {"User-Agent":random.choice(hs)}
- return hes
- # ¶°¥xÃҩҿĸê¿Ä¨é¾lÃB¥þ³¡¸ê®Æ
- def read_network_data(date,from_number):
- url = 'http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php'
- head =head_random()
- payload = {"download":'',
- "qdate":date,
- "selectType":"ALL"}
- res = requests.post(url, headers=head, data=payload)
- soup=BeautifulSoup(res.text, "lxml")
- tab = soup.select('tbody')[from_number] # 2Óªí®æ¤§2
- regs=[] # ªì©l°}¦C
-
- for i in range(len(tab.select('tr')[0].select('td'))): # ©w¸q2ºû°}¦Cºû«×
- regs.append([])
- for tr in tab.select('tr'): #Â^¨úªí®æ¸ê®Æ
- for i in range(len(tab.select('tr')[0].select('td'))):
- regs[i].append(tr.select('td')[i].text)
- return regs
- def conn_db():
- Conn = win32com.client.Dispatch("ADODB.Connection")
- strMDB ='Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + os.getcwd() + '\\twsedata.mdb;'
- Conn.ConnectionString = strMDB
- Conn.Open()
- return Conn
- def check_db_exists(regs):
- if not os.path.exists('twsedata.mdb'):
- pypyodbc.win_create_mdb( os.getcwd() + '\\twsedata.mdb')
- Conn=conn_db()
- sql = "CREATE TABLE ªÑ²¼(ªÑ²¼¥N¸¹ char(10) PRIMARY KEY,ªÑ²¼¦WºÙ char(20));"
- Conn.Execute(sql)
- sql = "CREATE TABLE ¿Ä¸ê¿Ä¨é«H¥Î¥æ©ö²Îp(¤é´Á datetime,¶µ¥Ø char(20),¶R¶i INTEGER,¶R¥X INTEGER,²{ª÷¡]¨é¡^ÀvÁÙ INTEGER,«e¤é¾lÃB INTEGER,¤µ¤é¾lÃB INTEGER);"
- Conn.Execute(sql)
- n = 0
- Conn=conn_db()
- for i in regs[0]:
- try:
- Conn.Execute('select * from ' + i); # table does not exist
- except Exception as e:
- sql="CREATE TABLE " + i + "(¤é´Á datetime,"
- sql= sql + "¸ê¶R¶i INTEGER,"
- sql= sql + "¸ê½æ¥X INTEGER,"
- sql= sql + "¸ê²{ª÷ÀvÁÙ INTEGER,"
- sql= sql + "¸ê«e¤é¾lÃB INTEGER,"
- sql= sql + "¸ê¤µ¤é¾lÃB INTEGER,"
- sql= sql + "¸êÃB INTEGER,"
- sql= sql + "¨é¶R¶i INTEGER,"
- sql= sql + "¨é½æ¥X INTEGER,"
- sql= sql + "¨é²{ª÷ÀvÁÙ INTEGER,"
- sql= sql + "¨é«e¤é¾lÃB INTEGER,"
- sql= sql + "¨é¤µ¤é¾lÃB INTEGER,"
- sql= sql + "¨éÃB INTEGER,"
- sql= sql + "¸ê¨é¤¬©è INTEGER,"
- sql= sql + "µù°O char(5));"
- Conn.Execute(sql)
- rs = win32com.client.Dispatch('ADODB.RecordSet')
- rs.ActiveConnection = Conn
- rs.Open('select * from ªÑ²¼', Conn, 3, 3)
- rs.AddNew()
- rs.Fields.Item(0).Value = regs[0][n]
- rs.Fields.Item(1).Value = regs[1][n]
- rs.Update()
- n += 1
- def add_data(regs,date):
- Conn=conn_db()
- for i in range(len(regs[0])):
- rs = win32com.client.Dispatch('ADODB.RecordSet')
- rs.ActiveConnection = Conn
- sql='select * from ' + regs[0][i]
- rs.Open(sql, Conn, 3, 3)
- for j in range(2,len(regs)):
- if j==2:
- rs1 = win32com.client.Dispatch('ADODB.RecordSet')
- rs1.ActiveConnection = Conn
- sql="SELECT * FROM " + regs[0][i] + " WHERE ¤é´Á = #" + date + "#;"
- rs1.Open(sql,Conn,3,3)
- if rs1.EOF:
- rs.AddNew()
- rs.Fields.Item(0).Value = date
- rs.Fields.Item(j-1).Value = regs[j][i]
- rs1.Close()
- else:
- rs1.Close()
- break
- rs.Fields.Item(j-1).Value = regs[j][i]
- rs.Update()
- # ¥H¤U¬°µ{¦¡ªº±Ò©lÂI¡G
- StartDate=input('½Ð¿é¤J¶}©lÂ^¨ú¤é´Á(¦è¤¸¤é´Á¦p¡G2016/1/1)¡G')
- EndDate=input('½Ð¿é¤Jµ²§ôÂ^¨ú¤é´Á(¦è¤¸¤é´Á¦p¡G2016/1/31)¡G')
- #³B²z¤é´ÁÁÙ¯uªº«Ü³Â·Ð
- ts=time.time()
- StartDate = time.strptime(StartDate, "%Y/%m/%d")
- EndDate =time.strptime(EndDate, "%Y/%m/%d")
- StartDate = datetime.date(StartDate[0], StartDate[1], StartDate[2])
- EndDate = datetime.date(EndDate[0], EndDate[1], EndDate[2])
- RangeDate = datetime.timedelta(days = 1)
- while StartDate <= EndDate:
- yy,mm,dd=str(StartDate).split('-')
- dat=datetime.datetime(int(yy), int(mm), int(dd))
- dd=dat.strftime('%Y/%m/%d')
- year=str(int(dd[0:4])-1911)
- date=dd.replace(dd[0:4], year)
- print('¥Ø«e°õ¦æºô¸ôÂ^¨ú',date ,'¤éªº¸ê®Æ¡A½Ðµy«á...')
- re=read_network_data(date,1)
- if re[0][0]=='¬dµL¸ê®Æ':
- print(date,'¤é¡A¥i¯à¬°¥ð¥«¤é¡A¬dµL¸ê®Æ¡C')
- StartDate = StartDate + RangeDate
- continue
- print('Àˬd¬O§_¦³·sªÑ²¼¥[¤J¡A½Ðµy«á...')
- check_db_exists(regs=re)
- print('±N¸ê®Æ¼g¤J¸ê®Æ®w¤¤¡A½Ðµy«á...')
- add_data(regs=re,date=dd)
- StartDate = StartDate + RangeDate
- te = time.time()
- print('ºI¦Ü¥Ø«e¡A®É¶¡¤w¯Ó¶O¡G', int(te - ts),'¬í')
- print('°õ¦æ§¹²¦¡I')
½Æ»s¥N½X |
|