SNOW-841996: multi-statement count parameter support doesnt seem to be working...

[dss010101]

Python version

3.11

Operating system and processor architecture

Linux-5.15.90.1-microsoft-standard-WSL2-x86_64-with-glibc2.31

Installed packages

aiohttp==3.8.4
aiohttp-cors==0.7.0
aiosignal==1.3.1
appdirs==1.4.4
asttokens==2.2.1
async-generator==1.10
async-timeout==4.0.2
attrs==23.1.0
backcall==0.2.0
beautifulsoup4==4.12.2
blessed==1.20.0
blinker==1.6.2
bs4==0.0.1
cachetools==5.3.0
certifi==2023.5.7
charset-normalizer==3.1.0
click==8.1.3
colorful==0.5.5
comm==0.1.3
cssselect==1.2.0
debugpy==1.6.7
decorator==5.1.1
distlib==0.3.6
distro==1.8.0
et-xmlfile==1.1.0
exceptiongroup==1.1.1
executing==1.2.0
fake-useragent==1.1.3
feedparser==6.0.10
filelock==3.12.0
finnhub-python==2.4.18
Flask==2.3.2
fredapi==0.5.0
frozenlist==1.3.3
google-api-core==2.11.0
google-auth==2.17.3
googleapis-common-protos==1.59.0
gpustat==1.1
grpcio==1.51.3
h11==0.14.0
idna==3.4
ipykernel==6.22.0
ipython==8.13.2
itsdangerous==2.1.2
jedi==0.18.2
Jinja2==3.1.2
jsonschema==4.17.3
jupyter_client==8.2.0
jupyter_core==5.3.0
lxml==4.9.2
MarkupSafe==2.1.2
matplotlib-inline==0.1.6
msgpack==1.0.5
multidict==6.0.4
nest-asyncio==1.5.6
numpy==1.24.3
nvidia-ml-py==11.525.112
opencensus==0.11.2
opencensus-context==0.1.3
openpyxl==3.1.2
outcome==1.2.0
packaging==23.1
pandas==2.0.1
pandas-datareader==0.10.0
parse==1.19.0
parso==0.8.3
pexpect==4.8.0
pickleshare==0.7.5
platformdirs==3.5.0
polars==0.17.12
prometheus-client==0.16.0
prompt-toolkit==3.0.38
protobuf==4.22.4
psutil==5.9.5
psycopg==3.1.9
psycopg-binary==3.1.9
psycopg-pool==3.1.7
psycopg2-binary==2.9.6
ptyprocess==0.7.0
pure-eval==0.2.2
py-spy==0.3.14
pyarrow==12.0.0
pyasn1==0.5.0
pyasn1-modules==0.3.0
pydantic==1.10.7
pyee==10.0.1
Pygments==2.15.1
pyppeteer==0.0.25
pyquery==2.0.0
pyrsistent==0.19.3
PySocks==1.7.1
python-dateutil==2.8.2
python-dotenv==1.0.0
pytz==2023.3
PyYAML==6.0
pyzmq==25.0.2
ray==2.4.0
requests==2.30.0
requests-html==0.10.0
rsa==4.9
selenium==4.10.0
sgmllib3k==1.0.0
six==1.16.0
smart-open==6.3.0
sniffio==1.3.0
sortedcontainers==2.4.0
soupsieve==2.4.1
stack-data==0.6.2
TA-Lib==0.4.26
tabula==1.0.5
tabula-py==2.7.0
tornado==6.3.1
tqdm==4.65.0
traitlets==5.9.0
trio==0.22.0
trio-websocket==0.10.3
typing_extensions==4.5.0
tzdata==2023.3
urllib3==2.0.2
virtualenv==20.21.0
w3lib==2.1.1
wcwidth==0.2.6
webdriver-manager==3.8.6
websockets==11.0.3
Werkzeug==2.3.3
wsproto==1.2.0
xlrd==2.0.1
yahoo-fin==0.8.9.1
yarl==1.9.2

What did you do?

This release states that multi-statement queries are now supported via the `num_statements `parameter to cursor.execute:  https://community.snowflake.com/s/article/Snowflake-Connector-for-Python-Release-Notes

The following is some code to test this:


test_sql = """
set( var1, var2) = ('test1', 'test2');
select $var1, $var2
"""

#documentatiaon says use 0 for any number of statements. i get the same exception if i set it to 2 also.
cur.execute(test_sql, num_statements = 0).fetchall()

