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"
