Access and download time series from ADO discharge database

For Python we need to use SQL to query. We will use the Pandas package to manipulate the data. For more information how to create an SQL query, you can look here.

Note that the access to the database is restricted to read-only, i.e. no new tables or records can be created by the users.

# %% [markdown]
# ### Example how to access and download time series from ADO discharge database

# %%
# import required packages
import pandas as pd
import numpy as np
from sqlalchemy import create_engine

# %%
# Establish connection using information supplied in documentation
postgres_str = "postgresql+psycopg2://ado_user:hydro#ado@10.7.18.68/climate_data"  #   10.8.244.31
conn = create_engine(postgres_str)

# %%
# Choose station 
# look here: https://maps.eurac.edu/maps/new?layer=geonode:hydro_station_ado&view=True
# Adige Bronzolo: ADO_DSC_ITH1_0012

# %%
# get the metadata
station_id_master = 'ADO_DSC_ITH1_0012'
query = f"SELECT date, discharge_m3_s FROM hydrology.discharge WHERE id_station = '{station_id_master}' ORDER BY date;"

df_station = pd.read_sql_query(query, conn)
df_station.info()
df_station.head()

# %%
# Get the metadata of the MASTER STATION from table 'metadata_discharge' 
query_metadata = f"SELECT * FROM hydrology.metadata_discharge WHERE id_station = '{station_id_master}';"
df_metadata = pd.read_sql_query(query_metadata, con=conn)
df_metadata.drop(['geom', 'source_id'], inplace=True, axis=1)
df_metadata.set_index('id_station', inplace=True)
display(df_metadata)

# %%
# Save station into csv file
df_station.to_csv('file_station.csv', encoding="utf-8")  # save station to csv file

# %%
# close the connection when finished
conn.close()