昨晚臨時有些突發狀況,沒仔細想清楚一些環節,進度拖慢了一些。
目前的csv檔格式如下:
"102年 6177 達麗 月成交資訊(元,股)"
年度,月份,最高價,最低價,加權(A/B)平均價,成交筆數,成交金額(A),成交股數(B),週轉率(%)
102,8,38.50,32.05,35.54,"4,494","282,038,391","7,935,480",4.84
說明: 1. 本統計資訊含一般、零股、盤後定價、鉅額交易,不含拍賣、標購。
頭兩行,尾一行,不須轉入sqlite3,頭兩行是固定的,容易排除,而尾一行是變動的。 實驗了一下,昨晚沒轉成。
練習一:中文字串的練習
在python3
a="說明"
a=="說明"
output:
True
---------------
a in "說明: 1. 本統計資訊含一般、零股、盤後定價、鉅額交易,不含拍賣、標購。"
output:
True
---------------
b="說不明"
b in "說明: 1. 本統計資訊含一般、零股、盤後定價、鉅額交易,不含拍賣、標購。"
output:
False
在python3和python有很大的改進,不用在字串左邊加上u, 在程式中,中文和英文沒兩樣
練習二:以上面的csv檔為例,抓出尾行
import csv
a="說明"
with open('/home/timloo/stock/month/2013_6177.csv', newline='') as csvfile:
for row in csv.reader(csvfile, delimiter=',', quotechar='"'):
if a in row[0]:
print(row[0])
output:
['說明: 1. 本統計資訊含一般、零股、盤後定價、鉅額交易,不含拍賣、標購。']
--------------------------------
import csv
a="說明"
with open('/home/timloo/stock/month/2013_6177.csv', newline='') as csvfile:
for row in csv.reader(csvfile, delimiter=',', quotechar='"'):
if (a not in row[0]):
print(row)
練習in 和not in 的語法(和sql的同樣關鍵,用法不一樣),昨晚就是卡在這裏,狀況不好的時候,就是會卡在莫名其妙的地方。
練習三:測試空白檔
with open('/home/timloo/stock/month/2013_9946.csv', newline='') as csvfile:
for row in csv.reader(csvfile, delimiter=',', quotechar='"'):
if (a not in row[0]):
print(row)
程式不會丟出異常,導致中斷。
conn = sqlite3.connect('revenue.db')
conn.text_factory = str
c = conn.cursor()
for row in c.execute("SELECT DISTINCT cpy FROM rvn WHERE date='201301'"):
cpy=row[0]
fm='/home/timloo/stock/month/2013_'+cpy+'.csv'
print(fm)
f=open(fm, 'r',newline='')
a="說明"
i=0
for row in csv.reader(f, delimiter=',', quotechar='"'):
i=i+1
if i>2 and (a not in row[0]):
f0=row[0];f1=row[1];f2=row[2].replace(",", "");f3=row[3].replace(",", "");
f4=row[4].replace(",", "");f5=row[5].replace(",", "");f6=row[6].replace(",", "");f7=row[7].replace(",", "");f8=row[8]
prlst=(cpy,f0,f1,f2,f3,f4,f5,f6,f7,f8)
#print(prlst)
c.execute('INSERT INTO m_prc_new VALUES (?,?,?,?,?, ?,?,?,?,? )', prlst)
f.close()
理論上,這樣就應該就會寫入成功,但是只會寫一個csv檔進入sqlite3裏,
查了一個多小時,才發現,c.execute,同時select又insert會讓程式中斷,
這是分段查出來的結果,可見文件沒有讀通。
改一下寫法,先把公司代號select出來放入list。
cpys=[]
for row in c.execute("SELECT DISTINCT cpy FROM rvn WHERE date='201301'"):
cpys.append(row[0])
再批次把csv檔寫入sqlite3
for cpy in cpys:
fm='/home/timloo/stock/month/2013_'+cpy+'.csv'
print(fm)
f=open(fm, 'r',newline='')
a="說明"
i=0
for row in csv.reader(f, delimiter=',', quotechar='"'):
i=i+1
if i>2 and (a not in row[0]):
f0=row[0];f1=row[1];f2=row[2].replace(",", "");f3=row[3].replace(",", "");
f4=row[4].replace(",", "");f5=row[5].replace(",", "");f6=row[6].replace(",", "");f7=row[7].replace(",", "");f8=row[8]
prlst=(cpy,f0,f1,f2,f3,f4,f5,f6,f7,f8)
#print(prlst)
c.execute('INSERT INTO m_prc_new VALUES (?,?,?,?,?, ?,?,?,?,? )', prlst)
f.close()
conn.commit()
測試一下,是否成功寫入?!!
for row in c.execute("SELECT distinct * FROM m_prc_new WHERE cpy in ('3149','1101','2330')"):
print(row)
output:
('1101', '102', '1', 40.0, 37.5, 38.92, 53525.0, 6654123049.0, 170962480.0, 4.63)
('1101', '102', '2', 40.8, 37.2, 38.78, 46188.0, 5486512954.0, 141461811.0, 3.83)
('1101', '102', '3', 39.05, 36.05, 37.46, 50890.0, 5471091482.0, 146040184.0, 3.95)
------------------------------------
('3149', '102', '7', 61.9, 50.5, 57.14, 28386.0, 3268354276.0, 57191335.0, 21.53)
('3149', '102', '8', 60.6, 52.7, 56.63, 23895.0, 2499503198.0, 44131173.0, 16.62)
**小結:**雖然從網站上直接批次另存csv檔,很方便,但是實作和想像還是有一段差距。
實作才能填滿每個細節。