Hello! I'm having issues with the SQLAlchemyTask. ...
# ask-the-community
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)
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.
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)
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
So have you got it working with the latest sqlalchemy image?
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)
Ah, okay. Please submit an issue.
cc @Niels Bantilan
If you could also create a PR, that'd be great!