python 融資融券彙總 (全部)"資料

帖子: 552
主題: 3
精華: 0
積分: 578
點名: 0
作業系統: win7
軟體版本: office 2010
閱讀權限: 50
性別: 男
註冊時間: 2015-2-8
最後登錄: 2024-2-26

2016猴年全勤獎章 2015羊年全勤獎章熱烈參與勳章

12^#

發表於 2016-9-29 09:32 | 顯示全部帖子

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

回復 30# c_c_lai

剛測試了一下，在Jupyther Notebook 上執行。
執行後沒錯誤產生，但卻是沒有資料存進去，
但一般執行卻沒有問題。
你試試一般執行，看結果如何

融資融券彙總SQLite3.rar (3.07 KB)

帖子: 552
主題: 3
精華: 0
積分: 578
點名: 0
作業系統: win7
軟體版本: office 2010
閱讀權限: 50
性別: 男
註冊時間: 2015-2-8
最後登錄: 2024-2-26

2016猴年全勤獎章 2015羊年全勤獎章熱烈參與勳章

13^#

發表於 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中打上檢詢錯誤的日期，雖然找不到，但也沒有產生錯誤，
所以我改了程式碼，你試看看，請複製以下代碼
其中也修正擷取網路資料的迴圈，現在擷取變很快
#-*- coding:utf-8 -*-

import requests

from bs4 import BeautifulSoup

import random

import os

import sqlite3

import datetime

import time

# 隨機更改 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

# 集台證所融資融券餘額全部資料

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=[] # 初始陣列



le=len(tab.select('tr')[0].select('td'))

for i in range(le): # 定義2維陣列維度

      regs.append([])

for tr in tab.select('tr'): #擷取表格資料

      for i in range(le):

         if i==0 or i==1 or i==15:    #把文字串中的空白文字去除掉

            strs=tr.select('td')[i].text

            strs=str(strs.replace(' ',''))

            regs[i].append(strs)

         else: # 把數字中的千分符號 "," 去除掉

            ints=tr.select('td')[i].text

            ints=str(ints.replace(',',''))

            regs[i].append(ints)

return regs

def check_db_exists(regs):

if not os.path.exists('twsedata.db'): # 檢查twsedata.db是否存在(和此程式碼相同的資料夾)

      conn = sqlite3.connect('twsedata.db')  # 如果沒有，就建立一個空白資料庫，(sqlite3.connect) 也是連結語句，有資料庫就連結，沒有就建立

      sql = "CREATE TABLE 股票(股票代號  char(10) PRIMARY KEY,股票名稱  char(20));"

      conn.execute(sql) #在新資料庫建立空白資料表 (股票)

      sql = "CREATE TABLE  融資融券信用交易統計( \

                              日期  datetime, \

                              項目  char(20), \

                              買進 INTEGER, \

                              買出 INTEGER, \

                              現金（券）償還 INTEGER, \

                              前日餘額 INTEGER, \

                              今日餘額 INTEGER);"

      conn.execute(sql) #在新資料建立空白資料表(融資融券信用交易統計)

      conn.commit() # 儲存

      conn.close() #關閉



n=0

conn = sqlite3.connect('twsedata.db') # conn和上一個 if ..中的 conn是不一樣的，上一個是沒有資料庫時才作用，為局部變數而此處，則為以下使用的全局變數

for i in regs[0]:  #以爬回來的資料陣列的第一列為迴圈

      try:    #先嘗試查詢 i 變數的資料表是否存在，若是不存在會發出 Exception 的錯誤訊息

         c = conn.cursor()

         sql = "select * from [" + i + "]"

         c.execute(sql) # table does not exist

         c.close()

      except Exception as e:  #當try 發出錯誤訊息(也就是讀不到  i 資料表)，此時建立一個 i 資料表

         c = conn.cursor()

         sql="CREATE TABLE [" + i + "](日期  DATETIME PRIMARY KEY, \

                                    資買進  INTEGER, \

                                    資賣出 INTEGER, \

                                    資現金償還 INTEGER, \

                                    資前日餘額 INTEGER, \

                                    資今日餘額 INTEGER, \

                                    資限額 INTEGER, \

                                    券買進  INTEGER, \

                                    券賣出 INTEGER, \

                                    券現金償還 INTEGER, \

                                    券前日餘額 INTEGER, \

                                    券今日餘額 INTEGER, \

                                    券限額 INTEGER, \

                                    資券互抵 INTEGER, \

                                    註記 char(5));"

         c.execute(sql) #建立 i 資料表

         sql="INSERT INTO 股票(股票代號, 股票名稱) \

            VALUES ('" + regs[0][n] + "','" + regs[1][n] + "');"

         c.execute(sql)  # 在股票資料表中寫入股票代號、股票名稱

         c.close() # 關閉 c 物件

      n += 1

conn.commit()

conn.close()

def add_data(regs,date):

conn = sqlite3.connect('twsedata.db')

