Skip to content

Commit 8a79094

Browse files
jh-RLIjh-RLI
authored
Merge pull request #2043 from OpenEnergyPlatform/feature-2040-add-example-dataset-artifacts-to-docker-dev-setup
Extend setup for database seeding in dev setup
2 parents 867d914 + 2c15c35 commit 8a79094

File tree

6 files changed

+496
-48
lines changed

6 files changed

+496
-48
lines changed

‎api/actions.py

Lines changed: 17 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -1,21 +1,21 @@
1-
# SPDX-FileCopyrightText: 2025 Pierre Francois <https://github.com/Bachibouzouk> © Reiner Lemoine Institut
2-
# SPDX-FileCopyrightText: 2025 Christian Winger <https://github.com/wingechr> © Öko-Institut e.V.
1+
# SPDX-FileCopyrightText: 2025 Pierre Francois <https://github.com/Bachibouzouk> © Reiner Lemoine Institut # noqa: E501
2+
# SPDX-FileCopyrightText: 2025 Christian Winger <https://github.com/wingechr> © Öko-Institut e.V. # noqa: E501
33
# SPDX-FileCopyrightText: 2025 Eike Broda <https://github.com/ebroda>
4-
# SPDX-FileCopyrightText: 2025 Johann Wagner <https://github.com/johannwagner> © Otto-von-Guericke-Universität Magdeburg
5-
# SPDX-FileCopyrightText: 2025 Jonas Huber <https://github.com/jh-RLI> © Reiner Lemoine Institut
6-
# SPDX-FileCopyrightText: 2025 Jonas Huber <https://github.com/jh-RLI> © Reiner Lemoine Institut
7-
# SPDX-FileCopyrightText: 2025 Martin Glauer <https://github.com/MGlauer> © Otto-von-Guericke-Universität Magdeburg
8-
# SPDX-FileCopyrightText: 2025 Martin Glauer <https://github.com/MGlauer> © Otto-von-Guericke-Universität Magdeburg
9-
# SPDX-FileCopyrightText: 2025 Martin Glauer <https://github.com/MGlauer> © Otto-von-Guericke-Universität Magdeburg
10-
# SPDX-FileCopyrightText: 2025 Tom Heimbrodt <https://github.com/tom-heimbrodt>
11-
# SPDX-FileCopyrightText: 2025 Christian Winger <https://github.com/wingechr> © Öko-Institut e.V.
12-
# SPDX-FileCopyrightText: 2025 Christian Hofmann <https://github.com/christian-rli> © Reiner Lemoine Institut
13-
# SPDX-FileCopyrightText: 2025 chrwm <https://github.com/chrwm> © Reiner Lemoine Institut
14-
# SPDX-FileCopyrightText: 2025 henhuy <https://github.com/henhuy> © Reiner Lemoine Institut
15-
# SPDX-FileCopyrightText: 2025 Jonas Huber <https://github.com/jh-RLI> © Reiner Lemoine Institut
16-
# SPDX-FileCopyrightText: 2025 Jonas Huber <https://github.com/jh-RLI> © Reiner Lemoine Institut
17-
# SPDX-FileCopyrightText: 2025 user <https://github.com/Darynarli> © Reiner Lemoine Institut
18-
# SPDX-FileCopyrightText: 2025 Christian Winger <https://github.com/wingechr> © Öko-Institut e.V.
4+
# SPDX-FileCopyrightText: 2025 Johann Wagner <https://github.com/johannwagner> © Otto-von-Guericke-Universität Magdeburg # noqa: E501
5+
# SPDX-FileCopyrightText: 2025 Jonas Huber <https://github.com/jh-RLI> © Reiner Lemoine Institut # noqa: E501
6+
# SPDX-FileCopyrightText: 2025 Jonas Huber <https://github.com/jh-RLI> © Reiner Lemoine Institut # noqa: E501
7+
# SPDX-FileCopyrightText: 2025 Martin Glauer <https://github.com/MGlauer> © Otto-von-Guericke-Universität Magdeburg # noqa: E501
8+
# SPDX-FileCopyrightText: 2025 Martin Glauer <https://github.com/MGlauer> © Otto-von-Guericke-Universität Magdeburg # noqa: E501
9+
# SPDX-FileCopyrightText: 2025 Martin Glauer <https://github.com/MGlauer> © Otto-von-Guericke-Universität Magdeburg # noqa: E501
10+
# SPDX-FileCopyrightText: 2025 Tom Heimbrodt <https://github.com/tom-heimbrodt> # noqa: E501
11+
# SPDX-FileCopyrightText: 2025 Christian Winger <https://github.com/wingechr> © Öko-Institut e.V. # noqa: E501
12+
# SPDX-FileCopyrightText: 2025 Christian Hofmann <https://github.com/christian-rli> © Reiner Lemoine Institut # noqa: E501
13+
# SPDX-FileCopyrightText: 2025 chrwm <https://github.com/chrwm> © Reiner Lemoine Institut # noqa: E501
14+
# SPDX-FileCopyrightText: 2025 henhuy <https://github.com/henhuy> © Reiner Lemoine Institut # noqa: E501
15+
# SPDX-FileCopyrightText: 2025 Jonas Huber <https://github.com/jh-RLI> © Reiner Lemoine Institut # noqa: E501
16+
# SPDX-FileCopyrightText: 2025 Jonas Huber <https://github.com/jh-RLI> © Reiner Lemoine Institut # noqa: E501
17+
# SPDX-FileCopyrightText: 2025 user <https://github.com/Darynarli> © Reiner Lemoine Institut # noqa: E501
18+
# SPDX-FileCopyrightText: 2025 Christian Winger <https://github.com/wingechr> © Öko-Institut e.V. # noqa: E501
1919
#
2020
# SPDX-License-Identifier: AGPL-3.0-or-later
2121

