Failures connecting to GravitySpy database with EventTable

Created by: andrew-lundgren

Accessing the GravitySpy database fails with two errors in the igwn-py39 environment. First psycopg2 is required to connect to the Postgres database, but it's not installed. After pip installing that locally, the query then fails with the traceback below. This is an incompatibility introduced in SQLAlchemy 2.0 with Pandas 1.5.3 issue.

One way to fix this is to downgrade to sqlalchemy<2.0, which worked when I locally pip installed it. This might be fixed in Pandas 2.0, which was released last week, but I haven't checked that. Another option would be modifying the call to Pandas following this post.

I also noticed that pandas is not in the pyproject.toml file. I'm not sure if it's brought in by another dependency.

To reproduce, first set these login credentials, then:

from gwpy.table import EventTable
gspy1 = EventTable.fetch('gravityspy', 'glitches_v2d0',
                            selection=[f"1267056018<event_time<1267106018"],
                            host='gravityspyplus.ciera.northwestern.edu')

Traceback of sqlalchemy error (trimmed to just relevant parts):

File /cvmfs/oasis.opensciencegrid.org/ligo/sw/conda/envs/igwn-py39/lib/python3.9/site-packages/gwpy/table/io/sql.py:91, in fetch(engine, tablename, columns, selection, **kwargs)
     88 qstr = 'SELECT %s FROM %s %s' % (columnstr, tablename, selectionstr)
     90 # perform query
---> 91 tab = pd.read_sql(qstr, engine, **kwargs)
     93 # Convert unicode columns to string
     94 types = tab.apply(lambda x: pd.api.types.infer_dtype(x.values))

# A bunch of Pandas internals

AttributeError: 'OptionEngine' object has no attribute 'execute'