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)