k (1 revision imported) |
AWS-Import>MaartenVanLoo |
||
Regel 1: | Regel 1: | ||
− | |||
− | == Introductie == | + | =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. | 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== | |
− | == Installatie == | ||
Enige voorbereiding is nodig! | 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: | ||
− | |||
− | |||
− | |||
<code>conda install --file requirements.txt </code> | <code>conda install --file requirements.txt </code> | ||
de <code>requirements.txt</code> file is [https://github.com/MaartenVL/heroku-postgres-flow/blob/main/requirements.txt hier] te downloaden. | de <code>requirements.txt</code> file is [https://github.com/MaartenVL/heroku-postgres-flow/blob/main/requirements.txt hier] te downloaden. | ||
− | + | *Registreer je hier voor een Heroku-account: https://signup.heroku.com/ | |
− | * Registreer je hier voor een Heroku-account: https://signup.heroku.com/ | ||
− | |||
{| style="width: 100%;" | {| style="width: 100%;" | ||
|- | |- | ||
|Helaas biedt Heroku vanaf eind November 2022 geen gratis credits meer aan: | |Helaas biedt Heroku vanaf eind November 2022 geen gratis credits meer aan: | ||
|- | |- | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" |[[File:Nofreelunch.png|thumb|left|650px]] |
− | [[File:Nofreelunch.png|thumb|left|650px]] | ||
|- | |- | ||
|Na registratie kan je je eerste app aanmaken: | |Na registratie kan je je eerste app aanmaken: | ||
|- | |- | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" |[[File:10-create-new-app.png|thumb|left|650px]] |
− | [[File:10-create-new-app.png|thumb|left|650px]] | ||
|- | |- | ||
|Kies de naam van de app en je regio, klik op "Create app": | |Kies de naam van de app en je regio, klik op "Create app": | ||
|- | |- | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" |[[File:11-app-name.png|thumb|left|650px]] |
− | [[File:11-app-name.png|thumb|left|650px]] | ||
|} | |} | ||
− | + | ==Postgres in de cloud via Heroku== | |
− | == Postgres in de cloud via Heroku | ||
{| style="width: 100%;" | {| style="width: 100%;" | ||
|- | |- | ||
|Ga naar "resources" en selecteer "Heroku Postgres".Kies voor "Hobby-Dev": | |Ga naar "resources" en selecteer "Heroku Postgres".Kies voor "Hobby-Dev": | ||
|- | |- | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" |[[File:21-heroku-postgress-resources.png|thumb|left|750px]] |
− | [[File:21-heroku-postgress-resources.png|thumb|left|750px]] | ||
|- | |- | ||
|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: | |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: | ||
|- | |- | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" |[[File:22-check-credentials-Capture.png|thumb|left|1050px]] |
− | [[File:22-check-credentials-Capture.png|thumb|left|1050px]] | ||
|- | |- | ||
|} | |} | ||
− | + | ==Connectie naar je nieuwe databank== | |
− | == Connectie naar je nieuwe databank == | ||
Er zijn verschillende manier om te connecteren naar de database. | Er zijn verschillende manier om te connecteren naar de database. | ||
− | + | ===Heroku CLI=== | |
− | === 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. |
− | * 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. |
− | * Voor deze tutorial zullen we echter dbeaver gebruiken, dat iets gebruiksvriendelijker is. | + | ===Dbeaver=== |
− | |||
− | === 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. | 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/ | |
− | * 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. |
− | * 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: |
− | * 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: | ||
− | |||
{| style="width: 100%;" | {| style="width: 100%;" | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" |[[File:32-db-conn1.png|thumb|left|350px]] |
− | [[File:32-db-conn1.png|thumb|left|350px]] | ||
|- | |- | ||
− | |} | + | |}Onze databank bevat momenteel nog geen tabellen, maar daar zorgen we voor in de volgende stap. |
− | + | ==Databank model== | |
− | Onze databank bevat momenteel nog geen tabellen, maar daar zorgen we voor in de volgende stap. | + | ===Maak je databank model aan=== |
− | |||
− | == Databank model == | ||
− | |||
− | === Maak je databank model aan === | ||
− | |||
We zullen gebruik maken van [https://dbdiagram.io/home 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. | We zullen gebruik maken van [https://dbdiagram.io/home 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. | 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. | ||
− | |||
{| style="width: 100%;" | {| style="width: 100%;" | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" |[[File:41-database-dbdiagram.png|thumb|left|850px]] |
− | [[File:41-database-dbdiagram.png|thumb|left|850px]] | ||
|- | |- | ||
− | |} | + | |}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. |
− | |||
− | 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. | ||
− | |||
{| style="width: 100%;" | {| style="width: 100%;" | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" |[[File:42-database-dbdiagram-export.png|thumb|left|350px]] |
− | [[File:42-database-dbdiagram-export.png|thumb|left|350px]] | ||
|- | |- | ||
|} | |} | ||
− | + | ===Maak de tabellen aan in dbeaver=== | |
− | === 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. | 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. | |
− | |||
− | * Open een nieuw script en plak hierin het .sql-bestand dat we hebben aangemaakt in dbdiagram.io. | ||
− | |||
− | |||
− | * | + | *Voer dit script uit (ook [https://github.com/MaartenVL/heroku-postgres-flow/tree/main/src hier] te downloaden: [https://github.com/MaartenVL/heroku-postgres-flow/blob/main/src/10-create-tables.sql 10-create-tables.sql]) |
+ | *De tabellen zijn nu aangemaakt. Je kan ze bekijken door op "public scheme" te klikken en vervolgens op "ER Diagram": | ||
{| style="width: 100%;" | {| style="width: 100%;" | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" |[[File:43-database-sql-creation.png|thumb|left|650px]] |
− | [[File:43-database-sql-creation.png|thumb|left|650px]] | ||
|- | |- | ||
|} | |} | ||
− | + | ==Migreer je datasheets naar de databank== | |
− | == Migreer je datasheets naar de databank == | + | ===Voorbeeld csv file=== |
− | === 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 [https://github.com/MaartenVL/heroku-postgres-flow/tree/main/data hier], dat we willen importeren. | Met de databank en (lege) tabellen op hun plaats is het tijd om te kijken naar het voorbeeld Excel-bestand, te vinden [https://github.com/MaartenVL/heroku-postgres-flow/tree/main/data hier], dat we willen importeren. | ||
− | + | *Er zijn 3 tabbladen in het Excel-bestand: | |
− | * Er zijn 3 tabbladen in het Excel-bestand: | + | **Locaties, waar wat extra informatie staat over de locaties waar de sensoren zijn geplaatst. |
− | ** Locaties, waar wat extra informatie staat over de locaties waar de sensoren zijn geplaatst. | + | **Sensoren, een oplijsting van de verschillende sensoren. |
− | ** Sensoren, een oplijsting van de verschillende sensoren. | + | **Metingen, een oplijsting van de gegevens die door de sensoren zijn verzameld. |
− | ** 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:[[File:51-fill-data-2.png|thumb|Locaties|left|350px]][[File:51-fill-data-3.png|thumb|Metingen|left|350px]] |
− | |||
− | De afbeeldingen hieronder tonen de dummygegevens die in elk van de tabbladen zijn ingevoerd. Deze gegevens zullen we in onze databank uploaden: | ||
− | |||
{| style="width: 100%;" | {| style="width: 100%;" | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" |[[File:51-fill-data-1.png|thumb|Sensoren|left|350px]] |
− | [[File:51-fill-data-1.png|thumb|Sensoren|left|350px]] | ||
|- | |- | ||
− | |||
|- | |- | ||
− | |||
|- | |- | ||
|} | |} | ||
− | + | ===Python en SQLalchemy=== | |
− | === 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 [https://github.com/MaartenVL/heroku-postgres-flow/tree/main/src hier] terug te vinden zijn) zijn hiervoor gemaakt: | 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 [https://github.com/MaartenVL/heroku-postgres-flow/tree/main/src 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. | |
− | * '''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. |
− | * '''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! |
− | * '''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.<pre style="color: black"> |
− | |||
− | Opmerking: je zal de credentials in "push-data-db.py" moeten aanpassen, zodat ze matchen op jouw database gegevens. | ||
− | |||
− | <pre style="color: black"> | ||
DBASE = 'd2hto0sg05etiq' | DBASE = 'd2hto0sg05etiq' | ||
USER = 'itwgxojqwqnqwl' | USER = 'itwgxojqwqnqwl' | ||
Regel 148: | Regel 104: | ||
PORT = 5432 | PORT = 5432 | ||
SCHEMA = 'public' | SCHEMA = 'public' | ||
− | </pre> | + | </pre>Nu maken we de connectie met de Heroku hosted postgres databank: de <code>dbclass</code> kan je vinden in <code>dbutils.py</code>. Het laat ons toe om vanuit python aan de slag te gaan met onze postgres databank:<pre> |
− | |||
− | Nu maken we de connectie met de Heroku hosted postgres databank: de <code>dbclass</code> kan je vinden in <code>dbutils.py</code>. Het laat ons toe om vanuit python aan de slag te gaan met onze postgres databank: | ||
− | |||
− | <pre> | ||
# Database connection for queries | # Database connection for queries | ||
#--------------------------------- | #--------------------------------- | ||
Regel 165: | Regel 117: | ||
concheck = db.connected() | concheck = db.connected() | ||
print(concheck) | print(concheck) | ||
− | </pre> | + | </pre>volgende stap, het importeren van de data in de excel sheets:<pre> |
− | |||
− | volgende stap, het importeren van de data in de excel sheets: | ||
− | |||
− | <pre> | ||
sensors,measurements,locations = import_excel_data() | sensors,measurements,locations = import_excel_data() | ||
− | </pre> | + | </pre>Vervolgens pushen we de data naar de databank:<pre> |
− | |||
− | Vervolgens pushen we de data naar de databank: | ||
− | |||
− | <pre> | ||
add_locations(db, data=locations) | add_locations(db, data=locations) | ||
add_sensors(db, data=sensors) | add_sensors(db, data=sensors) | ||
add_measurements(db, data=measurements) | add_measurements(db, data=measurements) | ||
− | </pre> | + | </pre>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 <code>add_locations</code> functie: |
− | + | *In <code>sessionmaker</code> maken we een handle om te kunnen connecteren naar de heroku gehoste database. | |
− | 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. | + | *Vervolgens itereren we over elke rij in <code>data</code>, waar we de locatie data kunnen vinden: |
− | Hieronder zoomen we in op de <code>add_locations</code> functie: | ||
− | |||
− | * In <code>sessionmaker</code> maken we een handle om te kunnen connecteren naar de heroku gehoste database. | ||
− | * Vervolgens itereren we over elke rij in <code>data</code>, waar we de locatie data kunnen vinden: | ||
− | |||
<pre> | <pre> | ||
... | ... | ||
for index, item in data.iterrows(): | for index, item in data.iterrows(): | ||
... | ... | ||
− | </pre> | + | </pre>Vervolgens checken we of de locaties al werden toegevoed in de databank (om duplicates te voorkomen):<pre> |
− | |||
− | Vervolgens checken we of de locaties al werden toegevoed in de databank (om duplicates te voorkomen): | ||
− | |||
− | <pre> | ||
... | ... | ||
exists = session.query(sqlalchemy.exists().where(orm_file.Location.name == item['locatie'])).scalar() | exists = session.query(sqlalchemy.exists().where(orm_file.Location.name == item['locatie'])).scalar() | ||
... | ... | ||
− | </pre> | + | </pre>Als de data nog niet werd toegevoegd, maken we een <code>Location</code> instance aan via de <code>orm_file</code>, en hieraan voegen we de nodige variabelen toe ('locatie','beschrijving','x','y','z'):<pre> |
− | |||
− | Als de data nog niet werd toegevoegd, maken we een <code>Location</code> instance aan via de <code>orm_file</code>, en hieraan voegen we de nodige variabelen toe ('locatie','beschrijving','x','y','z'): | ||
− | |||
− | <pre> | ||
... | ... | ||
location_to_add = orm_file.Location() | location_to_add = orm_file.Location() | ||
Regel 212: | Regel 143: | ||
location_to_add.z = item['z'] | location_to_add.z = item['z'] | ||
... | ... | ||
− | </pre> | + | </pre>Tot slot commiten we deze data naar de databank, en sluiten we de sessie nadat we over elke rij in <code>data</code> geloopt hebben:<pre> |
− | |||
− | Tot slot commiten we deze data naar de databank, en sluiten we de sessie nadat we over elke rij in <code>data</code> geloopt hebben: | ||
− | |||
− | <pre> | ||
... | ... | ||
session.add(location_to_add) | session.add(location_to_add) | ||
Regel 222: | Regel 149: | ||
... | ... | ||
session.close() | session.close() | ||
− | </pre> | + | </pre>De volledige code van <code>add_locations</code>:<pre> |
− | |||
− | De volledige code van <code>add_locations</code>: | ||
− | |||
− | <pre> | ||
def add_locations(db,data): | def add_locations(db,data): | ||
""" | """ | ||
Regel 256: | Regel 179: | ||
session.close() | session.close() | ||
</pre> | </pre> | ||
− | + | ===Raadpleeg je data in de databank=== | |
− | === Raadpleeg je data in de databank === | ||
Je kan nu in dbeaver verifiëren dat de data toegevoegd is aan de heroku-hosted postgres databank! | Je kan nu in dbeaver verifiëren dat de data toegevoegd is aan de heroku-hosted postgres databank! | ||
ws-base-props | |||
---|---|---|---|
Regel 1: | Regel 1: | ||
− | + | {{Base properties | |
+ | |Title=Hoe maak ik zelf een databank aan voor mijn citizen science project? | ||
+ | |Class=Draaiboek | ||
+ | }} |
Versie van 6 dec 2023 19:06
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
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!