While importing https://stibmivb.opendatasoft.com/api/explore/v2.1/catalog/datasets/gtfs-files-production/alternative_exports/gtfszip/ I got a unique constraint error on the translation.txt import.
The issuer added twice the same trans_id and lang.
I found the faulty lines and could import the data, but at the next update I will get again an error if they don't correct the file.
Is there a way to ignore existing trans_id & lang combination in the same file?
I'm afraid that SQLAlchemy doesn't support "OR IGNORE" in session.add().
2402 records read for <class 'pygtfs.gtfs_entities.Translation'>.
Traceback (most recent call last):
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1942, in _exec_single_context
context,
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 919, in do_executemany
cursor.executemany(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: translations.feed_id, translations.trans_id, translations.lang
2402 records read for <class 'pygtfs.gtfs_entities.Translation'>.
Traceback (most recent call last):
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1942, in _exec_single_context
context,
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 919, in do_executemany
cursor.executemany(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: translations.feed_id, translations.trans_id, translations.lang
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "sensor.py", line 321, in <module>
init(clean)
File "sensor.py", line 149, in init
attributes = getGTFSAttributes()
File "sensor.py", line 72, in getGTFSAttributes
pygtfs = import_gtfs_files()
File "sensor.py", line 66, in import_gtfs_files
pygtfs.append_feed(gtfs, os.path.join(".", data))
File "/home/pi/.local/lib/python3.7/site-packages/pygtfs/loader.py", line 121, in append_feed
schedule.session.flush()
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 4310, in flush
self._flush(objects)
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 4446, in _flush
transaction.rollback(_capture_exception=True)
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 4406, in _flush
flush_context.execute()
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
rec.execute(self)
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 645, in execute
uow,
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 98, in save_obj
insert,
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1044, in _emit_insert_statements
statement, multiparams, execution_options=execution_options
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1419, in execute
execution_options or NO_OPTIONS,
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 517, in _execute_on_connection
self, distilled_params, execution_options
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1649, in _execute_clauseelement
cache_hit=cache_hit,
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1849, in _execute_context
dialect, context, statement, parameters
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1989, in _exec_single_context
e, str_statement, effective_parameters, cursor, context
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1942, in _exec_single_context
context,
File "/home/pi/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 919, in do_executemany
cursor.executemany(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: translations.feed_id, translations.trans_id, translations.lang
[SQL: INSERT INTO translations (feed_id, trans_id, lang, translation) VALUES (?, ?, ?, ?)]
[parameters: [(1, 'STIB', 'fr', 'STIB'), (1, 'STIB', 'nl', 'MIVB'), (1, 'http://www.stib-mivb.be', 'fr', 'http://www.stib-mivb.be/index.htm?l=fr'), (1, 'http://www.stib-mivb.be', 'nl', 'http://www.stib-mivb.be/index.htm?l=nl'), (1, 'ABBAYE', 'fr', 'Abbaye'), (1, 'ABBAYE', 'nl', 'Abdij'), (1, 'ACACIAS', 'fr', 'Acacias'), (1, 'ACACIAS', 'nl', 'Acacia') ... displaying 10 of 2402 total bound parameter sets ... (1, 'ICHEC', 'fr', 'ICHEC'), (1, 'ICHEC', 'nl', 'ICHEC')]]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Hi,
While importing https://stibmivb.opendatasoft.com/api/explore/v2.1/catalog/datasets/gtfs-files-production/alternative_exports/gtfszip/ I got a unique constraint error on the translation.txt import. The issuer added twice the same trans_id and lang. I found the faulty lines and could import the data, but at the next update I will get again an error if they don't correct the file. Is there a way to ignore existing trans_id & lang combination in the same file? I'm afraid that SQLAlchemy doesn't support "OR IGNORE" in session.add().
Thanks