sqlalchemy ORM call fails with TypeError: expected bytes, str found

Question

I am making the following (relatively) simple call in a bit of code I have written:

pickups = session.query(Pickup).filter(Pickup.firebase_run_id == run_id).all()

I have two sets of integration tests over this code, one that runs locally and uses psycops2 and TLS, and one that runs in a GCP dev environment (a GCP cloud function) and uses pg8000 and a UNIX socket (GCP mandates (?) that cloud functions connect to Cloud SQL over a UNIX socket -- see here). The local integration tests run fine. However, the dev tests reliably fail with the following error:

    Traceback (most recent call last):
      File "/user_code/main.py", line 245, in GET_run
        response = run_get(run_id)
      File "/user_code/rubbish_geo_client/ops.py", line 686, in run_get
        pickups = session.query(Pickup).filter(Pickup.firebase_run_id == run_id).all()
      File "/env/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3319, in all
        return list(self)
      File "/env/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3481, in __iter__
        return self._execute_and_instances(context)
      File "/env/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3503, in _execute_and_instances
        querycontext, self._connection_from_session, close_with_result=True
    TypeError: expected bytes, str found

This specific function call is actually just one example of such a failure; all of my database calls fail with the same _execute_and_instances error!

Perhaps someone has an insight into what a workaround for this might be?

Answer 1

pg8000 had a longstanding bug in its implementation that the sqlalchemy driver just patched over. The new pg8000 maintainer recently introduced a patch for this issue, and released a new version of pg8000 that includes the fix. This unfortunately had the side-effect of breaking the sqlalchemy workaround.

Until sqlalchemy changes its driver to respect this patch, for now the best solution is to downgrade pg8000 from 1.16.6 to 1.16.5 or lower.

You can do so using pip install pg8000<=1.16.5.

For reference, refer to the following conversations: GH#commitcomment-43174891, GH#53.