Hoe maak ik zelf een databank aan voor mijn citizen science project

Introductie

In deze tutorial zullen we een postgres-database opzetten, gehost in de cloud via Heroku. We ontwerpen het databasemodel in dbdiagram.io en behandelen de import van gegevens uit een Excel-bestand met behulp van Python en SQLAlchemy.

Installatie

Enige voorbereiding is nodig!

  • Conda-omgeving

We moeten eerst een Python-omgeving opzetten in Conda. Voor deze tutorial gebruiken we Python 3.8. Pakketten kunnen via de commandline worden geïnstalleerd met behulp van:

conda install --file requirements.txt

de requirements.txt file is hier te downloaden.

Helaas biedt Heroku vanaf eind November 2022 geen gratis credits meer aan:
Nofreelunch.png
Na registratie kan je je eerste app aanmaken:
10-create-new-app.png
Kies de naam van de app en je regio, klik op "Create app":
11-app-name.png

Postgres in de cloud via Heroku

Ga naar "resources" en selecteer "Heroku Postgres".Kies voor "Hobby-Dev":
21-heroku-postgress-resources.png
Klik je net aangemaakte service aan, ga naar de "Settings" om de databank credentials te raadplegen. Je zal deze nodig hebben om te kunnen connecteren naar je databank:
22-check-credentials-Capture.png

Connectie naar je nieuwe databank

Er zijn verschillende manier om te connecteren naar de database.

Heroku CLI

Dbeaver

DBeaver is een gratis multi-platform databank tool voor ontwikkelaars, databasebeheerders, analisten en iedereen die met databases moet werken. Het ondersteunt alle populaire databases, inclusief degene die we in deze tutorial zullen gebruiken.

  • Om DBeaver te gebruiken, moet je al een versie van Postgres op je lokale machine geïnstalleerd hebben: https://www.postgresql.org/download/
  • Ga naar https://dbeaver.io/ om DBeaver te downloaden.
  • Open DBeaver nadat het geïnstalleerd is. Je kunt op het pictogram "new database connection" linksboven klikken om een verbinding te maken met de Heroku-database:
32-db-conn1.png

Onze databank bevat momenteel nog geen tabellen, maar daar zorgen we voor in de volgende stap.

Databank model

Maak je databank model aan

We zullen gebruik maken van dbdiagram.io om het databank model aan te maken. Eens ontworpen, kan deze geëxporteerd worden naar SQL code. Deze code kunnen we dan uitvoeren in dbeaver om de tabellen en alle relaties tussen de tabellen aan te maken.

In dit voorbeeld maken we 3 tabellen aan. Ze laten ons toe de meetwaarden van de sensoren op te slaan, alsook de meta-data en informatie over de locatie van de sensor.

41-database-dbdiagram.png

Eens de tabellen aangemaakt zijn in dbdiagram.io klik je op "export to PostgreSQL". Dit maakt een .sql file aan die we in de volgende stap zullen gebruiken.

42-database-dbdiagram-export.png

Maak de tabellen aan in dbeaver

Je zou de SQL-query kunnen starten met behulp van de Heroku CLI, een Python-script,... maar voor deze tutorial zullen we deze starten vanuit DBeaver.

  • Open DBeaver en maak verbinding met de database.
  • Open een nieuw script en plak hierin het .sql-bestand dat we hebben aangemaakt in dbdiagram.io.
  • De tabellen zijn nu aangemaakt. Je kan ze bekijken door op "public scheme" te klikken en vervolgens op "ER Diagram":
43-database-sql-creation.png

Migreer je datasheets naar de databank

Voorbeeld csv file

Met de databank en (lege) tabellen op hun plaats is het tijd om te kijken naar het voorbeeld Excel-bestand, te vinden hier, dat we willen importeren.

  • Er zijn 3 tabbladen in het Excel-bestand:
    • Locaties, waar wat extra informatie staat over de locaties waar de sensoren zijn geplaatst.
    • Sensoren, een oplijsting van de verschillende sensoren.
    • Metingen, een oplijsting van de gegevens die door de sensoren zijn verzameld.

De afbeeldingen hieronder tonen de dummygegevens die in elk van de tabbladen zijn ingevoerd. Deze gegevens zullen we in onze databank uploaden:

Locaties
Metingen
Sensoren

Python en SQLalchemy