the exception this produces is:

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/usr/local/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 910, in execute
    Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
  File "/usr/local/lib/python3.11/site-packages/snowflake/connector/errors.py", line 290, in errorhandler_wrapper
    handed_over = Error.hand_to_other_handler(
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/snowflake/connector/errors.py", line 345, in hand_to_other_handler
    cursor.errorhandler(connection, cursor, error_class, error_value)
  File "/usr/local/lib/python3.11/site-packages/snowflake/connector/errors.py", line 221, in default_errorhandler
    raise error_class(
snowflake.connector.errors.ProgrammingError: 100132 (P0000): JavaScript execution error: Uncaught Execution of multiple statements failed on statement "set( var1, var2) = ('test1', '..." (at line 1, position 0).
Actual statement count 1 did not match the desired statement count 3. in SYSTEM$MULTISTMT at '    throw `Execution of multiple statements failed on statement {0} (at line {1}, position {2}).`.replace('{1}', LINES[i])' position 4
stackstrace: 
SYSTEM$MULTISTMT line: 10

Is there a proper way to handle this use case?

### What did you expect to see?

We should be able to handle sql we are able to execute in snowflake without much manipulation for the client connector.  Dont think i like having to consider differently formatted sql depending on whether im using the client vs the db editor.

[dss010101]

The following executes without error when i dont attempt to select the variables (which doesn't solve my problem above)...but im not able to get any results.

test_sql = """
set( var1, var2) = ('test1', 'test2');
select 1, 2, 3
"""

cur.execute(test_sql , num_statements = 0).fetchall()
print([x for x in cur])

simply prints out: []

Ultimately, i would like to load this data into pandas using code that work fine for single statement queries such as this:

cur.execute(test_sql , num_statements = 0).fetch_pandas_all()

but when i try this with the above query, i get the following exception:

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/usr/local/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 1120, in fetch_pandas_all
    raise NotSupportedError
snowflake.connector.errors.NotSupportedError: Unknown error

[sfc-gh-spanaite]

Hi @dss010101, first your pip list doesn't capture snowflake-connector-python so I don't really know what version you're running. But, based on the release notes from version 2.9.0 you need to call SnowflakeCursor.nextset() to iterate through the results of each statement.

This works for me as an example:

query = "set( var1, var2) = ('test1', 'test2'); select $var1, $var2" 

cur.execute(query, num_statements=2)

print(cur.fetchone())

while cur.nextset():
    print(cur.fetchone())

I get back:

$ python test.py 
('Statement executed successfully.',)
('test1', 'test2')

[sfc-gh-sfan]

It is a known bug that fetch_pandas_all does not work perfectly with multi-statement, because the result format is JSON for the child statements. As a workaround, you could do something like:

for _ in range(<your statement count>):
    another_cursor.execute(
        f"select * from table(result_scan('{this_cursor.sfqid}'))"
    this_cursor.nextset()

@sfc-gh-mkeller : This is the issue we talked about last week :p

[sfc-gh-spanaite]

@dss010101 Do you have any other questions? Otherwise we can close this issue.

[dss010101]

ow what version you're running. But, based on the release notes from version 2.9.0 you need to call SnowflakeCursor.nextset() to iterate through the results of each statement.

the goal is to fetch it into a pandas dataframe with fetch_pandas_all. i am pointing out that that does not work without the sql as is. it would seem i need to use declare instead of set when trying to use variables. i am trying to avoid having to run different sql in snowflake vs what i run in python. so was trying to figure out what is the cconsistent way of doing this.

[dss010101]

so it is a bug then for fetch_pandas_all or just plain not supported? i guess that is the clarification needed.

[sfc-gh-sfan]

In short for the clarification, it is not supported. The reality is a bit complicated:

• ARROW result format is not supported for multi-statement queries, which means we cannot directly convert the result to pandas dataframe.
• We can add additional processing in the connector to workaround it by issuing result_scan on the statements on the client.

In an ideal world, the backend should return the result in ARROW format such that the client does not need to do special handling. We'll evaluate this against other priorities but I personally would like to see this happen :)

[dss010101]

got it. thanks for the clarification. And yes, i would personally like to see it happen also :)

[sfc-gh-dszmolka]

closing this issue as I see the original question has been answered (how to enable multi-statement support for a particular query) and also secondary request has been answered (multi-statement query vs. fetch_pandas not supported due to backend limitation)

we can take on the remaining topic (enhancement for working around the backend limitation) as resources permit, if possible please open a new request for that, being a different topic. Thank you in advance!