for i in range(len(regs[0])):

      try: #檢查某日期是否存在於資料表中，當某日期存在時，跳離這次的循環，這裡指的僅是這次，不是跳離 for

         c = conn.cursor()

         sql="INSERT INTO [" + regs[0][i] + "](日期,資買進,資賣出,資現金償還,資前日餘額, \

                                             資今日餘額,資限額,券買進,券賣出,券現金償還, \

                                             券前日餘額,券今日餘額,券限額,資券互抵,註記) \

                                    VALUES('" + date + "'," + regs[2][i] + "," + regs[3][i] + ", \

                                             " + regs[4][i] + ",  " + regs[5][i] + "," + regs[6][i] + ", \

                                             " + regs[7][i] + "," + regs[8][i] + ",  " + regs[9][i] + ", \

                                             " + regs[10][i] + "," + regs[11][i] + "," + regs[12][i] + ", \

                                             " + regs[13][i] + "," + regs[14][i]+ ",'" + regs[15][i] + "') ;"

         c.execute(sql)

      except Exception as e: #當日期不存在時，寫入資料到資料表中

         pass

      else:

         c.close()



conn.commit()

conn.close()

# 以下為程式的啟始點：

StartDate=input('請輸入開始擷取日期(西元日期如：2016/1/1)：')

EndDate=input('請輸入結束擷取日期(西元日期如：2016/1/31)：')

#處理日期還真的很麻煩

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('目前執行網路擷取',date ,'日的資料，請稍後...')

re=read_network_data(date,1)

if re[0][0]=='查無資料':

      print(date,'日，可能為休市日，查無資料。')

      StartDate = StartDate + RangeDate

      continue

print('檢查是否有新股票加入，請稍後...')

check_db_exists(regs=re)

print('將資料寫入資料庫中，請稍後...')

add_data(regs=re,date=dd)

StartDate = StartDate + RangeDate

te = time.time()

print('截至目前，時間已耗費：', int(te - ts),'秒')

print('執行完畢！')
複製代碼

帖子: 552
主題: 3
精華: 0
積分: 578
點名: 0
作業系統: win7
軟體版本: office 2010
閱讀權限: 50
性別: 男
註冊時間: 2015-2-8
最後登錄: 2024-2-26

2016猴年全勤獎章 2015羊年全勤獎章熱烈參與勳章

14^#

發表於 2016-9-29 13:00 | 顯示全部帖子

Untitled6.rar (4.96 KB)

帖子: 552
主題: 3
精華: 0
積分: 578
點名: 0
作業系統: win7
軟體版本: office 2010
閱讀權限: 50
性別: 男
註冊時間: 2015-2-8
最後登錄: 2024-2-26

2016猴年全勤獎章 2015羊年全勤獎章熱烈參與勳章

15^#

發表於 2016-9-29 18:41 | 顯示全部帖子

回復 37# c_c_lai

這應該不會影響查詢吧！

帖子: 552
主題: 3
精華: 0
積分: 578
點名: 0
作業系統: win7
軟體版本: office 2010
閱讀權限: 50
性別: 男
註冊時間: 2015-2-8
最後登錄: 2024-2-26

2016猴年全勤獎章 2015羊年全勤獎章熱烈參與勳章

16^#

發表於 2016-9-29 21:18 | 顯示全部帖子

回復 40# c_c_lai

不好意思，不是很明白你的意思是什麼？可以舉列說明嗎？因為我沒在操作設票，所以對這，不明白

帖子: 552
主題: 3
精華: 0
積分: 578
點名: 0
作業系統: win7
軟體版本: office 2010
閱讀權限: 50
性別: 男
註冊時間: 2015-2-8
最後登錄: 2024-2-26

2016猴年全勤獎章 2015羊年全勤獎章熱烈參與勳章

17^#

發表於 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就有建立索引

帖子: 552
主題: 3
精華: 0
積分: 578
點名: 0
作業系統: win7
軟體版本: office 2010
閱讀權限: 50
性別: 男
註冊時間: 2015-2-8
最後登錄: 2024-2-26

2016猴年全勤獎章 2015羊年全勤獎章熱烈參與勳章

18^#

發表於 2016-9-30 09:58 | 顯示全部帖子

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

回復 35# c_c_lai

在sqlite3中用 sql語法，有些許不同，就是資料表要用 [ ] 中括號

import sqlite3 as lit
import pandas as pd
conn = sqlite3.connect('twsedata.sqlite')
data=pd.read_sql('select * from [0050]',conn)
conn.close()
data

複製代碼

帖子: 552
主題: 3
精華: 0
積分: 578
點名: 0
作業系統: win7
軟體版本: office 2010
閱讀權限: 50
性別: 男
註冊時間: 2015-2-8
最後登錄: 2024-2-26

2016猴年全勤獎章 2015羊年全勤獎章熱烈參與勳章

19^#

發表於 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
再麻煩你了

帖子: 552
主題: 3
精華: 0
積分: 578
點名: 0
作業系統: win7
軟體版本: office 2010
閱讀權限: 50
性別: 男
註冊時間: 2015-2-8
最後登錄: 2024-2-26

2016猴年全勤獎章 2015羊年全勤獎章熱烈參與勳章

20^#

發表於 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')"