Hello I m having issues with the SQLAlchemyTask It appears t Flyte #ask-the-community

Hello! I'm having issues with the SQLAlchemyTask. ...

Justin Boutwell

03/14/2023, 7:40 PM

Hello! I'm having issues with the SQLAlchemyTask. It appears that for sqlalchemy >= 2.0 the task is broken because that library fails to execute string querys and only works with sqlalchemy.text(querys). On https://github.com/flyteorg/flytekit/blob/master/plugins/flytekit-sqlalchemy/flytekitplugins/sqlalchemy/task.py#L135

df = pd.read_sql_query(interpolated_query, connection)

becomes

df = pd.read_sql_query(sqlalchemy.text(interpolated_query), connection)

I can verify locally sqlalchemy 1.4.x works with either a text object or string, but 2.0.x only works with text. I can submit that as an issue against flytekit, but I'm wondering if a) if there is a way to verify what version of the module the task is using and b) if there is a way to force using a specific sqlalchemy version.

Justin Boutwell

03/14/2023, 7:42 PM

Relatedly, as a work around, I've attempt to write a task that just uses sqlalchemy directly instead of the flytekit plugin, but it fails to execute on on the

import sqlalchemy

line. Are there packages not included or not resolvable in the base python image? (if that question even makes sense)

Justin Boutwell

03/15/2023, 1:48 AM

In case anyone else stumble upon this, I did find a workaround to the flytekitplugings-sqlalchemy 1.4.2 image failing by forcing the container to an older image

Copy code

sql_task = SQLAlchemyTask(container_image="<http://cr.flyte.org/flyteorg/flytekit:py3.10-sqlalchemy-1.2.7|cr.flyte.org/flyteorg/flytekit:py3.10-sqlalchemy-1.2.7>", ...)

I also got using sqlalchemy library working directly by using the sqlalchemy image which has the dependencies built in (conversely I could build my own image) and specifying it at runtime

Copy code

pyflyte run -p flyte-test -d development --destination-dir /app --remote --image <http://cr.flyte.org/flyteorg/flytekit:py3.10-sqlalchemy-latest|cr.flyte.org/flyteorg/flytekit:py3.10-sqlalchemy-latest> db_test.py db_workflow --limit 5

Samhita Alla

03/15/2023, 7:38 AM

So have you got it working with the latest sqlalchemy image?

Justin Boutwell

03/15/2023, 1:56 PM

no, i still believe flytekitsplugins-sqlalchemy is broken. but since cr.flyte.org/flyteorg/flytekit:py3.10-sqlalchemy-latest loads the sqlalchemy and other postgres modules it works as a base image for using those modules, but to use the SQLAlchemyTask i had to revert to 3.10-sqlalchemy-1.2.7 (sqlalchemy 2.0 released in early January, so needed to pick a flytekit version prior to that)

Justin Boutwell

03/15/2023, 1:59 PM

just running through https://docs.flyte.org/projects/cookbook/en/latest/auto/integrations/flytekit_plugins/sql/sql_alchemy.html will fail for all the recent flytekit builds

Samhita Alla

03/15/2023, 3:00 PM

Ah, okay. Please submit an issue.

Samhita Alla

03/15/2023, 3:01 PM

[flyte-bug]

Slackbot

03/15/2023, 3:01 PM

🐞 Create a new Flyte Bug issue: https://github.com/flyteorg/flyte/issues/new?assignees=&labels=bug%2Cuntriaged&template=bug_report.yaml&title=%5BBUG%5D+