Issue with numpy types due to upgrade to other psycopg version

Summary

When the throughput tool tries to upload calibration results to the DB, it gets error due to unsupported by psycopg types.

Steps to reproduce

Launch cwltool for the throughput_calibrator, e.g. from the directory /src/calibpipe/tests/workflows/throughput:

cwltool dpps-uc-120-22.cwl dpps-uc-120-22.cfg > test.log

What is the expected correct behavior?

Data should be uploaded to the DB after calculating throughput coefficients.

Relevant logs and/or screenshots

2024-05-14 13:02:08,551 INFO [calibpipe.ThroughputCalibration.MuonCalibrator] (muon_calibrator.update_throughput_db): Optical throughput for telescope 1 is filled from the calibration method Muon Rings
2024-05-14 13:02:08,582 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2024-05-14 13:02:08,582 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-05-14 13:02:08,583 INFO sqlalchemy.engine.Engine select current_schema()
2024-05-14 13:02:08,583 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-05-14 13:02:08,584 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2024-05-14 13:02:08,584 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-05-14 13:02:08,587 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-14 13:02:08,595 INFO sqlalchemy.engine.Engine INSERT INTO optical_throughput (tel_id, obs_id, validity_start, validity_end, optical_throughput_coefficient, optical_throughput_coefficient_std, method, valid_minimum_found, parameters_at_limit, calibration_success) VALUES (%(tel_id)s::INTEGER, %(obs_id)s::INTEGER, %(validity_start)s::TIMESTAMP WITH TIME ZONE, %(validity_end)s::TIMESTAMP WITH TIME ZONE, %(optical_throughput_coefficient)s, %(optical_throughput_coefficient_std)s, %(method)s::VARCHAR, %(valid_minimum_found)s, %(parameters_at_limit)s, %(calibration_success)s) RETURNING optical_throughput."ID"
2024-05-14 13:02:08,595 INFO sqlalchemy.engine.Engine [generated in 0.00018s] {'tel_id': 1, 'obs_id': 501, 'validity_start': datetime.datetime(2022, 3, 13, 13, 38, 33, 166932, tzinfo=datetime.timezone.utc), 'validity_end': datetime.datetime(2022, 3, 13, 13, 42, 5, 445275, tzinfo=datetime.timezone.utc), 'optical_throughput_coefficient': 0.16524618173619923, 'optical_throughput_coefficient_std': 0.026384080625776334, 'method': 'Muon Rings', 'valid_minimum_found': True, 'parameters_at_limit': True, 'calibration_success': True}
Error while inserting a row in 'optical_throughput'.
2024-05-14 13:02:08,596 INFO sqlalchemy.engine.Engine COMMIT
2024-05-14 13:02:08,596 ERROR [calibpipe.ThroughputCalibration] (tool.run): Caught unexpected exception: (psycopg.DataError) Numpy integer expected, got 'int64'
[SQL: INSERT INTO optical_throughput (tel_id, obs_id, validity_start, validity_end, optical_throughput_coefficient, optical_throughput_coefficient_std, method, valid_minimum_found, parameters_at_limit, calibration_success) VALUES (%(tel_id)s::INTEGER, %(obs_id)s::INTEGER, %(validity_start)s::TIMESTAMP WITH TIME ZONE, %(validity_end)s::TIMESTAMP WITH TIME ZONE, %(optical_throughput_coefficient)s, %(optical_throughput_coefficient_std)s, %(method)s::VARCHAR, %(valid_minimum_found)s, %(parameters_at_limit)s, %(calibration_success)s) RETURNING optical_throughput."ID"]
[parameters: {'tel_id': 1, 'obs_id': 501, 'validity_start': datetime.datetime(2022, 3, 13, 13, 38, 33, 166932, tzinfo=datetime.timezone.utc), 'validity_end': datetime.datetime(2022, 3, 13, 13, 42, 5, 445275, tzinfo=datetime.timezone.utc), 'optical_throughput_coefficient': 0.16524618173619923, 'optical_throughput_coefficient_std': 0.026384080625776334, 'method': 'Muon Rings', 'valid_minimum_found': True, 'parameters_at_limit': True, 'calibration_success': True}]
(Background on this error at: https://sqlalche.me/e/20/9h9h)
Traceback (most recent call last):
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.DataError: Numpy integer expected, got 'int64'

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

Traceback (most recent call last):
  File "/Users/vdk/Software/ctasoft/ctapipe/src/ctapipe/core/tool.py", line 427, in run
    self.finish()
  File "/Users/vdk/Software/ctasoft/calibpipe/src/calibpipe/tools/throughput_calibrator.py", line 108, in finish
    self.muon_calibrator.update_throughput_db(self.throughputs)
  File "/Users/vdk/Software/ctasoft/calibpipe/src/calibpipe/throughput/muon_calibrator.py", line 345, in update_throughput_db
    TableHandler.upload_data(tel_container, self.config)
  File "/Users/vdk/Software/ctasoft/calibpipe/src/calibpipe/database/interfaces/table_handler.py", line 315, in upload_data
    TableHandler.insert_row_in_database(
  File "/Users/vdk/Software/ctasoft/calibpipe/src/calibpipe/database/interfaces/table_handler.py", line 63, in insert_row_in_database
    raise err
  File "/Users/vdk/Software/ctasoft/calibpipe/src/calibpipe/database/interfaces/table_handler.py", line 60, in insert_row_in_database
    connection.execute(sa.insert(table).values(**kwargs))
  File "/Users/vdk/Software/ctasoft/calibpipe/src/calibpipe/database/connections/sql_connection.py", line 115, in execute
    return self.session.execute(*args)
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2200, in _execute_internal
    result = conn.execute(
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
    self._handle_dbapi_exception(
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2357, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/vdk/mambaforge/envs/cta-dev/lib/python3.10/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
sqlalchemy.exc.DataError: (psycopg.DataError) Numpy integer expected, got 'int64'
[SQL: INSERT INTO optical_throughput (tel_id, obs_id, validity_start, validity_end, optical_throughput_coefficient, optical_throughput_coefficient_std, method, valid_minimum_found, parameters_at_limit, calibration_success) VALUES (%(tel_id)s::INTEGER, %(obs_id)s::INTEGER, %(validity_start)s::TIMESTAMP WITH TIME ZONE, %(validity_end)s::TIMESTAMP WITH TIME ZONE, %(optical_throughput_coefficient)s, %(optical_throughput_coefficient_std)s, %(method)s::VARCHAR, %(valid_minimum_found)s, %(parameters_at_limit)s, %(calibration_success)s) RETURNING optical_throughput."ID"]
[parameters: {'tel_id': 1, 'obs_id': 501, 'validity_start': datetime.datetime(2022, 3, 13, 13, 38, 33, 166932, tzinfo=datetime.timezone.utc), 'validity_end': datetime.datetime(2022, 3, 13, 13, 42, 5, 445275, tzinfo=datetime.timezone.utc), 'optical_throughput_coefficient': 0.16524618173619923, 'optical_throughput_coefficient_std': 0.026384080625776334, 'method': 'Muon Rings', 'valid_minimum_found': True, 'parameters_at_limit': True, 'calibration_success': True}]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

Environment

Version used: SQLAlchemy=2.0.29, psycopg=3.1.18, psycopg2-binary=2.9.9, cwltool=3.1.20240404144621
Operation System: MacOS 12.5

Edited May 14, 2024 by Vadym Voitsekhovskyi