python3抓取杭州房价信息

因为马上要去杭州，杭州房价去年涨的太厉害了，现在政策比较多看不清杭州房价的形式，所以想写个爬虫将杭州房产的交易信息做个记录。

准备阶段

自己的服务器用的CentOS，上面装了python3，因为要连接数据库，需要安装psycopg2，于是

python3 -m pip install psycopg2

设计好数据库

----------------------------------------
create table for new house transaction each day
----------------------------------------
CREATE TABLE hangzhou.trans_daily_info (
    trans_date DATE NOT NULL,
    downtown_new_trans SMALLINT NOT NULL,
    downtown_new_vol INTEGER NOT NULL,
    xiaoshan_new_trans SMALLINT NOT NULL,
    xiaoshan_new_vol INTEGER NOT NULL,
    yuhang_new_trans SMALLINT NOT NULL,
    yuhang_new_vol INTEGER NOT NULL,
    fuyang_new_trans SMALLINT NOT NULL,
    fuyang_new_vol INTEGER NOT NULL,
    djd_new_trans SMALLINT NOT NULL,
    djd_new_vol INTEGER NOT NULL,
    urban_new_daily_trans SMALLINT NOT NULL,
    urban_new_daily_vol INTEGER NOT NULL,
    other4county_new_qty SMALLINT NOT NULL,
    other4country_new_vol INTEGER NOT NULL,
    downtown_old_qty SMALLINT NOT NULL
    PRIMARY KEY (trans_date)
);

----------------------------------------
create table for weekly hot residence area
----------------------------------------
create table hangzhou.old_weekly_hot_residence(
    id SERIAL primary key , 
    start_time DATE NOT NULL,
    end_time DATE NOT NULL,
    residence_name VARCHAR(50) NOT NULL
);

----------------------------------------
create table for weekly hotest residence
----------------------------------------
CREATE TABLE hangzhou.old_weekly_hotest_residence (
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    week SMALLINT NOT NULL,
    residence_name VARCHAR(50) NOT NULL,
    comment TEXT NOT NULL,
    PRIMARY KEY (start_date,end_date)
);
----------------------------------------
create table for second hand residence transaction info
----------------------------------------
CREATE TABLE hangzhou.old_trans_weekly_info (
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    week SMALLINT NOT NULL,
    city_commercial_house_qty INTEGER NOT NULL,
    city_residence_qty INTEGER NOT NULL,
    urban_commerical_house_qty INTEGER NOT NULL,
    urban_residence_qty INTEGER NOT NULL,
    shangcheng_qty INTEGER DEFAULT 0 ,
    xiacheng_qty INTEGER  DEFAULT 0,
    jianggan_qty INTEGER DEFAULT 0,
    gongshu_qty INTEGER DEFAULT 0,
    xihu_qty INTEGER DEFAULT 0,
    bingjiang_qty INTEGER DEFAULT 0,
    zhijiang_qty INTEGER DEFAULT 0,
    xiasha INTEGER DEFAULT 0,
    PRIMARY KEY (start_date,end_date)
);

后来发现crontab中的命令不执行，check /var/log/cron中发现也没有更新，于是check crond 发现问题，重启

service crond status

当执行的时候发现week字段多余于是删除

 alter table hangzhou.old_trans_weekly_info drop week;
 alter table hangzhou.old_weekly_hotest_residence drop week;

之后发现需要添加comment2 字段，于是

alter table hangzhou.old_weekly_hotest_residence ADD comment2 TEXT ;
alter table hangzhou.old_weekly_hotest_residence ALTER comment2 SET NOT NULL;

未完待续