@@ -2574,23 +2574,6 @@ def set_table_metadata(table, schema, metadata, cursor=None):
25742574
readable_table_name=readable_table_name,
25752575
)
25762576

2577-
# ---------------------------------------
2578-
# update the table comment in oedb table if sqlalchemy curser is provided
2579-
# ---------------------------------------
2580-
2581-
# # TODO: The following 2 lines seems to duplicate with the lines below the if block
2582-
# oedb_table_obj = _get_table(schema=schema, table=table)
2583-
# oedb_table_obj.comment = metadata_str
2584-
# if cursor is not None:
2585-
# # Surprisingly, SQLAlchemy does not seem to escape comment strings
2586-
# # properly. Certain strings cause errors database errors.
2587-
# # This MAY be a security issue. Therefore, we do not use
2588-
# # SQLAlchemy's compiler here but do it manually.
2589-
# sql = "COMMENT ON TABLE {schema}.{table} IS %s".format(
2590-
# schema=oedb_table_obj.schema, table=oedb_table_obj.name
2591-
# )
2592-
# cursor.execute(sql, (metadata_str,))
2593-
25942577
# ---------------------------------------
25952578
# update search index
25962579
# ---------------------------------------

‎dataedit/management/commands/create_example_tables.py

Lines changed: 177 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1,38 +1,80 @@
1-
# myapp/management/commands/setup_tables.py
1+
import json
2+
from csv import DictReader
3+
from pathlib import Path
24

35
from django.contrib.auth import get_user_model
46
from django.core.management.base import BaseCommand
7+
from sqlalchemy import MetaData
8+
from sqlalchemy.dialects.postgresql import insert as pg_insert
9+
from sqlalchemy.exc import SQLAlchemyError
10+
from sqlalchemy.orm import sessionmaker
511

12+
from api.actions import (
13+
_get_engine,
14+
set_table_metadata,
15+
try_convert_metadata_to_v2,
16+
try_parse_metadata,
17+
try_validate_metadata,
18+
)
619
from api.services.permissions import assign_table_holder
720
from api.services.table_creation import TableCreationOrchestrator
21+
from dataedit.views import get_tag_keywords_synchronized_metadata
822

923
User = get_user_model()
1024

11-
# Define your table specs here:
12-
# Note: these can keep `"type"` for readability; we remap it below.
25+
1326
TABLE_DEFS = [
1427
{
1528
"schema": "model_draft",
16-
"table": "my_first_table",
29+
"table": "example_wind_farm_capacity",
1730
"columns": [
1831
{
1932
"name": "id",
20-
"type": "integer",
33+
"type": "bigserial",
2134
"options": {"primary_key": True, "nullable": False},
2235
},
36+
{
37+
"name": "technology",
38+
"type": "text",
39+
"options": {"nullable": True},
40+
},
41+
{
42+
"name": "type",
43+
"type": "text",
44+
"options": {"nullable": True},
45+
},
46+
{
47+
"name": "year",
48+
"type": "integer",
49+
"options": {"nullable": True},
50+
},
51+
{
52+
"name": "date",
53+
"type": "date",
54+
"options": {"nullable": True},
55+
},
2356
{
2457
"name": "value",
58+
"type": "numeric",
59+
"options": {"nullable": True},
60+
},
61+
{
62+
"name": "comment",
2563
"type": "text",
26-
"options": {"nullable": True, "default": ""},
64+
"options": {"nullable": True},
65+
},
66+
{
67+
"name": "geometry",
68+
"type": "geometry",
69+
"options": {"nullable": True},
2770
},
2871
],
29-
"constraints": [
30-
# e.g. { "constraint_type": "unique", "columns": ["value"], "name": "uq_value" } # noqa
31-
],
32-
},
33-
# … more specs …
72+
"constraints": [],
73+
}
3474
]
3575

76+
CSV_FILE = "dataedit/management/data/example_wind_farm_capacity.csv"
77+
3678

3779
class Command(BaseCommand):
3880
help = "Seed DataEdit tables + actual DB tables as in the Tables API"
@@ -43,6 +85,7 @@ def handle(self, *args, **opts):
4385
name="test", defaults={"email": "[email protected]", "is_staff": True}
4486
)
4587

