class PageExtractorDB():
__name__ = "PageExtractorDB"
@staticmethod
def sql_creator(page_info):
sql = '''
update
anjuke_list
set
page_info_json = '{page_info_json}'
where
house_id = {house_id}
'''.format(page_info_json=dumps(
page_info["data"]).encode('utf-8').decode('unicode_escape'),
house_id=page_info["house_id"])
return sql
def __init__(self):
self.db = DBController()
def update(self, page_info):
sql = PageExtractorDB.sql_creator(page_info)
try:
self.db.execute(sql)
except Exception as e:
db_err("[%s]更新数据错误[%s] %s %s " %
(self.__name__, page_info['house_id'], str(e), sql))
else:
db_info("[%s]成功更新数据[%s]" % (self.__name__, page_info["house_id"]))
@property
def exit(self):
self.db.close
def _count(self, start=0):
'''库存数量'''
from .sql_template import get_count_sql
DBController.execute(self, get_count_sql)
count = self.cur.fetchone()[0]
return count - start
class PriceStaterDB():
__name__ = "PriceStaterDB"
@staticmethod
def sql_creator(price_trend_json):
sql = '''
update
anjuke_list
set
price_trend_json = '{price_trend_json}'\
where
house_id = {house_id}
'''.format(house_id=price_trend_json["house_id"],
price_trend_json=price_trend_json["data"])
return sql
def __init__(self):
self.db = DBController()
@property
def select_cba_key_iter(self):
'''cba means community/block/area in database struct
注意 本SQL中已经过滤掉可能重复的cba_key
'''
sql = """
select
distinct(concat(community_id, '-', block_id, '-', area))
from
anjuke_list
"""
self.db.execute(sql)
data = self.db.cur.fetchall()
data = [d[0] for d in data]
for cba in data:
yield tuple(cba.split('-'))
def insert(self, json_data):
'''向数据表中插入json_data
(接口返回数据, 接口参数)
'''
sql_data = json_data[-1].split('-')
sql_data.append(json_data[0])
sql = """
insert into
anjuke_price_trend
(`community_id`, `block_id`, `area_id`, `price_trend_json`)
value
(%s, %s, %s, '%s')
""" % (tuple(sql_data))
try:
self.db.execute(sql)
except self.db.IntegrityError:
db_info("忽略已存在的数据（%s %s %s）" % tuple(json_data[-1].split('-')))
else:
db_info("插入一条数据（%s %s %s）" % tuple(json_data[-1].split('-')))
@property
def exit(self):
self.db.close
def __init__(self, store, size=20, housetype=1, source=1):
DBController.__init__(self)
self.store = store # 所属门店
self.size = size # 推送条数
self.source = source # 房源来源（1-推广 2-本地）
self.housetype = housetype # 房型
self.house_list = list() # 搜索结果（size的5倍）
self.__search_house_list__ # 执行搜索操作
def get_house_page_failed(self):
'''返回获取房源页面详情信息失败的房源编号列表'''
from .sql_template import get_house_page_failed_sql
DBController.execute(self, get_house_page_failed_sql)
house_id_list = list()
for house_id in self.cur.fetchall():
house_id_list.append(house_id[0])
return house_id_list
def insert_house(self, district, house_info_list):
'''向house_info表中插入多条房源信息'''
from .sql_template import house_info_insert_sql, local_date
for house_info in house_info_list:
try:
DBController.execute(self, (house_info_insert_sql.format(
district=district, insert_date=str(local_date))) %
(tuple(house_info)))
db_info("插入房间【%s】成功" % house_info[0])
except self.IntegrityError:
db_info("忽略插入重复【%s】" % house_info[0])
except Exception as e:
db_err("未知错误！输出house_info待确定【%s】 %s %s" %
(house_info[0], str(house_info), str(e)))
def update_house_info(self, house_info_list):
'''将房间详情页面的信息'''
from .sql_template import update_house_info_sql
for house_info in house_info_list:
update_house_info_sql_exec = str()
try:
update_house_info_sql_exec = (update_house_info_sql.format(
house_id=house_info[0])) % (tuple(house_info[1:]))
DBController.execute(self, update_house_info_sql_exec)
db_info("更新房源【%s】详情成功" % house_info[0])
except IndexError:
db_err("[IndexError]更新页面详情数据错误 %s" % str(house_info))
except Exception as e:
db_err("更新页面详情数据错误 %s %s" %
(str(e), update_house_info_sql_exec))
def update_house_stat(self, house_stat_list):
'''更新房间统计信息'''
from .sql_template import update_house_stat_sql, insert_house_stat_json_sql
for house_stat in house_stat_list:
try:
update_house_stat_sql_exec = (update_house_stat_sql.format(
house_id=house_stat[0])) % (tuple(house_stat[1:-1]))
DBController.execute(self, update_house_stat_sql_exec)
DBController.execute(
self, insert_house_stat_json_sql %
(tuple([house_stat[0]] + [house_stat[-1]])))
db_info("更新房源【%s】统计成功" % house_stat[0])
except self.IntegrityError:
db_info("重复的插入统计表【%s】" % house_stat[0])
except Exception as e:
db_err("更新统计信息错误 %s %s" % (str(e), update_house_stat_sql_exec))
def get_hc_ids(self, start=0, num=80):
'''向数据库请求num数量的房间ID+地标ID'''
from .sql_template import get_hc_id_sql
count = self._count(start=start)
for t in range(0, int(count / num) + 1):
id_list = list()
if t != int(count / num):
for i in range(1, num + 1):
id_list.append(str(start + t * num + i))
else:
for i in range(int(count / num) * num, count + 1):
id_list.append(str(start + i))
id_list = ",".join(id_list)
DBController.execute(self, get_hc_id_sql % (id_list))
hc_id_list = self.cur.fetchall()
yield (hc_id_list)
class HouseSelectorDB(DBController):
__name__ = "HouseSelectorDB"
def __init__(self):
self.db = DBController()
def insert(self, house_info):
self.house_info = house_info
sql = sql_creator(self.house_info)
try:
self.db.execute(sql)
except self.db.IntegrityError:
db_warn("[%s]重复插入数据[%d]" % (self.__name__, self.house_info["id"]))
except Exception:
db_err("[%s]插入数据错误[%d] %s" %
(self.__name__, self.house_info['id'], sql))
else:
db_info("[%s]成功插入数据[%d]" % (self.__name__, self.house_info["id"]))
@property
def exit(self):
self.db.close
def __init__(self):
self.db = DBController()
def __init__(self):
DBController.__init__(self)
def backup_table(db, t):
'''备份缓存表操作'''
b1_sql = rename_sql.format(fromname=house_info_name,
toname="%s_%s" % (house_info_name, t))
b2_sql = rename_sql.format(fromname=house_stat_name,
toname="%s_%s" % (house_stat_name, t))
db.execute(b1_sql)
db.execute(b2_sql)
create(db)
if __name__ == "__main__":
# 创建数据库连接
db = DBController()
# 获取当前时间
t = Time.now_date_str()
# 不携带参数的情况下交互式数据库操作
if len(sys.argv) == 1:
while True:
opeartor = input(welcome_str)
if opeartor.strip() == "0":
db_optor_info("程序退出...")
break
def __init__(self):
# 初始化DBController对象
DBController.__init__(self)