search

QGEP / qgepqwat2ili

3 stars 3 forks source link

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

Open urskaufmann opened 1 year ago

urskaufmann commented 1 year ago

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:\PROGRA~1\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:\PROGRA~1\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:\PROGRA~1\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:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 2682, in _flush transaction.rollback(_captureexception=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:\PROGRA~1\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.executeaggregate(self, set) File "C:\PROGRA~1\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:\PROGRA~1\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:\PROGRA~1\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:\PROGRA~1\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:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in _handle_dbapiexception util.raise( File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_ raise exception File "C:\PROGRA~1\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:/PROGRA~1/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:\PROGRA~1\QGIS3\apps\grass\grass82\etc\python C:\Windows\system32 C:\Program Files\QGIS3\bin\python39.zip C:\PROGRA~1\QGIS3\apps\Python39\DLLs C:\PROGRA~1\QGIS3\apps\Python39\lib C:\Program Files\QGIS3\bin C:\PROGRA~1\QGIS3\apps\Python39 C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\win32 C:\PROGRA~1\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):

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 commented 1 year ago

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)?

sjib commented 1 year ago
sjib commented 1 year ago

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 commented 1 year ago

List of difference VSA-KEK 2019/ 2020 and qgep see https://github.com/QGEP/qgepqwat2ili/issues/91#issuecomment-1514785218

sjib commented 1 month ago

Should be solved with https://github.com/QGEP/datamodel/pull/235