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.
- Registreer je hier voor een Heroku-account: https://signup.heroku.com/
| Helaas biedt Heroku vanaf eind November 2022 geen gratis credits meer aan: |
| Na registratie kan je je eerste app aanmaken: |
| Kies de naam van de app en je regio, klik op "Create app": |
Postgres in de cloud via Heroku
| Ga naar "resources" en selecteer "Heroku Postgres".Kies voor "Hobby-Dev": |
| 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: |
Connectie naar je nieuwe databank
Er zijn verschillende manier om te connecteren naar de database.
Heroku CLI
- Ga naar https://devcenter.heroku.com/articles/heroku-cli en volg daar het stappenplan om de Heroku CLI op te zetten in je commandline terminal.
- Voor deze tutorial zullen we echter dbeaver gebruiken, dat iets gebruiksvriendelijker is.
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:
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.
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.
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.
- Voer dit script uit (ook hier te downloaden: 10-create-tables.sql)
- De tabellen zijn nu aangemaakt. Je kan ze bekijken door op "public scheme" te klikken en vervolgens op "ER Diagram":
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:
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:
# Database connection for queries
#---------------------------------
# get instance of class
db = dbclass(dbase=DBASE,
user=USER,
password=PASSWORD,
port=PORT,
host=HOST)
# 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
sessionmakermaken 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!