def community_incorrect_lbs():
lbs = location.geo()
db = MySQLdb.connect('rds7di028yhg19m2v656o.mysql.rds.aliyuncs.com',"alphago","Alphago0311",'realestate' ,charset="utf8")
cr = db.cursor(cursorclass = MySQLdb.cursors.DictCursor)
sql = 'select * from community where latitude < 38 or latitude > 42 or longitude < 114 or longitude > 118'
cr.execute(sql)
invalidcommunity = cr.fetchall()
for cd in invalidcommunity :
print '%s community lbs invalid (%s,%s)' % ( cd['name'],cd['latitude'],cd['longitude'])
loc = lbs.coder('北京' + cd['name'].encode('utf8'))
if loc != None:
latitude = '%.14f' % loc['lat']
longitude = '%.14f' % loc['lng']
sql = 'update community set latitude = "%s" , longitude = "%s" where id=%s'%(latitude,longitude,cd['id'])
print sql
cr.execute(sql)
db.commit()
# break
else:
print 'translate location failed on %s'% cd['name']
db.close()
return True
def __init__(self, tp, urlptn, data, timeout=10, interval=10):
"""
init Crawler instance
args:
type: list / info
"""
super(CommunityCrawlerT, self).__init__(urlptn, data, timeout,
interval)
self._type = tp
self._geo = location.geo()
def transfer_community():
global cidx
lgs = location.geo()
ldb = MySQLdb.connect('rds7di028yhg19m2v656o.mysql.rds.aliyuncs.com',
"alphago",
"Alphago0311",
'realestate',
charset="utf8")
tdb = MySQLdb.connect('alpha-go.tech',
"root",
"root",
'lianjia',
charset="utf8")
lcr = ldb.cursor(cursorclass=MySQLdb.cursors.DictCursor)
tcr = tdb.cursor(cursorclass=MySQLdb.cursors.DictCursor)
tsql = 'select count(*) as c from community'
tcr.execute(tsql)
cc = tcr.fetchone()
while cidx < cc['c']:
tsql = 'select * from community limit 1 offset %s ' % cidx
tcr.execute(tsql)
community = tcr.fetchone()
lsql = 'select * from community where code="' + community['code'] + '"'
lcr.execute(lsql)
finish = lcr.fetchone()
if finish == None:
print '%d community hit one update %s' % (cidx, community['name'])
loc = lgs.coder(community['name'].encode('utf8'))
if loc != None:
community['latitude'] = '%.14f' % loc['lat']
community['longitude'] = '%.14f' % loc['lng']
lsql = 'insert into community \
(name,code,district,bizcycle,subway,school,link,latitude,longitude) \
values("' +community['name']+'","'+community['code']+'","'+community['district']+'","' \
+community['bizcycle']+'","'+community['subway']+'","'+community['school']+'","'+community['link'] \
+'","'+community['latitude']+'","'+community['longitude']+'")'
else:
lsql = 'insert into community \
(name,code,district,bizcycle,subway,school,link) \
values("' +community['name']+'","'+community['code']+'","'+community['district']+'","' \
+community['bizcycle']+'","'+community['subway']+'","'+community['school']+'","'+community['link']+'")'
print lsql
# break
lcr.execute(lsql)
ldb.commit()
else:
print '%d community hit one === %s' % (cidx, community['name'])
cidx += 1
ldb.close()
tdb.close()
return True
def transfer_deal():
global didx
lgs = location.geo()
ldb = MySQLdb.connect('rds7di028yhg19m2v656o.mysql.rds.aliyuncs.com',
"alphago",
"Alphago0311",
'realestate',
charset="utf8")
tdb = MySQLdb.connect('alpha-go.tech',
"root",
"root",
'lianjia',
charset="utf8")
lcr = ldb.cursor(cursorclass=MySQLdb.cursors.DictCursor)
tcr = tdb.cursor(cursorclass=MySQLdb.cursors.DictCursor)
tsql = 'select count(*) as c from deal'
tcr.execute(tsql)
cc = tcr.fetchone()
while didx < cc['c']:
tsql = 'select * from deal limit 1 offset %s ' % didx
tcr.execute(tsql)
deal = tcr.fetchone()
lsql = 'select * from deal where code="' + deal['code'] + '"'
lcr.execute(lsql)
finish = lcr.fetchone()
if finish == None:
print '%d deal hit one update %s' % (didx, deal['code'])
lsql = 'insert into deal \
(code,community,link,brief,measure,unit_price,total_price,sign_time,floor,direction,\
decoration,elevator,year,school,subway,bedroom,livingroom) \
values("' +deal['code']+'","'+deal['community']+'","'+deal['link']+'","' \
+deal['brief']+'","'+deal['measure']+'","'+deal['unit_price']+'","'+deal['total_price'] \
+'","'+deal['sign_time']+'","'+deal['floor']+'","'+deal['direction']+'","'+deal['decoration']\
+'","'+deal['elevator']+'","'+deal['year']+'","'+deal['school']+'","'+deal['subway']\
+'","'+deal['bedroom']+'","'+deal['livingroom']+'")'
print lsql
# break
lcr.execute(lsql)
ldb.commit()
else:
print '%d deal hit one === %s' % (didx, deal['code'])
didx += 1
ldb.close()
tdb.close()
return True
def community_null_lbs():
global nidx
lbs = location.geo()
db = MySQLdb.connect('rds7di028yhg19m2v656o.mysql.rds.aliyuncs.com',"alphago","Alphago0311",'realestate' ,charset="utf8")
cr = db.cursor(cursorclass = MySQLdb.cursors.DictCursor)
sql = 'select count(*) as c from community'
cr.execute(sql)
cd = cr.fetchone()
while nidx < cd['c']:
sql = 'select * from community limit 1 offset %s ' % nidx
cr.execute(sql)
community = cr.fetchone()
if community['latitude'] == None or community['longitude'] == None :
print '%d community lbs NULL %s' % (nidx, community['name'])
loc = lbs.coder(community['name'].encode('utf8'))
if loc == None:
print 'try + bj'
loc = lbs.coder('北京'+community['name'].encode('utf8'))
if loc != None:
latitude = '%.14f' % loc['lat']
longitude = '%.14f' % loc['lng']
sql = 'update community set latitude = "%s" , longitude = "%s" where id=%s'%(latitude,longitude,community['id'])
print sql
cr.execute(sql)
db.commit()
# break
else:
print 'translate location failed on %s'% community['name']
else:
print '%d %s has lbs (%s, %s)'%(nidx,community['name'],community['latitude'],community['longitude'])
nidx += 1
db.close()
return True