def fetchSiteKeys(self, source, desig):
"""All site keys for market or submarket indicated by desig.
If desig indicates a market, return all sitekeys. Otherwise, try
to delegate to canonSiteKey"""
# sql = "select ms.sitekey from marketsites ms, markets m, marketnames mn where ms.code=m.code and m.code=mn.code and mn.name=%s and ms.source=%s"
# seems better, but still not perfectly normalized
# but for a market with multiple site keys, returns all
db = trbotdb.connect(CONNECTKEY)
c = db.cursor()
# desig is a market indicator, e.g., IAD, miami
sql = "select distinct ms.sitekey from marketsites ms, markets m, marketnames mn where ms.code=m.code and m.code=mn.code and (mn.name=%s or mn.code=%s) and ms.source=%s"
c.execute(sql, (desig,desig,source))
v = [row[0] for row in c.fetchall()]
if v:
trbotdb.disconnect(db)
return v
canon = self.canonSiteKey(source, desig)
if canon:
return canon
return []
def fetchMarket(self, source, desig):
sql = "select mn.code from trbaux.marketnames mn where lower(mn.name)=lower(%s)"
db = trbotdb.connect(CONNECTKEY)
c = db.cursor()
c.execute(sql, (desig))
for row in c.fetchall():
code = row[0]
if code:
return code
sql = "select code from trbaux.marketsites where source=%s and sitekey=%s"
db = trbotdb.connect(CONNECTKEY)
c = db.cursor()
c.execute(sql, (source, desig))
for row in c.fetchall():
code = row[0]
if code:
return code
print >> sys.stderr, "Warning: s=%s market d=%s not found" % (self.source, desig)
return desig
def canonSiteKey(self, source, desig):
"""If desig is a sitekey itself, return just it as singleton list (possibly canonicalized)"""
# desig is a sitekey, e.g., nova, ftlauderdale
sql = "select distinct ms.sitekey from marketsites ms where ms.sitekey=%s and ms.source=%s"
db = trbotdb.connect(CONNECTKEY)
c = db.cursor()
c.execute(sql, (desig,source))
v = [row[0] for row in c.fetchall()]
if v:
trbotdb.disconnect(db)
# always only one value
return [v[0]]
return []
def fetchMarket(self, source, desig):
sql = "select mn.code from trbaux.marketnames mn where lower(mn.name)=lower(%s)"
db = trbotdb.connect(CONNECTKEY)
c = db.cursor()
c.execute(sql, (desig,))
for row in c.fetchall():
code = row[0]
if code:
return code
# next, look at marketsites (neighborhoods/download roots)
nospaces = desig.replace(' ','')
sql = "select code from trbaux.marketsites where source=%s and (sitekey=%s or city=%s or replace(city,' ','')=%s)"
c.execute(sql, (self.source, desig, desig, nospaces))
for row in c.fetchall():
code = row[0]
if code:
return code
print >> sys.stderr, "Warning: s=%s market d=%s not found" % (self.source, desig)
return desig