Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

TypeError when using SnowflakeConnector fetchall() #16110

Open
tmyhu opened this issue Nov 26, 2024 · 2 comments
Open

TypeError when using SnowflakeConnector fetchall() #16110

tmyhu opened this issue Nov 26, 2024 · 2 comments
Labels
bug Something isn't working

Comments

@tmyhu
Copy link

tmyhu commented Nov 26, 2024 •
edited by zzstoatzz
Loading

Bug summary

After updating to Prefect 3 and prefect-snowflake 0.28.1, my task that fetches results for a Snowflake query fails with a TypeError:

@task(name="Fetch Record IDs from a Snowflake Query", description="Fetches record IDs from Snowflake using a query")
def fetch_record_ids_from_snowflake_task(republishing_from_snowflake: RepublishingFromSnowflakeModel):

    with SnowflakeConnector.load("test") as connector:
        all_rows = connector.fetch_all("SELECT 'JxUxzzA9UwMZhRNqknJ9X8' AS id UNION ALL SELECT 'dvXvY4uSYSCX4Wyiaryh3Q' AS id;")

    record_ids = [str(row[0]) for row in all_rows]

    return record_ids

Stack trace:

prefect-flow-vmmbq-twvdm prefect-job 23:15:12.653 | ERROR   | prefect.engine - Engine execution of flow run '229b5acd-f8f9-46ac-941d-1b1444bbaf55' exited with unexpected exception
prefect-flow-vmmbq-twvdm prefect-job Traceback (most recent call last):
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/engine.py", line 42, in <module>
prefect-flow-vmmbq-twvdm prefect-job     run_flow(flow, flow_run=flow_run)
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/flow_engine.py", line 821, in run_flow
prefect-flow-vmmbq-twvdm prefect-job     return run_flow_sync(**kwargs)
prefect-flow-vmmbq-twvdm prefect-job            ^^^^^^^^^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/flow_engine.py", line 701, in run_flow_sync
prefect-flow-vmmbq-twvdm prefect-job     return engine.state if return_type == "state" else engine.result()
prefect-flow-vmmbq-twvdm prefect-job                                                        ^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/flow_engine.py", line 255, in result
prefect-flow-vmmbq-twvdm prefect-job     raise self._raised
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/flow_engine.py", line 655, in run_context
prefect-flow-vmmbq-twvdm prefect-job     yield self
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/flow_engine.py", line 699, in run_flow_sync
prefect-flow-vmmbq-twvdm prefect-job     engine.call_flow_fn()
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/flow_engine.py", line 678, in call_flow_fn
prefect-flow-vmmbq-twvdm prefect-job     result = call_with_parameters(self.flow.fn, self.parameters)
prefect-flow-vmmbq-twvdm prefect-job              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/utilities/callables.py", line 206, in call_with_parameters
prefect-flow-vmmbq-twvdm prefect-job     return fn(*args, **kwargs)
prefect-flow-vmmbq-twvdm prefect-job            ^^^^^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/opt/prefect/gitlab-repository-gitlab-repo-prefect-flows/prefect_flows/flows/republisher/flow_republisher.py", line 481, in republisher_flow
prefect-flow-vmmbq-twvdm prefect-job     record_ids = fetch_record_ids_from_snowflake_task(republishing_from_snowflake)
prefect-flow-vmmbq-twvdm prefect-job                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/tasks.py", line 1002, in __call__
prefect-flow-vmmbq-twvdm prefect-job     return run_task(
prefect-flow-vmmbq-twvdm prefect-job            ^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/task_engine.py", line 1526, in run_task
prefect-flow-vmmbq-twvdm prefect-job     return run_task_sync(**kwargs)
prefect-flow-vmmbq-twvdm prefect-job            ^^^^^^^^^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/task_engine.py", line 1339, in run_task_sync
prefect-flow-vmmbq-twvdm prefect-job     return engine.state if return_type == "state" else engine.result()
prefect-flow-vmmbq-twvdm prefect-job                                                        ^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/task_engine.py", line 482, in result
prefect-flow-vmmbq-twvdm prefect-job     raise self._raised
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/task_engine.py", line 781, in run_context
prefect-flow-vmmbq-twvdm prefect-job     yield self
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/task_engine.py", line 1337, in run_task_sync
prefect-flow-vmmbq-twvdm prefect-job     engine.call_task_fn(txn)
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/task_engine.py", line 804, in call_task_fn
prefect-flow-vmmbq-twvdm prefect-job     result = call_with_parameters(self.task.fn, parameters)
prefect-flow-vmmbq-twvdm prefect-job              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect/utilities/callables.py", line 206, in call_with_parameters
prefect-flow-vmmbq-twvdm prefect-job     return fn(*args, **kwargs)
prefect-flow-vmmbq-twvdm prefect-job            ^^^^^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/opt/prefect/gitlab-repository-gitlab-repo-prefect-flows/prefect_flows/flows/republisher/flow_republisher.py", line 115, in fetch_record_ids_from_snowflake_task
prefect-flow-vmmbq-twvdm prefect-job     all_rows = connector.fetch_all(republishing_from_snowflake.snowflake_query)
prefect-flow-vmmbq-twvdm prefect-job                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/prefect_snowflake/database.py", line 555, in fetch_all
prefect-flow-vmmbq-twvdm prefect-job     return cursor.fetchall()
prefect-flow-vmmbq-twvdm prefect-job            ^^^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/snowflake/connector/cursor.py", line 1531, in fetchall
prefect-flow-vmmbq-twvdm prefect-job     row = self.fetchone()
prefect-flow-vmmbq-twvdm prefect-job           ^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/snowflake/connector/cursor.py", line 1497, in fetchone
prefect-flow-vmmbq-twvdm prefect-job     raise err
prefect-flow-vmmbq-twvdm prefect-job   File "/usr/local/lib/python3.12/site-packages/snowflake/connector/cursor.py", line 1485, in fetchone
prefect-flow-vmmbq-twvdm prefect-job     _next = next(self._result, None)
prefect-flow-vmmbq-twvdm prefect-job             ^^^^^^^^^^^^^^^^^^^^^^^^
prefect-flow-vmmbq-twvdm prefect-job TypeError: 'NoneType' object is not an iterator

This happens with every query I tried.

I was able to work around the error by using the connection directly instead which seems to work fine:

    with SnowflakeConnector.load("test") as connector:
        with connector.get_connection() as connection:
            all_rows = connection.cursor().execute(republishing_from_snowflake.snowflake_query).fetchall()

Version info

Container image `prefecthq/prefect:3.1.2-python3.12` on Kubernetes.

Additional context

Playing around with the snowflake-connector library directly, I noticed that I get the same error when calling fetchall() on a cursor that has not executed any query.
@tmyhu tmyhu added the bug Something isn't working label Nov 26, 2024
@zzstoatzz
Copy link
Collaborator

zzstoatzz commented Nov 26, 2024 •
edited
Loading

hi @tmyhu - thanks for the issue!

Playing around with the snowflake-connector library directly, I noticed that I get the same error when calling fetchall() on a cursor that has not executed any query.

Given this, do you think the issue has to do with the implementation of prefect-snowflake somehow?

@tmyhu
Copy link
Author

tmyhu commented Nov 26, 2024

Yes that was my suspicion hence raising the issue here instead of in the snowflake connector repo. But I'm not proficient enough in python to hunt down the cause in the source code.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@zzstoatzz @tmyhu