trap-manage.py initdb fails with SQLAlchemy language error

[AntoniaR]

Using the TraP version from this Pull request #596 and python release 3.10.9 in a conda environment.

I set up a pipeline.cfg file as normal to create a new database for running the tests on this pull request version. I then run trap-manage.py initdb in the same folder as the pipeline.cfg file. I then get this error message and fail to make a database:

Traceback (most recent call last):
  File "/home/antoniar/miniconda3/envs/TraP_r6_py3/bin/trap-manage.py", line 7, in <module>
    exec(compile(f.read(), __file__, 'exec'))
  File "/home/antoniar/TraP_py3/tkp/tkp/bin/trap-manage.py", line 10, in <module>
    tkp.management.main()
  File "/home/antoniar/TraP_py3/tkp/tkp/management.py", line 376, in main
    args.func(args)
  File "/home/antoniar/TraP_py3/tkp/tkp/management.py", line 246, in init_db
    populate(dbconfig)
  File "/home/antoniar/TraP_py3/tkp/tkp/db/sql/populate.py", line 145, in populate
    database.session.execute("CREATE LANGUAGE plpgsql;")
  File "/home/antoniar/miniconda3/envs/TraP_r6_py3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2229, in execute
    return self._execute_internal(
  File "/home/antoniar/miniconda3/envs/TraP_r6_py3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2022, in _execute_internal
    statement = coercions.expect(roles.StatementRole, statement)
  File "/home/antoniar/miniconda3/envs/TraP_r6_py3/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 413, in expect
    resolved = impl._literal_coercion(
  File "/home/antoniar/miniconda3/envs/TraP_r6_py3/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 639, in _literal_coercion
    return self._text_coercion(element, argname, **kw)
  File "/home/antoniar/miniconda3/envs/TraP_r6_py3/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 632, in _text_coercion
    return _no_text_coercion(element, argname)
  File "/home/antoniar/miniconda3/envs/TraP_r6_py3/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 602, in _no_text_coercion
    raise exc_cls(
sqlalchemy.exc.ArgumentError: Textual SQL expression 'CREATE LANGUAGE plpgsql;' should be explicitly declared as text('CREATE LANGUAGE plpgsql;')

[jdswinbank]

Without looking, this seems more likely to be due to a change in SQLAlchemy than in the TraP itself.

Actually, looking at the setup.py, I see we are pinning to a pretty old version (sqlalchemy>=1.0.0,<1.4.0). It may be that the best answer here is just to upgrade to a modern version.

[AntoniaR]

Hmmm... good point, we should probably switch to the latest versions of all the packages in setup.py. I will try that now.

[AntoniaR]

Just checked and the setup.py in the pull request branch is already using all the latest versions:

install_requires = """                                                                                                                                                         
    astropy                                                                                                                                                                    
    colorlog                                                                                                                                                                   
    numpy                                                                                                                                                                      
    psycopg2                                                                                                                                                                   
    python-casacore                                                                                                                                                            
    python-dateutil                                                                                                                                                            
    pytz                                                                                                                                                                       
    scipy                                                                                                                                                                      
    sqlalchemy                                                                                                                                                                 
    alembic                                                                                                                                                                    
    monotonic                                                                                                                                                                  
    psutil                                                                                                                                                                     
    pytest                                                                                                                                                                     
    dask                                                                                                                                                                       
    dask[array]                                                                                                                                                                
    """.split()

I think you're right that it is something that has changed in SQLAlchemy, perhaps new formatting in the latest version?

[jdswinbank]

Oh, yes, that makes sense. You are explicitly picking up changes in SQLAlchemy 2, which was just released the other day. If you unpin versions, TraP will need to be updated to work with the new version.

[AntoniaR]

Yes, that would be very helpful. Thanks! Antonia

---

Dr. Antonia Rowlinson

Associate Professor Anton Pannekoek Institute, University of Amsterdam @.**@.>

Associate Scientist ASTRON, Netherlands Institute for Radio Astronomy @.**@.>

---

From: Hanno Spreeuw @.> Date: Monday, 6 February 2023 at 12:29 To: transientskp/tkp @.> Cc: Antonia Rowlinson @.>, Assign @.> Subject: Re: [transientskp/tkp] trap-manage.py initdb fails with SQLAlchemy language error (Issue #604)

Somehow does not break any unit test from the converted_to_python3_with_sourcefinder_from_pyse_repo branch. Should I add a unit test to catch it?

— Reply to this email directly, view it on GitHubhttps://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Ftransientskp%2Ftkp%2Fissues%2F604%23issuecomment-1418933819&data=05%7C01%7Cb.a.rowlinson%40uva.nl%7C7cf9c8192fd64e3799a208db083566ab%7Ca0f1cacd618c4403b94576fb3d6874e5%7C0%7C0%7C638112797669532490%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=mGQ%2BU6lrvxFQq2Lsv%2FLnTLbTfBeS8%2FKT5MByiGTolLM%3D&reserved=0, or unsubscribehttps://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAANNSBEG2ZZIA6CKVNGW65TWWDOBHANCNFSM6AAAAAAUNNZA6Y&data=05%7C01%7Cb.a.rowlinson%40uva.nl%7C7cf9c8192fd64e3799a208db083566ab%7Ca0f1cacd618c4403b94576fb3d6874e5%7C0%7C0%7C638112797669532490%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=eCzlNSmzMmGM5AnCdWJZ%2BUrZUsIf82FPFSpJvR0wYVI%3D&reserved=0. You are receiving this because you were assigned.Message ID: @.***>

[HannoSpreeuw]

Yeah, sorry, you replied to a comment that I removed yesterday, since the error did appear from trap-manage.py initdb with SQLAlchemy 2 which I guess also breaks almost any unit test involving a db. I mistakenly thought it did not since there was an 1.x version of SQLAlchemy in my virt env.

[AntoniaR]

This issue is solved by setting sqlalchemy==1.4 in setup.py

A nicer solution would be to upgrade to sqlalchemy 2.

[AntoniaR]

Closing this issue as the workaround is fine.