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}