FlexMeasures / flexmeasures / 36227954503

86%

push

github

Move to psycopg 3 (#2601)

* Stop relying on psycopg2 writing parameters into the query

psycopg2 wrote each parameter into the SQL, where Postgres coerced it to
whatever it was compared with. psycopg 3 sends typed parameters, which
turned a number of loose comparisons into errors:

- A URL's id string compared with an integer id column (e.g. /accounts/5,
  every /assets/<id>/... page, /assets/owned_by/<id>): ids are now turned
  into integers first, and a non-numeric id gives a 404. The account pages
  share a new get_account_by_id_or_raise_notfound, so an unknown account's
  audit log also gives a 404 rather than an error.
- An ISO 8601 duration string compared with an interval column in
  get_or_create_model (e.g. flexmeasures add toy-account): lookup values
  are now bound as their column's type, so the database reads them as it
  does when inserting.
- A JSON path given as a string to jsonb_path_exists (deleting an asset or
  a sensor): it is now cast to jsonpath.
- An id beyond what an integer column holds (e.g. a request for asset
  8171766575): the id fields now match nothing for it (get_by_id), and the
  asset search's id-prefix filter stops at the largest integer, rather
  than the database refusing the query.
- The audit log of a sensor attribute update stored its asset id as a
  one-element tuple, which psycopg2 happened to write as (1).

Tests that compared columns with strings now parse them first.

All of this works with psycopg2 as before.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01PZsZTwYKqi3NBS2EfQVZMU
Signed-off-by: F.N. Claessen <felix@seita.nl>

* Connect through psycopg 3 by default

A SQLALCHEMY_DATABASE_URI that names no driver (postgresql://) now connects through psycopg 3, on every SQLAlchemy version, where SQLAlchemy 2.0 would pick psycopg2 and 2.1 psycopg 3. FlexMeasures now depends on psycopg[binary] directly. A URI that names psycopg2 (postgresql+psycopg2://) ... (continued)

41 of 45 new or added lines in 15 files covered. (91.11%)

20549 of 23871 relevant lines covered (86.08%)

0.86 hits per line