def deleteCatalogTables(datasetName, server='Rainier'):
contYN = input(
'Are you sure you want to delete all of the catalog tables for ' +
datasetName + ' ? [yes/no]: ')
if contYN == 'yes':
""" delete tblVariables, then tblDataset_References then finally tblDatasets """
print('connecting to database...')
conn = dc.dbConnect(usr=usr, psw=psw)
cursor = conn.cursor()
print('db connection successful')
Dataset_ID = str(findID(datasetName, catalogTable='tblDatasets'))
print('Dataset ID used to remove catalog tables: ', Dataset_ID)
cur_str = """DELETE FROM [Opedia].[dbo].[tblVariables] WHERE [Dataset_ID] = """ + Dataset_ID
cursor.execute(cur_str)
print('-- Instances of ' + datasetName + ' removed from tblVariables')
cur_str = """DELETE FROM [Opedia].[dbo].[tblDataset_References] WHERE [Dataset_ID] = """ + Dataset_ID
cursor.execute(cur_str)
print('-- Instances of ' + datasetName +
' removed from tblDataset_References')
cur_str = """DELETE FROM [Opedia].[dbo].[tblDatasets] WHERE [ID] = """ + Dataset_ID
cursor.execute(cur_str)
print('-- Instances of ' + datasetName + ' removed from tblDatasets')
print('Commiting changes...')
conn.commit()
conn.rollback()
print('Changes to dB commited')
else:
print('Catalog tables for ' + datasetName + ' not deleted')
def insertSeaFlowCruises_ST_bounds():
cruise_ID_list = iF.cruise_ID_list()['ID_list']
cruise_nickname_list = iF.cruise_ID_list()['Nickname_list']
server = 'Rainier'
for cruise_ID, nickname in zip(cruise_ID_list, cruise_nickname_list):
conn = dc.dbConnect(server)
cursor = conn.cursor()
# print(cruise_ID,nickname)
Start_Time = iF.findMinMaxDate_cruiseID(cruise_ID)['minDate']
End_Time = iF.findMinMaxDate_cruiseID(cruise_ID)['maxDate']
Lat_Min = iF.findMinMaxSpatial_cruiseID(cruise_ID)['minlat']
Lat_Max = iF.findMinMaxSpatial_cruiseID(cruise_ID)['maxlat']
Lon_Min = iF.findMinMaxSpatial_cruiseID(cruise_ID)['minlon']
Lon_Max = iF.findMinMaxSpatial_cruiseID(cruise_ID)['maxlon']
""" Iterate through cruise_ID_list, insert S,T in the row. SQL update?"""
sql_str = """UPDATE [Opedia].[dbo].[tblCruise] SET Start_Time = '""" + str(
Start_Time
) + """', End_Time = '""" + str(End_Time) + """', Lat_Min = '""" + str(
Lat_Min
) + """', Lat_Max = '""" + str(Lat_Max) + """', Lon_Min = '""" + str(
Lon_Min) + """', Lon_Max = '""" + str(
Lon_Max) + """' WHERE [ID] = '""" + str(cruise_ID) + """'"""
# print(sql_str)
# break
print('updating the spatiotemporal bounds for: ' + str(cruise_ID) +
' ' + str(nickname))
cursor.execute(sql_str)
conn.commit()
cursor.close()
conn.close()
def findID(datasetName, catalogTable, server='Rainier'):
""" this function pulls the ID value from the [tblDatasets] for the tblDataset_References to use """
conn = dc.dbConnect(server)
cursor = conn.cursor()
cur_str = """select [ID] FROM [Opedia].[dbo].[""" + catalogTable + """] WHERE [Dataset_Name] = '""" + datasetName + """'"""
cursor.execute(cur_str)
IDvar = (cursor.fetchone()[0])
return IDvar
def findVariables(datasetName, catalogTable, server='Rainier'):
conn = dc.dbConnect(server)
cursor = conn.cursor()
cur_str = """select [Variables] FROM [Opedia].[dbo].[""" + catalogTable + """] WHERE [Dataset_Name] = '""" + datasetName + """'"""
cursor.execute(cur_str)
IDvar = (cursor.fetchone()[0])
varlist = IDvar.split(',')
return varlist
def findID_CRUISE(cruiseName):
""" this function pulls the ID value from the [tblCruises]"""
server = 'Rainier'
conn = dc.dbConnect(server)
cursor = conn.cursor()
cur_str = """select [ID] FROM [Opedia].[dbo].[tblCruise] WHERE [Name] like '%""" + cruiseName + """%'"""
cursor.execute(cur_str)
IDvar = (cursor.fetchone()[0])
return IDvar
def remove_duplicatesCatalogTables(usr='******', psw='Rdw10^3pb'):
catalogTableList = ['tblDatasets', 'tblDataset_References', 'tblVariables']
conn = dc.dbConnect(usr=usr, psw=psw)
cursor = conn.cursor()
cur_str_tblDatasets = '\n WITH list_duplicates (Dataset_Name, Dataset_Long_Name, duplicate_count) AS\n (\n SELECT Dataset_Name, Dataset_Long_Name,\n ROW_NUMBER() OVER(PARTITION BY Dataset_Name, Dataset_Long_Name ORDER BY Dataset_Name, Dataset_Long_Name) AS duplicate_count\n FROM tblDatasets_copy)\n DELETE FROM list_duplicates WHERE duplicate_count > 1\n '
cur_str_tblDataset_References = '\n WITH list_duplicates (Dataset_ID, Reference, duplicate_count) AS\n (\n SELECT Dataset_ID, Reference,\n ROW_NUMBER() OVER(PARTITION BY Dataset_ID, Reference ORDER BY Dataset_ID, Reference) AS duplicate_count\n FROM tblDataset_References_copy)\n DELETE FROM list_duplicates WHERE duplicate_count > 1\n '
cur_str_tblVariables = '\n WITH list_duplicates (Table_Name, Short_Name, Long_Name, duplicate_count) AS\n (\n SELECT Table_Name, Short_Name, Long_Name,\n ROW_NUMBER() OVER(PARTITION BY Table_Name, Short_Name, Long_Name ORDER BY Table_Name, Short_Name, Long_Name) AS duplicate_count\n FROM tblVariables_copy)\n DELETE FROM list_duplicates WHERE duplicate_count > 1\n '
cursor.execute(tblDatasets)
cursor.execute(tblDataset_References)
cursor.execute(tblVariables)
def findMinMaxDate_cruiseID(ID):
server = 'Rainier'
conn = dc.dbConnect(server)
cursor = conn.cursor()
cur_str_min = """select min(time) FROM [Opedia].[dbo].[tblCruise_Trajectory] where Cruise_ID = '""" + str(ID) + """'"""
cur_str_max = """select max(time) FROM [Opedia].[dbo].[tblCruise_Trajectory] where Cruise_ID = '""" + str(ID) + """'"""
cursor.execute(cur_str_min)
minDate = (cursor.fetchone()[0])
cursor.execute(cur_str_max)
maxDate = (cursor.fetchone()[0])
return {'minDate':minDate,'maxDate':maxDate}
def cruise_ID_list():
server = 'Rainier'
conn = dc.dbConnect(server)
cursor = conn.cursor()
cur_str = """select [ID], [Nickname] FROM [Opedia].[dbo].[tblCruise]"""
cursor.execute(cur_str)
IDlist = (cursor.fetchall())
newID_list = [x[0] for x in IDlist]
newNickname_list = [x[1] for x in IDlist]
return {'ID_list':newID_list,'Nickname_list':newNickname_list}
def lineInsert(tableName,
columnList,
query,
determinator=',',
server='Rainier'):
conn = dc.dbConnect(server)
cursor = conn.cursor()
insertQuery = """INSERT INTO %s %s VALUES %s """ % (tableName, columnList,
query)
print(insertQuery)
cursor.execute(insertQuery)
conn.commit()
def lineInsert(tableName,
columnList,
query,
determinator=',',
server='Rainier',
usr=cr.usr_rainier,
psw=cr.psw_rainier):
conn = dc.dbConnect(usr=usr, psw=psw)
cursor = conn.cursor()
insertQuery = """INSERT INTO %s %s VALUES %s """ % (tableName, columnList,
query)
cursor.execute(insertQuery)
conn.commit()
def findMinMaxSpatial_cruiseID(ID):
server = 'Rainier'
conn = dc.dbConnect(server)
cursor = conn.cursor()
cur_str_minlat = """select min(lat) FROM [Opedia].[dbo].[tblCruise_Trajectory] where Cruise_ID = '""" + str(ID) + """'"""
cur_str_maxlat = """select max(lat) FROM [Opedia].[dbo].[tblCruise_Trajectory] where Cruise_ID = '""" + str(ID) + """'"""
cur_str_minlon = """select min(lon) FROM [Opedia].[dbo].[tblCruise_Trajectory] where Cruise_ID = '""" + str(ID) + """'"""
cur_str_maxlon = """select max(lon) FROM [Opedia].[dbo].[tblCruise_Trajectory] where Cruise_ID = '""" + str(ID) + """'"""
cursor.execute(cur_str_minlat)
minlat = (cursor.fetchone()[0])
cursor.execute(cur_str_maxlat)
maxlat = (cursor.fetchone()[0])
cursor.execute(cur_str_minlon)
minlon = (cursor.fetchone()[0])
cursor.execute(cur_str_maxlon)
maxlon = (cursor.fetchone()[0])
return {'minlat':minlat,'maxlat':maxlat,'minlon':minlon,'maxlon':maxlon}
