Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DSS Import: Handling of IntegrityError: "not present in table" #118

Closed
urskaufmann opened this issue Apr 14, 2023 · 5 comments
Closed

DSS Import: Handling of IntegrityError: "not present in table" #118

urskaufmann opened this issue Apr 14, 2023 · 5 comments
Assignees
Labels
bug Something isn't working

Comments

@urskaufmann
Copy link

Describe the bug
Get this error when importing dss_2015_lv95 - data. But the mentioned key ch21ha8pME000029 in table maintenance_event exists.
Propably there is a wrong order when importing the data (re_maintenance_event_wastewater_structure before maintenance_event)?

Ein Fehler trat bei der Ausführung von Python-Code auf:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "re_maintenance_event_wastewater_structure" violates foreign key constraint "rel_maintenance_event_wastewater_structure_maintenance_event" DETAIL: Key (fk_maintenance_event)=(ch21ha8pME000029) is not present in table "maintenance_event". [SQL: INSERT INTO qgep_od.re_maintenance_event_wastewater_structure (fk_wastewater_structure, fk_maintenance_event) VALUES (%(fk_wastewater_structure)s, %(fk_maintenance_event)s) RETURNING qgep_od.re_maintenance_event_wastewater_structure.obj_id] [parameters: {'fk_wastewater_structure': 'A001gni5bhr3714o', 'fk_maintenance_event': 'ch21ha8pME000029'}] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Traceback (most recent call last):
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 608, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.ForeignKeyViolation: insert or update on table "re_maintenance_event_wastewater_structure" violates foreign key constraint "rel_maintenance_event_wastewater_structure_maintenance_event"
DETAIL: Key (fk_maintenance_event)=(ch21ha8pME000029) is not present in table "maintenance_event".

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\dss_ie/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui\gui_import.py", line 204, in commit_session
self.session.commit()
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 1046, in commit
self.transaction.commit()
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 504, in commit
self.prepare_impl()
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 483, in _prepare_impl
self.session.flush()
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 2540, in flush
self.flush(objects)
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 2682, in _flush
transaction.rollback(_capture_exception=True)
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in exit
compat.raise
(
File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise

raise exception
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 2642, in _flush
flush_context.execute()
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 419, in execute
n.execute_aggregate(self, set_)
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 521, in execute_aggregate
self.execute(uow)
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 586, in execute
persistence.save_obj(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\persistence.py", line 239, in save_obj
_emit_insert_statements(
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\persistence.py", line 1135, in _emit_insert_statements
result = cached_connections[connection].execute(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1011, in execute
return meth(self, multiparams, params)
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1124, in _execute_clauseelement
ret = self._execute_context(
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1316, in _execute_context
self.handle_dbapi_exception(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in handle_dbapi_exception
util.raise
(
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise

raise exception
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 608, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "re_maintenance_event_wastewater_structure" violates foreign key constraint "rel_maintenance_event_wastewater_structure_maintenance_event"
DETAIL: Key (fk_maintenance_event)=(ch21ha8pME000029) is not present in table "maintenance_event".

[SQL: INSERT INTO qgep_od.re_maintenance_event_wastewater_structure (fk_wastewater_structure, fk_maintenance_event) VALUES (%(fk_wastewater_structure)s, %(fk_maintenance_event)s) RETURNING qgep_od.re_maintenance_event_wastewater_structure.obj_id]
[parameters: {'fk_wastewater_structure': 'A001gni5bhr3714o', 'fk_maintenance_event': 'ch21ha8pME000029'}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

Python-Version: 3.9.5 (tags/v3.9.5:0a7dcbd, May 3 2021, 17:27:52) [MSC v.1928 64 bit (AMD64)]
QGIS-Version: 3.28.5-Firenze Firenze, 50adba36f2

Python-Pfad:
C:/PROGRA1/QGIS3/apps/qgis-ltr/./python
C:/Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\dss_ie/python
C:/Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\dss_ie/python/plugins
C:/PROGRA
1/QGIS3/apps/qgis-ltr/./python/plugins
C:\PROGRA1\QGIS3\apps\grass\grass82\etc\python
C:\Windows\system32
C:\Program Files\QGIS3\bin\python39.zip
C:\PROGRA
1\QGIS3\apps\Python39\DLLs
C:\PROGRA1\QGIS3\apps\Python39\lib
C:\Program Files\QGIS3\bin
C:\PROGRA
1\QGIS3\apps\Python39
C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages
C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\win32
C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\win32\lib
C:\PROGRA
1\QGIS3\apps\Python39\lib\site-packages\Pythonwin
C:/Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\dss_ie/python
//holingerag.intra/DEM/DEMProfiles/KAU/Desktop/test_dss_ie
C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\dss_ie/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui....
.
To Reproduce
Exact steps to reproduce the behavior:
Import DSS-data with maintenance_events and re_maintenace_event_wastewater_structure data

Expected behavior
no error

Screenshots / data

Desktop (please complete the following information):

  • QGEP plugin version 20230411 test vsa-dss
  • QGEP datamodel version 1.6.0
  • QGEP project file version 1.6.0
  • QGIS Version 3.28.5
  • OS WIN 10

Additional context
if I delete the re_maintenance_event_wastewater_structure records, I get a similar error:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "throttle_shut_off_unit" violates foreign key constraint "rel_throttle_shut_off_unit_wastewater_node" DETAIL: Key (fk_wastewater_node)=(A001gni5btl9vhqi) is not present in table "wastewater_node". [SQL: INSERT INTO qgep_od.throttle_shut_off_unit (obj_id, actuation, adjustability, control, cross_section, effective_cross_section, gross_costs, identifier, kind, manufacturer, remark, signal_transmission, subsidies, throttle_unit_opening_current, throttle_unit_opening_current_optimized, last_modification, fk_dataowner, fk_provider, fk_wastewater_node, fk_control_center, fk_overflow) VALUES (%(obj_id)s, %(actuation)s, %(adjustability)s, %(control)s, %(cross_section)s, %(effective_cross_section)s, %(gross_costs)s, %(identifier)s, %(kind)s, %(manufacturer)s, %(remark)s, %(signal_transmission)s, %(subsidies)s, %(throttle_unit_opening_current)s, %(throttle_unit_opening_current_optimized)s, %(last_modification)s, %(fk_dataowner)s, %(fk_provider)s, %(fk_wastewater_node)s, %(fk_control_center)s, %(fk_overflow)s)] [parameters: ({'obj_id': 'ch21ha8pTS000000', 'actuation': 3157, 'adjustability': 3160, 'control': 3165, 'cross_section': None, 'effective_cross_section': None, 'gross_costs': None, 'identifier': '330 Drosselschieber', 'kind': 135, 'manufacturer': 'DN 300, Sohle 90cm unterhalb "Leapingwehr"', 'remark': None, 'signal_transmission': None, 'subsidies': None, 'throttle_unit_opening_current': 115, 'throttle_unit_opening_current_optimized': None, 'last_modification': datetime.date(2021, 11, 18), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_node': 'A001gni5btl9vhqi', 'fk_control_center': None, 'fk_overflow': None}, {'obj_id': 'ch21ha8pTS000001', 'actuation': 3157, 'adjustability': 3160, 'control': 3165, 'cross_section': None, 'effective_cross_section': None, 'gross_costs': None, 'identifier': '700 Drosselschieber', 'kind': 135, 'manufacturer': 'DN 250', 'remark': None, 'signal_transmission': None, 'subsidies': None, 'throttle_unit_opening_current': 125, 'throttle_unit_opening_current_optimized': None, 'last_modification': datetime.date(2021, 11, 18), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_node': 'ch21ha8pWN000006', 'fk_control_center': None, 'fk_overflow': None})] (Background on this error at: http://sqlalche.me/e/13/gkpj)

@urskaufmann
Copy link
Author

This error was not a problem of dss-import. The xtf-file was wrong...
The question now is: why was this error in the xtf-file not found when validation the xtf.
Why comes such error-messages to the user (and are not found by the software before)?

@urskaufmann urskaufmann changed the title DSS Import: error when importing data with maintenace_events DSS Import: Handling of IntegrityError: "not present in table" Apr 18, 2023
@sjib sjib self-assigned this Apr 19, 2023
@sjib
Copy link
Contributor

sjib commented Apr 19, 2023

  • In VSA-DSS Import/Export the class maintenance_event was not yet configured as this needs to be treated differently than in VSA-KEK where it goes together with the subclass examination.

@sjib
Copy link
Contributor

sjib commented Apr 19, 2023

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "throttle_shut_off_unit" violates foreign key constraint "rel_throttle_shut_off_unit_wastewater_node" DETAIL: Key (fk_wastewater_node)=(A001gni5btl9vhqi) is not present in table "wastewater_node".

@urskaufmann If this was a data error - was the wastewater_node missing in your data and it did not find that in the validation process?
That would be very strange.

@sjib sjib added the bug Something isn't working label Apr 26, 2023
@sjib
Copy link
Contributor

sjib commented May 19, 2023

List of difference VSA-KEK 2019/ 2020 and qgep see #91 (comment)

@sjib
Copy link
Contributor

sjib commented May 11, 2024

Should be solved with QGEP/datamodel#235

@sjib sjib closed this as completed Oct 3, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants