def Related(code): ''' (string) -> list Gets a list of species/subspecies that share the code root (i.e. the first 5 characters of the code). If your argument exceeds five characters, the function will ignore all but the first five. If you submit an argument with fewer than five characters, the function will return all GAP codes that begin with whatever argument you submitted. Argument: code -- the species' unique GAP ID or the beginning of the GAP ID Examples: >>> Related("aBAFR") [u'aBAFRc', u'aBAFRl', u'aBAFRx'] >>> Related("aBAFRc") [u'aBAFRc', u'aBAFRl', u'aBAFRx'] >>> Related("aBA") [u'aBAFRc', u'aBAFRl', u'aBAFRx', u'aBATRx'] ''' import gapdb code = code[0:5] sppCursor, sppCon = gapdb.ConnectSppDB() qryResult = sppCursor.execute("""SELECT strUniqueID FROM dbo.tblAllSpecies WHERE strUniqueID LIKE '""" + code + """%' """).fetchall() del sppCursor sppCon.close() spCodes =[item[0] for item in qryResult] return spCodes
def Taxonomy(spCode): ''' (string) -> tuple Returns a tuple of 8 items: GAP species code, class, order, family, genus, species, subspecies, full scientific name, and common name. Argument: spCode -- the species' unique GAP ID. Example: >>> Taxonomy("abafrc") (u'aBAFRc', u'Amphibia', u'Anura', u'Craugastoridae', u'Craugastor', u'augusti', u'cactorum', u'Craugastor augusti cactorum', u'Western Barking Frog') ''' import gapdb, pyodbc try: sppCursor, sppCon = gapdb.ConnectSppDB() # Query the species databsae to return a tuple of taxonomic info qry = sppCursor.execute("""SELECT al.strUniqueID, al.strClass, al.strOrder, al.strFamily, al.strGenus, al.strSpecies, al.strSubspecies, al.strFullSciName, al.strCommonName FROM dbo.tblAllSpecies AS al WHERE al.strUniqueID = ?""", spCode).fetchone() del sppCursor sppCon.close() # If the result is not of type pyodbc.row, return None if type(qry) <> pyodbc.Row: return None # If the result if of type pyodbc.row, then... # Create an empty list tL = [] # For each item in the query result for i in qry: # If it = None, if i is None: # Then append an empty string tL.append('') # If the item is not None, then append the stripped item else: tL.append(i.strip()) # Then create a tuple of the list taxTup = tuple(tL) return taxTup except Exception, e: print 'Exception in function Taxonomy().' print e.message
def __RunQuery(qry): try: # Connect to the database sppCursor, sppConn = gapdb.ConnectSppDB() # Get the range table for the species rangeAtts = sppCursor.execute(qry.query, qry.sp).fetchall() # Close the database connection sppConn.close() return rangeAtts except Exception as e: print 'Error in gaprange.__RunQuery()' print e
def RangeTable_NEW(sp, outDir, state=False, includeMigratory=True, includeHistoric=True): ''' (string, string, string, string, string) -> string Creates a comma-delimited text file of the species' range, with fields indicating 12-digit HUC, origin, presence, reproductive use, and seasonality. Returns the full, absolute path to the output text file. Arguments: sp -- The species six-character unique GAP code outDir -- The directory within which you wish to place the output text file state -- An optional parameter to indicate a state to which you wish to limit the result includeMigratory -- An optional boolean parameter indicating whether to include migratory range in the output. By default, it is set to True includeHistoric -- An optional boolean parameter indicating whether to include historic/extirpated range in the output. By default, it is set to True Example: >>> RangeTable('mNAROx', 'My_Range_Folder', state="OH") ''' import pandas as pd, os try: # Ensure that the output directory exists if the directory exists, go on # If the directory does not yet exist, create it and all necessary parent directories oDir = os.path.abspath(outDir) if not os.path.exists(oDir): os.makedirs(oDir) ## Connect to the Species Database sppCursor, sppConn = gapdb.ConnectSppDB() # Build an SQL statement that returns relevant fields in the # appropriate taxa table tblRanges_<taxa> using a species code # First get the taxon code then get a dataframe of the hucs used by the species, # then clean it up tax = dictionaries.taxaDict[sp[0]] sql = """SELECT DISTINCT t.strUC, t.strHUC12RNG, intGapOrigin, intGapPres, intGapRepro, intGapSeas FROM dbo.tblRanges_""" + tax + """ as t WHERE (t.strUC = ?)""" spDF = pd.io.sql.read_sql(sql, sppConn, params=sp.split()) if len(spDF) == 0: print("ERROR - No range data was retrieved for {0}".format(sp)) spDF.drop(["strUC"], axis=1, inplace=True) spDF.columns=["HUC12","Origin","Presence","Repro","Season"] spDF["HUC12"] = [str(i) for i in spDF["HUC12"]] except Exception as e: print("There was an error getting the species dataframe- {0}".format(e)) try: # Apply any filters specified if not includeMigratory: # Filter out migratory records spDF = spDF.loc[(spDF["Season"] != 2) & (spDF["Season"] != 5) & (spDF["Season"] != 8)] if not includeHistoric: # Filter out historic records spDF = spDF.loc[spDF["Presence"] != 7] except Exception as e: print("There was an error filtering the dataframe- {0}".format(e)) try: if state: # Make sure that the user entered a valid state abbreviation or name fromAbbr = dictionaries.stateDict_From_Abbr toAbbr = dictionaries.stateDict_To_Abbr if state in fromAbbr: stateName = fromAbbr[state] elif state in toAbbr: stateName = state ## Get a dataframe of hucs in the state sql_State = """SELECT s.strHUC12RNG FROM dbo.tblBoundaryCrosswalk as s WHERE (s.strStateName = ?)""" stateDF = pd.io.sql.read_sql(sql_State, sppConn, params=[stateName]) #Join the state-huc dataframe with the species-huc dataframe to get hucs in state the #species uses. Clean up. spDF = pd.merge(spDF, stateDF, left_on="HUC12", right_on="strHUC12RNG", how='right') spDF.drop(["strHUC12RNG"], inplace=True, axis=1) except Exception as e: print("There was an error with the state-huc dataframe - {0}".format(e)) try: #Write final dataframe to csv file spDF.to_csv(outDir + "/" + sp + "_RangeTable.txt", sep=",", index=False) # Close the database connection sppConn.close() except Exception as e: print("There was an error writing to txt file - {0}".format(e)) # Return the path to the table return outDir + "/" + sp + "_RangeTable.txt"
def SppInAOI(AOIShp, hucShp, workDir, origin, season, reproduction, presence): ''' (string, string, string, string, list, list, list, list) -> list Returns a list of species occurring within the provided polygon. Runtime is about 3-5 minutes. Arguments: AOIShp -- A shapefile polygon (dissolved) to investigate. Should have the same coordinate systems as the huc shapefile. hucShp -- A 12 digit huc shapefile that matches the GAP species database hucs. workDir -- Where to work and save output. origin -- Origin codes to include. season -- Season codes to include. reproduction -- Reproduction codes to include. presence -- Presence codes to include. Example: >>> sppList = SppInPolygon(AOIShp = "T:/Temp/BlueMountains2.shp", hucShp = config.hucs, workDir = "T:/Temp/", origin = [1], season = [1, 3, 4], reproduction = [1, 2, 3], presence = [1, 2, 3]) ''' import arcpy arcpy.ResetEnvironments() arcpy.env.overwriteOutput=True arcpy.env.workspace = workDir import pandas as pd ############################################## Get list of hucs within polygon ############################################################################### print("\nSelecting HUCs completely within the AOI shapefile\n") arcpy.management.MakeFeatureLayer(hucShp, 'HUCs_lyr') arcpy.management.MakeFeatureLayer(AOIShp, 'shp_lyr') arcpy.management.SelectLayerByLocation('HUCs_lyr', 'INTERSECT', 'shp_lyr') # Make an empty list to append selHUCsList = [] # Get the fields from the input selected HUCs layer fields = arcpy.ListFields('HUCs_lyr') # Create a fieldinfo object fieldinfo = arcpy.FieldInfo() # Use only the HUC12RNG field and set it to fieldinfo for field in fields: if field.name == "HUC12RNG": fieldinfo.addField(field.name, field.name, "VISIBLE", "") # The selected HUCs layer will have fields as set in fieldinfo object arcpy.MakeTableView_management("HUCs_lyr", "selHUCsTV", "", "", fieldinfo) # Loop through the selected HUCs and add them to a list for row in sorted(arcpy.da.SearchCursor('selHUCsTV', ['HUC12RNG'])): selHUCsList.append(row[0]) # Make the selected HUCs list a set for comparing with species range HUCs selHUCsSet = set(selHUCsList) ################################################# Get a species list to assess ############################################################################### print("Comparing species ranges to selected HUCs\n") ## Make WHRdb and Species databse connections whrCursor, whrConn = gapdb.ConnectWHR() sppCursor, sppConn = gapdb.ConnectSppDB() # Build and SQL statement that returns CONUS # full species codes and names that are in the modeled list sql = """SELECT t.strUC, t.strCommonName, t.strScientificName, t.strsubSciNameText, t.ysnInclude, intRegionCode FROM dbo.tblAllSpecies as t WHERE (t.ysnInclude = 'True') AND t.intRegionCode < 7""" # Pull into a dataframe dfAllSpp = pd.read_sql(sql, whrConn) # Drop the region code and include fields dfAllSpp = dfAllSpp.drop(['intRegionCode','ysnInclude'], axis=1) # Drop duplicates to get unique species codes dfUnique = dfAllSpp.drop_duplicates(subset='strUC', keep='first') ################################ Asses each species' occurence in polygon hucs ############################################################################### # List to collect species in AOI masterList = [] for SC in list(dfUnique.strUC): taxa = dictionaries.taxaDict[SC[0]] # What hucs are species' in? sql = """SELECT t.strHUC12RNG, t.strUC, t.intGapOrigin, t.intGapPres, t.intGapRepro, t.intGapSeas FROM dbo.tblRanges_""" + taxa + """ as t WHERE (t.strUC = '""" + str(SC) + """') AND t.strHUC12RNG < '190000000000'""" dfRngHUCs = pd.read_sql(sql, sppConn) # Which hucs have acceptable attributes? select={'intGapPres':presence, 'intGapSeas':season, 'intGapOrigin':origin, 'intGapRepro':reproduction} dfS1 = dfRngHUCs[dfRngHUCs[select.keys()].isin(select).all(axis=1)] # Get the strHUC12RNG column into a set SpeciesSet = set(dfS1[dfS1.columns[0]].tolist()) # Compare the species and AOI huc sets to see if there's any overlap. if len(selHUCsSet & SpeciesSet) > 0: print(gapdb.NameCommon(SC)) masterList.append(SC) else: pass if len(masterList) == 0: print "!!!! There was some sort of problem !!!!\n" else: # Delete cursors and close db connections sppConn.close() whrConn.close() del sppCursor, sppConn del whrCursor, whrConn return masterList
def GetEndemics(extentShapefile, shpHucs, workDir, keyword): """ (string, string, string) -> string & saved csv file. Use this to create a CSV file of species' (including subspecies) whose ranges are endemic to a specified input AOI shapefile. Generally, the AOI shapefile should be a single polygon. The script uses a select by location function in which 12-digit HUCs are selected that are completely within the AOI shapefile. If there is more than one polygon, the selections will be made within each individual polygon - i.e. there will by multiple selections as opposed to one continuous set of HUCs. The shapefile must have projection and coordinate system that matches the 12-digit HUC shapefile from which species' ranges are derived. The final CSV file will contain the following fields: Species Code Scientific Name Common Name NOTE: Be careful with this function, finding endemics may be more difficult than it seems. This obviously does not take into account species' ranges outside CONUS since GAP ranges are not complete outside the lower 48 (with some AK, HI, PR exceptions). And, obviously again, this does not take into consideration ranges in other countries during different seasons. It would be possible to alter this script to look for seasonal endemism. As currently written, the sql query to get HUC range data includes all seasons and known, possibly, and potentially present ocurrence status. Also, bear in mind that you may need to take extra caution regarding endemic species that are distributed up to the edges of oceans. Arguments: extentShapfile -- A designated AOI shapefile with projection and coordinate system to match the 12-digit HUC range shapefile. shpHucs -- A 12-digit HUC range shapefile. workDir -- Where to save the csv file (KeywordEndemicSpecies.txt) keyword -- Keyword to use in output file name, whatever you want that to be. Example: >> csvPath = GetEndemics(extent="T:/Project/ProjectExtent.shp", workDir='T:/Project/', shpHUCs="T:/hucs.shp", keyword="ThisProject") """ import arcpy import pandas as pd, datetime from datetime import datetime starttime = datetime.now() # +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ # ++++ Directory & File Locations ++++ arcpy.env.workspace = workDir # *************************************************************** ''' ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Select HUCs of the CONUS HUC shapefile that are completely within the user defined source layer feature shapefile. Each must be made into a layer prior to using SelectLayerByLocation ''' print "\nSelecting HUCs completely within the designated shapefile ....\n" arcpy.MakeFeatureLayer_management(shpHucs, 'HUCs_lyr') arcpy.MakeFeatureLayer_management(extentShapefile, 'shp_lyr') arcpy.SelectLayerByLocation_management('HUCs_lyr', 'COMPLETELY_WITHIN', 'shp_lyr') # Make an empty list to append selHUCsList = [] # Get the fields from the input selected HUCs layer fields = arcpy.ListFields('HUCs_lyr') # Create a fieldinfo object fieldinfo = arcpy.FieldInfo() # Use only the HUC12RNG field and set it to fieldinfo for field in fields: if field.name == "HUC12RNG": fieldinfo.addField(field.name, field.name, "VISIBLE", "") # The selected HUCs layer will have fields as set in fieldinfo object arcpy.MakeTableView_management("HUCs_lyr", "selHUCsTV", "", "", fieldinfo) # Loop through the selected HUCs and add them to a list for row in sorted(arcpy.da.SearchCursor('selHUCsTV', ['HUC12RNG'])): selHUCsList.append(row[0]) # Make the selected HUCs list a set for comparing with species range HUCs selHUCsSet = set(selHUCsList) ''' ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Get HUC range data from the Species Database ''' print "\n++++++++++++++ Comparing species ranges to selected HUCs +++++++++++++++++\n" # Make an empty master dataframe dfMaster = pd.DataFrame() ## Make WHRdb and Species databse connections whrCursor, whrConn = gapdb.ConnectWHR() sppCursor, sppConn = gapdb.ConnectSppDB() # Build and SQL statement that returns CONUS # full species codes and names that are in the modeled list sql = """SELECT t.strUC, t.strCommonName, t.strScientificName, t.strsubSciNameText, t.ysnInclude, intRegionCode FROM dbo.tblAllSpecies as t WHERE (t.ysnInclude = 'True') AND t.intRegionCode < 7""" # Pull into a dataframe dfAllSpp = pd.read_sql(sql, whrConn) # Drop the region code and include fields dfAllSpp = dfAllSpp.drop(['intRegionCode','ysnInclude'], axis=1) # Drop duplicates to get unique species codes dfUnique = dfAllSpp.drop_duplicates(subset='strUC', keep='first') ''' Loop over the unique species list to calculate each one's range size and percentage ''' # Set up an iterator to get row index for dfUSpp dataframe # First, sort and reset the row index in dfUnique dataframe dfSort = dfUnique.sort_values(by='strUC') dfUSpp = dfSort.reset_index(drop=True) i = -1 for spp in dfUSpp['strUC']: print "Working on " + spp + " ...." # Add one to the iterartor i += 1 # Now, get the scientific name, subspecies name, # common name, and species code based on row index SN = dfUSpp['strScientificName'][i] SSN = dfUSpp['strsubSciNameText'][i] CN = dfUSpp['strCommonName'][i] SC = dfUSpp['strUC'][i] # Get the taxon from the species code if spp[0] == 'a': taxa = 'Amphibians' elif spp[0] == 'b': taxa = 'Birds' elif spp[0] == 'm': taxa = 'Mammals' else: taxa = 'Reptiles' # Build an SQL statement that returns relevant fields in the # appropriate taxa table tblRanges_<taxa> using a species code # Limit the HUC codes to only CONUS - i.e. < 190000000000 sql = """SELECT t.strHUC12RNG, t.strUC, t.intGapOrigin, t.intGapPres, t.intGapRepro, t.intGapSeas FROM dbo.tblRanges_""" + taxa + """ as t WHERE (t.strUC = '""" + str(spp) + """') AND t.strHUC12RNG < '190000000000'""" dfRngHUCs = pd.read_sql(sql, sppConn) # Select only known, possibly, or potentially present; # year-round, winter, or summer seasons select={'intGapPres':[1,2,3], 'intGapSeas':[1,3,4]} dfS1 = dfRngHUCs[dfRngHUCs[list(select)].isin(select).all(axis=1)] # Get the strHUC12RNG column into a set dfS1Set = set(dfS1[dfS1.columns[0]].tolist()) # Subtract this species' range HUC set from the shapefile's HUC set # to see if the set is empty => all range HUCs for the species would # then be entirely within the shapefile's interior HUCs if len(dfS1Set - selHUCsSet) == 0: print SN, "range is endemic to the input shapefile\n" # Add the species' info to a dataframe dfMaster = dfMaster.append({'Species Code':SC, 'Scientific Name':SN, 'subspecies Name':SSN, 'Common Name':CN}, ignore_index=True) else: print "Range not endemic to AOI. Moving on to next species...\n" # Check to see if there are any species with their range entirely # within the designated shapefile. If not print message to the screen if len(dfMaster) == 0: print " ========= No species have endemic range within the AOI =========\n" else: # Reorder columns in completed dataframe dfMaster = dfMaster[['Species Code', 'Scientific Name','Common Name']] # Export to text file outFileName = workDir + keyword + "EndemicSpeciesList.txt" dfMaster.to_csv(outFileName) # Return dfMaster return outFileName # Delete cursors and close db connections sppConn.close() whrConn.close() del sppCursor, sppConn del whrCursor, whrConn del dfAllSpp, dfUnique, dfSort, dfUSpp del dfS1, dfS1Set endtime = datetime.now() delta = endtime - starttime print "+"*35 print "Processing time: " + str(delta) print "+"*35 print("!!! BE SURE TO READ THE NOTES IN THE DOCUMENTATION !!!")
def ListIntroducedSpp(anyIntroducedHUCs=True): ''' () -> list Gets a list of GAP species codes for all species/subspecies that have any introduced range. Arguments: anyIntroducedHUCs - Boolean argument indicating whether species with any introduced range--as opposed to all introduced range--are returned. By default, it is set to True, meaning that species with even a single introduced HUC among any number of native or reintroduced HUCs will be returned. ''' qry = ''' SELECT DISTINCT dbo.tblAllSpecies.strUniqueID FROM dbo.tblAllSpecies INNER JOIN dbo.tblRanges_Birds ON dbo.tblAllSpecies.strUniqueID = dbo.tblRanges_Birds.strUC WHERE (((dbo.tblRanges_Birds.intGapOrigin)=2)) AND dbo.tblAllSpecies.strModelStatus = 'Complete' UNION SELECT DISTINCT dbo.tblAllSpecies.strUniqueID FROM dbo.tblAllSpecies INNER JOIN dbo.tblRanges_Mammals ON dbo.tblAllSpecies.strUniqueID = dbo.tblRanges_Mammals.strUC WHERE (((dbo.tblRanges_Mammals.intGapOrigin)=2)) AND dbo.tblAllSpecies.strModelStatus = 'Complete' UNION SELECT DISTINCT dbo.tblAllSpecies.strUniqueID FROM dbo.tblAllSpecies INNER JOIN dbo.tblRanges_Reptiles ON dbo.tblAllSpecies.strUniqueID = dbo.tblRanges_Reptiles.strUC WHERE (((dbo.tblRanges_Reptiles.intGapOrigin)=2)) AND dbo.tblAllSpecies.strModelStatus = 'Complete' UNION SELECT DISTINCT dbo.tblAllSpecies.strUniqueID FROM dbo.tblAllSpecies INNER JOIN dbo.tblRanges_Amphibians ON dbo.tblAllSpecies.strUniqueID = dbo.tblRanges_Amphibians.strUC WHERE (((dbo.tblRanges_Amphibians.intGapOrigin)=2)) AND dbo.tblAllSpecies.strModelStatus = 'Complete'; ''' # Connect to the database sppCursor, sppConn = gapdb.ConnectSppDB() # Get the range table for the species sppInt = sppCursor.execute(qry).fetchall() sppInt = [i[0] for i in sppInt] if not anyIntroducedHUCs: qry = ''' SELECT DISTINCT dbo.tblAllSpecies.strUniqueID FROM dbo.tblAllSpecies INNER JOIN dbo.tblRanges_Birds ON dbo.tblAllSpecies.strUniqueID = dbo.tblRanges_Birds.strUC WHERE (((dbo.tblRanges_Birds.intGapOrigin)<>2)) AND dbo.tblAllSpecies.strModelStatus = 'Complete' UNION SELECT DISTINCT dbo.tblAllSpecies.strUniqueID FROM dbo.tblAllSpecies INNER JOIN dbo.tblRanges_Mammals ON dbo.tblAllSpecies.strUniqueID = dbo.tblRanges_Mammals.strUC WHERE (((dbo.tblRanges_Mammals.intGapOrigin)<>2)) AND dbo.tblAllSpecies.strModelStatus = 'Complete' UNION SELECT DISTINCT dbo.tblAllSpecies.strUniqueID FROM dbo.tblAllSpecies INNER JOIN dbo.tblRanges_Reptiles ON dbo.tblAllSpecies.strUniqueID = dbo.tblRanges_Reptiles.strUC WHERE (dbo.tblRanges_Reptiles.intGapOrigin<>2) AND dbo.tblAllSpecies.strModelStatus = 'Complete' UNION SELECT DISTINCT dbo.tblAllSpecies.strUniqueID FROM dbo.tblAllSpecies INNER JOIN dbo.tblRanges_Amphibians ON dbo.tblAllSpecies.strUniqueID = dbo.tblRanges_Amphibians.strUC WHERE (dbo.tblRanges_Amphibians.intGapOrigin <> 2) AND dbo.tblAllSpecies.strModelStatus = 'Complete'; ''' sppNative = sppCursor.execute(qry).fetchall() sppNative = [i[0] for i in sppNative] sppInt = list(set(sppInt) - set(sppNative)) # Close the database connection sppConn.close() return sppInt
def PublishRanges(spp): ''' (list) -> insertion of records into table "Publishes" ranges for the species in the list provided. Records for each species are deleted from published table and deep storage, then are replaced with new records from the temporary range tables. Arguments: spp -- a python list of species codes (strUC) to process. Example: >>> PublishRanges(["aadsax", "aamtox"]) ''' for i in spp: print "Processing: " + i deepTest = "" # create cursor based on connection sppCursor, sppConnection = gapdb.ConnectSppDB() # create dictionary for proper table lookup RangeTabledict = dictionaries.taxaDict # look up the correct species database range table speciesTable = RangeTabledict[i[0]] # Test to see if the species is in the tmp table try: deepTest = "DeepStorage" deepTest = sppCursor.execute("""SELECT t.strHUC12RNG FROM dbo.tblRanges_tmp_{0} as t WHERE t.strUC = '{1}'""".format(speciesTable, i)).fetchone()[0] except: pass # if the species wasn't in the temp table, then say so and quit if deepTest == "DeepStorage": print "Failed to Publish: " + i +" There are no records in the tmp tbl\n" else: # build and execute sql statement to delete all records with a strUC # equal to the species UC sppCursor.execute("""DELETE from dbo.tblRanges_{0} WHERE strUC = '{1}'""".format(speciesTable, i)) sppCursor.execute("""DELETE from dbo.tblRanges_DS_{0} WHERE strUC = '{1}'""".format(speciesTable, i)) # insert records from tmp table to current range table sppCursor.execute ("""INSERT INTO dbo.tblRanges_{0} (strUC, strHUC12RNG, intGapOrigin,intGapPres, intGapRepro, intGapSeas, StrCompSrc, strNS_cd, strNWGap_cd, strSEGap_cd, strSWGap_cd) SELECT strUC, strHUC12RNG, intGapOrigin, intGapPres, intGapRepro, intGapSeas, StrCompSrc, strNS_cd, strNWGap_cd, strSEGap_cd, strSWGap_cd FROM dbo.tblRanges_tmp_{0} WHERE dbo.tblRanges_tmp_{0}.strUC='{1}';""".format(speciesTable, i)) print "Published to Public table" # insert records from tmp table to deep storage sppCursor.execute ("""INSERT INTO dbo.tblRanges_DS_{0} (strUC, strHUC12RNG, intGapOrigin,intGapPres, intGapRepro, intGapSeas, StrCompSrc, strNS_cd, strNWGap_cd, strSEGap_cd, strSWGap_cd) SELECT strUC, strHUC12RNG, intGapOrigin, intGapPres, intGapRepro, intGapSeas, StrCompSrc, strNS_cd, strNWGap_cd, strSEGap_cd, strSWGap_cd FROM dbo.tblRanges_tmp_{0} WHERE dbo.tblRanges_tmp_{0}.strUC='{1}';""".format(speciesTable, i)) print "Published to Deep Storage table" #save changes to database sppConnection.commit() # create cursor for status updates StatusCursor, StatusConnection = gapdb.ConnectSppDB() # update the status of the species print "Updating range status for " + i StatusCursor.execute("""UPDATE dbo.tblAllSpecies SET strRangeStatus='{1}' WHERE tblAllSpecies.strUniqueID='{0}'""".format(i, "complete")) # Commit changes to DB StatusConnection.commit() print "Completed publishing for: " + i + "\n"