def EMEP_APA_Excel(path): """ Copy the excel that has EMEP and APA gas information to database """ tableName = "EMEP_APA_INFO" tableCharacteristics = """ "Nome" VARCHAR(255), "Units" VARCHAR(255), "ID_APA" VARCHAR(255), "ID_EMEP" VARCHAR(255), "Formula" VARCHAR(255), "Formula_codificada" VARCHAR(255)""" copy_excel_database('', path, Database, tableName, tableCharacteristics, create_sql_EMEP_APA_Excel, db_user, db_password, db_host, db_port, "Processed")
def stations_loc(path): """ Upload to database and GeoServer the data about the locations of the stations """ tableName = "stations_location" copy_excel_database( '', path, Database, tableName, """ "Id_rede" INT, "Id_estacao" INT, "Name" VARCHAR(255), "Location" geography(POINT), "Tipo" VARCHAR(255)""", create_sql_stations_loc, db_user, db_password, db_host, db_port) xml = '<featureType><name>%s</name></featureType>' % (tableName) workspace = 'APA_EMEP_Data' datastore = 'Database' database_configuration = { "dataStore": { "name": "{}".format(datastore), "connectionParameters": { "entry": [{ "@key": "host", "$": "{}".format(global_settings.POSTGRESQL_HOST) }, { "@key": "port", "$": "{}".format(global_settings.POSTGRESQL_PORT) }, { "@key": "database", "$": "{}".format(Database) }, { "@key": "user", "$": "{}".format(global_settings.POSTGRESQL_USERNAME) }, { "@key": "passwd", "$": "{}".format(global_settings.POSTGRESQL_PASSWORD) }, { "@key": "dbtype", "$": "postgis" }] } } } if checkWorkspace(workspace, gs_rest_url, user, pwd, False): if verify_datastore(workspace, datastore, gs_rest_url, user, pwd, database_configuration): if checkFeature(workspace, datastore, tableName, gs_rest_url, user, pwd): r = requests.post('{0}/workspaces/{1}/datastores/{2}/featuretypes'\ .format(gs_rest_url, workspace,datastore), auth=HTTPBasicAuth(user, pwd), data=xml, headers=headers_xml ) log_logs_file("Upload Layer {}".format(r.status_code))
def extract_and_saved_excel(id_rede, id_estacao, year, information, tableCharacteristics): """ From the APA data store retrieve the excel, and saved the data in the database """ information['Id_rede'] = id_rede information['Id_estacao'] = id_estacao form_data = init_formData_APA(id_rede, id_estacao, year) response = request_Data_External_Source( 'POST', "https://qualar1.apambiente.pt/qualar/excel_new.php?excel=1", data=form_data) if response.status_code == 200: with open(PATH_EXCEL_TMP_FILE, "w") as f: f.write(response.text) Recover_corrupt_file(PATH_EXCEL_TMP_FILE) copy_excel_database('', PATH_EXCEL_TMP_FILE, DB_NAME, TABLENAME, tableCharacteristics, save_to_database, db_user, db_password, db_host, db_port, delete=False, information=information) os.remove(PATH_EXCEL_TMP_FILE) else: information['str_estacoes'] += '{}'.format( information['Id_estacao']) + ', '
def InsertValues(path_to_excel, keys, data_UI, coordenatePoints, datum_code, tableName, DB_Name, db_user, db_password, db_host, db_port, foreignData={}): """ Upload the excel file to the database """ information = { 'foreignData': foreignData, 'keys': keys, 'data_UI': data_UI, 'coordenatePoints': coordenatePoints, 'datum_code': datum_code, } return copy_excel_database('', path_to_excel, DB_Name, tableName, '', save_to_DB, db_user, db_password, db_host, db_port, delete=False, information=information, create=False, cv=True)