Cosima cookbook does not import with sqlalchemy 2

[dsroberts]

Hi All

Just attempted a test hh5 update, ran a notebook and got this:

---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
Cell In[1], line 3
      1 get_ipython().run_line_magic('matplotlib', 'inline')
----> 3 import cosima_cookbook as cc
      4 import matplotlib.pyplot as plt
      5 import numpy as np

File /scratch/v45/dr4292/conda_tmp/envs/analysis3-22.10/lib/python3.9/site-packages/cosima_cookbook/__init__.py:9
      6 import pkg_resources
      8 from . import database
----> 9 from . import querying
     10 from . import explore
     12 try:

File /scratch/v45/dr4292/conda_tmp/envs/analysis3-22.10/lib/python3.9/site-packages/cosima_cookbook/querying.py:12
     10 from sqlalchemy import func, distinct, or_
     11 from sqlalchemy.orm import aliased
---> 12 from sqlalchemy.sql.selectable import subquery
     13 import warnings
     14 import xarray as xr

ImportError: cannot import name 'subquery' from 'sqlalchemy.sql.selectable' (/scratch/v45/dr4292/conda_tmp/envs/analysis3-22.10/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py)

Can confirm it works fine with sqlalchemy 1.4.46.

[angus-g]

I don't know if subquery is even actually used, not sure why we have that import! (something something lint unused imports...)

[dougiesquire]

I don't think that the import of subquery is the only issue. After removing this, most of the tests error out with:

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: 'PRAGMA user_version'