介紹csv, sqlite3, datetime 3個模組,
內建的模組,免安裝
import csv
with open('D:\\Revenue\\sii_201201.csv', 'rb') as csvfile:
rvnreader = csv.reader(csvfile, delimiter=',', quotechar='"')
for row in rvnreader:
print (', '.join(row))
讀入csv檔,delimiter是分隔符號(欄與欄的分隔),quotechar是把欄位內容包起來的符號。
import sqlite3
conn = sqlite3.connect('revenue.db')
c = conn.cursor()
c.execute('''CREATE TABLE rvn
(date text, cls text, cpy text,cpyname text, monr real,
lmonr real,lyrmonr real,lpm real,lyrpm real,sumr real,lsumr real,llpm real)''')
conn.commit()
望文生義的使用方式,新建table。
合併兩個動作,讀csv,寫sqlite3
with open('D:\\Revenue\\sii_201201.csv', 'rb') as csvfile:
rvnreader = csv.reader(csvfile, delimiter=',', quotechar='"')
for row in rvnreader:
rvnlst=('201201',row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],
row[9],row[10])
c.execute('INSERT INTO rvn VALUES (?,?,?,?,?, ?,?,?,?,?, ?,?)', rvnlst)
conn.commit()
以下是datetime模組的練習
bday=datetime.date(1972, 7, 10)
bday.replace(day=20)
Out[41]: datetime.date(1972, 7, 20)
bday.replace(year=2000)
Out[42]: datetime.date(2000, 7, 10)
bday.replace(year=200)
Out[43]: datetime.date(200, 7, 10)
bday.replace(year=-1)
---------------------------------------------------------------------------
ValueError Traceback (most recent call last)
<ipython-input-44-b43cd49ee1bc> in <module>()
----> 1 bday.replace(year=-1)
ValueError: year is out of range
tday=datetime.date.today()
tday.weekday()
Out[46]: 1
tday.isoweekday()
Out[47]: 2
tday.isocalendar()
Out[48]: (2013, 38, 2)
half_year = timedelta(days=183)
d=d+half_year
d
Out[57]: datetime.date(2002, 9, 11)
t=d.timetuple()
ic=d.isocalendar()
for i in ic:
print(i)
2002
37
3
d.isoformat()
Out[66]: '2002-09-11'
d1=datetime.date(1988,4,5)
d1
Out[68]: datetime.date(1988, 4, 5)
i=2010,j=8,k=6
File "<ipython-input-69-3adf8be519e6>", line 1
SyntaxError: can't assign to literal
i=2010;j=8;k=6
d2=datetime.date(i,j,k)
d2
Out[71]: datetime.date(2010, 8, 6)
d.strftime("%d/%m/%y")
Out[75]: '11/09/02'
d.strftime("%d-%m-%y")
Out[76]: '11-09-02'
d.strftime("%d.%m.%y")
Out[77]: '11.09.02'
----------------------------------------
晚上測試的補充:
#!/usr/bin/python
# -*- coding: utf-8 -*-
import csv
import sqlite3
conn = sqlite3.connect('revenue.db')
conn.text_factory = str
c = conn.cursor()
with open('/home/timloo/Revenue/sii_201201.csv', 'rb') as csvfile:
rvnreader = csv.reader(csvfile, delimiter=',', quotechar='"')
for row in rvnreader:
rvnlst=('201201',row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],
row[9],row[10])
c.execute('INSERT INTO rvn VALUES (?,?,?,?,?, ?,?,?,?,?, ?,?)', rvnlst)
conn.commit()
沒有conn.text_factory = str這一行時,
會報error.
sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.
產生下一個問題,
c.execute('SELECT * FROM rvn')
print c.fetchone()
寫入成功,卻秀出
(u'201201', u'\ufeff"\u7522\u696d\u5225"', u'\u516c\u53f8\u4ee3\u865f', u'\u516c\u53f8\u540d\u7a31', u'\u7576\u6708\u71df\u6536', u'\u4e0a\u6708\u71df\u6536', u'\u53bb\u5e74\u7576\u6708\u71df\u6536', u'\u4e0a\u6708\u6bd4\u8f03\u589e\u6e1b(%)', u'\u53bb\u5e74\u540c\u6708\u589e\u6e1b(%)', u'\u7576\u6708\u7d2f\u8a08\u71df\u6536', u'\u53bb\u5e74\u7d2f\u8a08\u71df\u6536', u'\u524d\u671f\u6bd4\u8f03\u589e\u6e1b(%)')
不是給我們看的中文。
做一個encode的動作,
c.execute('SELECT * FROM rvn')
row = c.fetchone()
print row[1].encode('utf8')
就可以秀中文,
那中文欄位都要一直逐欄位encode個沒完,
奇妙的現象產生了,
c.execute("SELECT * FROM rvn where cpy='3149'")
row = c.fetchone()
print row[0],row[1],row[2],row[3],row[4],row[5]
201201 光電業 3149 正達國際 626592.0 642657.0
不用encode也會秀中文。
高興之餘,試一下中文查詢
c.execute(u"SELECT * FROM rvn where cpyname like '正達%'")
#print c.fetchone()
row = c.fetchone()
print row[0],row[1],row[2],row[3],row[4],row[5]
結果是201201 光電業 3149 正達國際 626592.0 642657.0
**結語:**拜開放之賜,其實python雖然中文支援上要轉個彎,但其實還好,http://stackoverflow.com/相對於微軟上的MSDN.
# -*- coding: utf-8 -*-沒法在IPython上跑,網上有不少解法,
但是,可以把# -*- coding: utf-8 -*-寫入檔案裏,
然後!python select.py就可在IPython裏執行,驚嘆號(!)算是IPython的眾多魔術技法之一。