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()