We zullen Python in combinatie met het SQLalchemy-pakket gebruiken om de gegevens in de Excel-sheets naar de database over te zetten. De volgende scripts (die hier terug te vinden zijn) zijn hiervoor gemaakt:

  • excel_parser.py: een parser om de Excel-tabbladen in te lezen en te transformeren, zodanig dat ze klaar zijn om in het databank model te plaatsen.
  • orm.py: bevat de SQLalchemy Object-Relational Mapping (ORM), zodat we gemakkelijk met onze databasestructuur kunnen werken in Python.
  • push-data-db.py: start dit bestand om de gegevens te lezen, te transformeren en in de databank te plaatsen!

Opmerking: je zal de credentials in "push-data-db.py" moeten aanpassen, zodat ze matchen op jouw database gegevens.

DBASE = 'd2hto0sg05etiq'
USER = 'itwgxojqwqnqwl'
PASSWORD = ';-)'
HOST = 'ec2-34-248-169-69.eu-west-1.compute.amazonaws.com'
PORT = 5432
SCHEMA = 'public'

Nu maken we de connectie met de Heroku hosted postgres databank: de dbclass kan je vinden in dbutils.py. Het laat ons toe om vanuit python aan de slag te gaan met onze postgres databank:

 

  1. Database connection for queries
    2. 

  2. ---------------------------------
    3. 

  3. get instance of class


db = dbclass(dbase=DBASE,



           user=USER,
           password=PASSWORD,
           port=PORT,
           host=HOST)



  1. test connection


concheck = db.connected()
print(concheck)

volgende stap, het importeren van de data in de excel sheets:

sensors,measurements,locations = import_excel_data()

Vervolgens pushen we de data naar de databank:

add_locations(db, data=locations)
add_sensors(db, data=sensors)
add_measurements(db, data=measurements)

Het is belangrijk deze bovenstaande 3 lijntjes in de juiste volgorde uit te voeren, daar je eerst de locatie data nodig hebt om ernaar te kunnen refereren in de sensor tabel. Hieronder zoomen we in op de add_locations functie:

  • In sessionmaker maken we een handle om te kunnen connecteren naar de heroku gehoste database.
  • Vervolgens itereren we over elke rij in data, waar we de locatie data kunnen vinden:
...    
for index, item in data.iterrows():
...

Vervolgens checken we of de locaties al werden toegevoed in de databank (om duplicates te voorkomen):

...
exists = session.query(sqlalchemy.exists().where(orm_file.Location.name == item['locatie'])).scalar()
...

Als de data nog niet werd toegevoegd, maken we een Location instance aan via de orm_file, en hieraan voegen we de nodige variabelen toe ('locatie','beschrijving','x','y','z'):

...
location_to_add = orm_file.Location()
location_to_add.name = item['locatie']
location_to_add.description = item['beschrijving']
location_to_add.x = item['x']
location_to_add.y = item['y']
location_to_add.z = item['z']
...

Tot slot commiten we deze data naar de databank, en sluiten we de sessie nadat we over elke rij in data geloopt hebben:

...
session.add(location_to_add)
session.commit()
...
session.close()

De volledige code van add_locations:

def add_locations(db,data):



   """



   :return:
   """
   Session = sessionmaker(bind=db.sqla_engine)
   session = Session()
   # loop over data to add and check if data is already in the db. If not ==> add
   for index, item in data.iterrows():
       exists = session.query(sqlalchemy.exists().where(orm_file.Location.name == item['locatie'])).scalar()
       if not exists:
           print('table: "Location" with column: "name" and value: {value} does not exist,  adding data'.format(
               value=item['locatie']))



           location_to_add = orm_file.Location()
           location_to_add.name = item['locatie']
           location_to_add.description = item['beschrijving']
           location_to_add.x = item['x']
           location_to_add.y = item['y']
           location_to_add.z = item['z']
           print(location_to_add)
           session.add(location_to_add)
           session.commit()
       else:
           print(
               'table: "Location" with column: "name" and value: {value}  already existed, skip adding of data'.format(
                   value=item['locatie']))
   session.close()

Raadpleeg je data in de databank

Je kan nu in dbeaver verifiëren dat de data toegevoegd is aan de heroku-hosted postgres databank!

Overgenomen van "https://vloca-kennishub.vlaanderen.be/index.php?title=Draaiboek:Hoe_maak_ik_zelf_een_databank_aan_voor_mijn_citizen_science_project%3F&oldid=10500"