88+
print("Hello world")
4689
orchestrator = TableCreationOrchestrator()
4790

4891
for spec in TABLE_DEFS:
@@ -88,4 +131,126 @@ def handle(self, *args, **opts):
88131
)
89132

90133
except Exception as e:
91-
self.style.ERROR(f"✘ Failed to create {schema_name}.{table_name}: {e}")
134+
self.stderr.write(
135+
self.style.ERROR(
136+
f"✘ Failed to create {schema_name}.{table_name}: {e}"
137+
)
138+
)
139+
140+
try:
141+
# Seed the table with data from CSV
142+
self._seed_data(schema_name, table_name, CSV_FILE)
143+
144+
except Exception as e:
145+
self.stderr.write(
146+
self.style.ERROR(
147+
f"✘ Failed to seed {schema_name}.{table_name} with data: {e}"
148+
)
149+
)
150+
151+
try:
152+
# Set metadata for the table
153+
metadata_file = "dataedit/management/data/datapackage.json"
154+
self._set_metadata(schema_name, table_name, metadata_file)
155+
except Exception as e:
156+
self.stderr.write(
157+
self.style.ERROR(
158+
f"✘ Failed to set metadata for {schema_name}.{table_name}: {e}"
159+
)
160+
)
161+
162+
def _seed_data(self, schema, table_name, csv_file):
163+
engine = _get_engine()
164+
Session = sessionmaker(bind=engine)
165+
session = Session()
166+
metadata = MetaData(schema=schema)
167+
metadata.reflect(bind=engine, schema=schema)
168+
169+
full_table_name = f"{schema}.{table_name}"
170+
table = metadata.tables.get(full_table_name)
171+
172+
print(table)
173+
174+
if table is None:
175+
self.stderr.write(
176+
self.style.ERROR(
177+
f"Table '{full_table_name}' not found in reflected metadata."
178+
)
179+
)
180+
return
181+
182+
with open(csv_file, newline="", encoding="utf-8") as f:
183+
reader = DictReader(f)
184+
rows = []
185+
186+
for row in reader:
187+
cleaned = {k: (v if v != "" else None) for k, v in row.items()}
188+
rows.append(cleaned)
189+
190+
if not rows:
191+
self.stdout.write(
192+
self.style.WARNING(f"No rows to insert into {full_table_name}.")
193+
)
194+
return
195+
196+
self.stdout.write(
197+
f"Preparing to insert {len(rows)} rows into {full_table_name}"
198+
)
199+
self.stdout.write(f"First row: {rows[0]}" if rows else "No rows parsed.")
200+
201+
try:
202+
stmt = pg_insert(table).values(rows)
203+
stmt = stmt.on_conflict_do_nothing(index_elements=["id"])
204+
session.execute(stmt)
205+
session.commit()
206+
self.stdout.write(
207+
self.style.SUCCESS(
208+
f"✔ Inserted {len(rows)} rows into {full_table_name}"
209+
)
210+
)
211+
except SQLAlchemyError as e:
212+
session.rollback()
213+
self.stderr.write(self.style.ERROR(f"SQLAlchemy insert error: {e}"))
214+
except Exception as e:
215+
session.rollback()
216+
self.stderr.write(self.style.ERROR(f"General insert error: {e}"))
217+
finally:
218+
session.close()
219+
220+
def _set_metadata(self, schema, table_name, metadata_file):
221+
metadata_path = Path(metadata_file)
222+
metadata: dict = {}
223+
224+
if not metadata_path.exists():
225+
self.stderr.write(
226+
self.style.ERROR(f"Metadata file '{metadata_file}' not found.")
227+
)
228+
return
229+
230+
with open(metadata_path, encoding="utf-8") as f:
231+
raw_metadata = json.load(f)
232+
233+
metadata, error = try_parse_metadata(raw_metadata)
234+
if error:
235+
raise Exception(f"Metadata parse error: {error}")
236+
237+
metadata = try_convert_metadata_to_v2(metadata)
238+
metadata, error = try_validate_metadata(metadata)
239+
if error:
240+
raise Exception(f"Metadata validation error: {error}")
241+
242+
# Sync keywords with tag system
243+
keywords = metadata["resources"][0].get("keywords", []) or []
244+
synced = get_tag_keywords_synchronized_metadata(
245+
table=table_name, schema=schema, keywords_new=keywords
246+
)
247+
metadata["resources"][0]["keywords"] = synced["resources"][0]["keywords"]
248+
249+
# Save to Django's oemetadata JSONB field and comment
250+
set_table_metadata(table=table_name, schema=schema, metadata=metadata)
251+
252+
self.stdout.write(
253+
self.style.SUCCESS(
254+
f"✔ Metadata saved and tags synced for {schema}.{table_name}"
255+
)
256+
)

0 commit comments

Comments
 (0)