项目一 爬取外汇数据

import urllib.request
import re
import sqlite3

class MySpider:
    def openDB(self):
        self.con=sqlite3.connect("rates.db")
        self.cursor=self.con.cursor()
        try:
            self.cursor.execute("drop table rates")
        except:
            pass
        sql="create table rates (Currency varchar(256) primary key,TSP float,CSP float, TBP float, CBP float)"
        self.cursor.execute(sql)

    def closeDB(self):
        self.con.commit()
        self.con.close()

    def insertDB(self,Currency,TSP,CSP,TBP,CBP):
        try:
            sql="insert into rates (Currency,TSP,CSP,TBP,CBP) values (?,?,?,?,?)"
            self.cursor.execute(sql,[Currency,TSP,CSP,TBP,CBP])
        except Exception as err:
            print(err)

    def show(self):
        self.cursor.execute("select Currency,TSP,CSP,TBP,CBP from rates")
        rows=self.cursor.fetchall()
        print("%-18s%-12s%-12s%-12s%-12s" %("Currency","TSP","CSP","TBP","CBP"))
        for row in rows:
            print("%-18s%-12.2f%-12.2f%-12.2f%-12.2f" % (row[0],row[1],row[2],row[3],row[4]))

    def spider(self,url):
        try:
            resp=urllib.request.urlopen(url)
            data=resp.read()
            html=data.decode()
            p=re.search(r"",html)
            q = re.search(r"",html)
            i=0
            while p and q:
                a=p.end()
                b=q.start()
                tds=html[a:b]
                m=re.search(r"",tds)
                n = re.search(r"", tds)
                row=[]
                while m and n:
                    u=m.end()
                    v=n.start()
                    row.append(tds[u:v].strip("\n"))
                    tds=tds[n.end():]
                    m=re.search(r"",tds)
                    n = re.search(r"", tds)
                i=i+1
                if i>=2 and len(row)==6:
                    Currency=row[0]
                    TSP=float(row[2])
                    CSP=float(row[3])
                    TBP=float(row[4])
                    CBP=float(row[5])
                    self.insertDB(Currency,TSP,CSP,TBP,CBP)
                html=html[q.end():]
                p=re.search(r"",html)
                q = re.search(r"",html)
        except Exception as err:
            print(err)

    def process(self):
        self.openDB()
        #self.spider("http://127.0.0.1:5000")
        spider.spider("http://www.rj-jwb.com.cn/spiderbook/chapter1/")
        self.show()
        self.closeDB()
#主程序
spider=MySpider()
spider.process()