def __init__(self, mysql_user, mysql_passwd, mysql_host="127.0.0.1", mysql_port=3306, mysql_db="OSTK", simplegeo_oauth_token=None, simplegeo_oauth_secret=None):
# Load the USDA IDs
with open("ostk_data.pickle", "rb") as datafile:
data = cPickle.load(datafile)
self.counties = data["counties"]
self.states = data["states"]
# self.states_with_threatened_endangered = data["states_with_threatened_endangered"]
# Instantiate a ostk SimpleGeo object for resolving coordinates to states/counties
if (simplegeo_oauth_token and simplegeo_oauth_secret):
self.simplegeo = SimpleGeo(simplegeo_oauth_token, simplegeo_oauth_secret,mysql_user, mysql_passwd, mysql_host, mysql_port, mysql_db)
else:
self.simplegeo = None
# Optionally use a MySQL database for caching
if not mysql_user:
self.cache = False
self.db = None
self.cursor = None
return
self.cache = True
self.db = MySQLdb.connect(host=mysql_host, user=mysql_user, passwd=mysql_passwd, db=mysql_db, port=mysql_port)
self.cursor = self.db.cursor()
self.cursor.execute("""CREATE TABLE IF NOT EXISTS `ostk_usda` (
`request` varchar(255) NOT NULL,
`response` text NOT NULL,
PRIMARY KEY (`request`));""")
class USDA:
def __init__(self, mysql_user, mysql_passwd, mysql_host="127.0.0.1", mysql_port=3306, mysql_db="OSTK", simplegeo_oauth_token=None, simplegeo_oauth_secret=None):
# Load the USDA IDs
with open("ostk_data.pickle", "rb") as datafile:
data = cPickle.load(datafile)
self.counties = data["counties"]
self.states = data["states"]
# self.states_with_threatened_endangered = data["states_with_threatened_endangered"]
# Instantiate a ostk SimpleGeo object for resolving coordinates to states/counties
if (simplegeo_oauth_token and simplegeo_oauth_secret):
self.simplegeo = SimpleGeo(simplegeo_oauth_token, simplegeo_oauth_secret,mysql_user, mysql_passwd, mysql_host, mysql_port, mysql_db)
else:
self.simplegeo = None
# Optionally use a MySQL database for caching
if not mysql_user:
self.cache = False
self.db = None
self.cursor = None
return
self.cache = True
self.db = MySQLdb.connect(host=mysql_host, user=mysql_user, passwd=mysql_passwd, db=mysql_db, port=mysql_port)
self.cursor = self.db.cursor()
self.cursor.execute("""CREATE TABLE IF NOT EXISTS `ostk_usda` (
`request` varchar(255) NOT NULL,
`response` text NOT NULL,
PRIMARY KEY (`request`));""")
def query_by_coordinates(self, query, lat, lon):
# Resolve coordinates into state and county (if possible) then do a lookup
if not self.simplegeo:
raise RuntimeError, "no SimpleGeo credentials provided"
county, state = self.simplegeo.look_up(lat, lon)
return self.query_by_state_andor_county(query, state, county)
def query_by_state_andor_county(self, query, state, county=None):
# Prepare the query string
if "&download=on" not in query:
query += "&download=on"
state = state.title()
if not self.states.has_key(state):
raise ValueError, "state not found"
if county:
county = county.title()
county_key = state + ":" + county
if not self.counties.has_key(county_key):
raise ValueError, "county not found"
query += ("&county=%s" % self.counties[county_key])
else:
query += ("&statefips=%s" % self.states[state])
if len(query) > 255:
raise ValueError, "derived query is longer than 255 characters which is unsupported for now"
# Return cached results if available; if not, do a full lookup
if self.cache:
self.cursor.execute("SELECT response FROM ostk_usda WHERE request=%s", [query])
results = self.cursor.fetchall()
if len(results) != 0:
result = results[0][0]
return result
result_file = urllib2.urlopen("http://plants.usda.gov/java/AdvancedSearchServlet", query)
result = json.dumps(list(csv.DictReader(result_file)))
if self.cache:
self.cursor.execute("INSERT INTO ostk_usda VALUES (%s, %s)", [query, result])
self.db.commit()
return result
def __del__(self):
# Clean up the database resources if they were in use
if self.cache:
self.cursor.close()
self.db.close()
