Cacher is the code snippet organizer for pro developers

#-*-coding=utf-8-*-
__author__ = 'rocky'
#获取每天深圳一手房，二手房的成交套数与面积，并且写入数据库
#主要就是正则表达抓取几个数字
import urllib2,re
import database
def getContent():
    url="http://ris.szpl.gov.cn/"
    one_hand="credit/showcjgs/ysfcjgs.aspx"
    second_hand="credit/showcjgs/esfcjgs.aspx"
    req=urllib2.Request(url+one_hand)
    content=urllib2.urlopen(req).read()
    #返回的就是网页的源码，没有做任何防爬虫的处理，zf网站，呵呵
    #print content
    date=re.compile(r'<SPAN class=titleblue><span id=\"lblCurTime5\">(.*)</span>')
    reg=re.compile(r'<td width="14%"><b>(\d+)</b>')
    result=reg.findall(content)
    current_date=date.findall(content)

    reg2=re.compile(r'<td align="right"><b>(.*?)</b>')
    yishou_area=reg2.findall(content)


    print current_date[0]
    print '一手商品房成交套数：%s'  % result[0]
    print '一手商品房成交面积： %s'  % yishou_area[0]


    sec_req=urllib2.Request(url+second_hand)
    sec_content=urllib2.urlopen(sec_req).read()

    sec_quantity=re.compile(r'<td width="30%">(\d+)</td>')
    sec_result=sec_quantity.findall(sec_content)
    second_area=re.findall(r'<td align="right">(.*?)</td>',sec_content)

    print '二手商品房成交套数：%s'  % sec_result[1]
    print '二手商品房成交面积： %s'  % second_area[2]
    database.create_table()
    database.insert(current_date[0],result[0],yishou_area[0],sec_result[1],second_area[2])

getContent()

# -*-coding=utf-8-*-
__author__ = 'Rocky'
import sqlite3

def create_table():
    conn = sqlite3.connect('shenzhen_house.db')
    try:
        create_tb_cmd='''
        CREATE TABLE IF NOT EXISTS HOUSE
        ('日期' TEXT,
        '一手房套数' TEXT,
        '一手房面积' TEXT,
        '二手房套数' TEXT,
        '二手房面积' TEXT);
        '''
        #主要就是上面的语句
        conn.execute(create_tb_cmd)
    except:
        print "Create table failed"
        return False


    conn.execute(create_tb_cmd)
    conn.commit()
    conn.close()

def insert(date,one_hand,one_area,second_hand,second_area):
    conn = sqlite3.connect('shenzhen_house.db')
    print "open database passed"

    cmd="INSERT INTO HOUSE ('日期','一手房套数','一手房面积','二手房套数','二手房面积') VALUES('%s','%s','%s','%s','%s');" %(date,one_hand,one_area,second_hand,second_area)
    #works 要么加\"
    #paul_su="INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES(5,'%s',32,'CALIFORNIA',2000.00);" %temp2
    #works 要么加 ’‘

    #allen="INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES(2,'ALLEN',72,'CALIFORNIA',20500.00);"
    #teddy="INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES(3,'TEDDY',732,'CALIFORNIA',52000.00);"
    #mark="INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES(4,'MARK',327,'CALIFORNIA',3000.00);"
    #sun="INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES(?,?,?,?,?);"
    #conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES(?,?,32,'CALIFORNIA',2000.00)",temp)

    conn.execute(cmd)

    conn.commit()
    conn.close()