[Q] An error occurs when the record count of read_sql is 0

[takisawa]

I executed the following Python script.

pandas.read_sql("SELECT * FROM td_table WHERE 1=0", td)
=> tdclient.errors.InternalError: row index out of bound (0 out of 0)

Is this in the specifications?

In the case of mysql-connector-python, the result is as follows.

pandas.read_sql("SELECT * FROM mysql_table WHERE 1=0", mysql)
=> Empty DataFrame

[yyuu]

.fetchall()
Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples). Note that the cursor's arraysize attribute can affect the performance of this operation.

An Error (or subclass) exception is raised if the previous call to .execute*() did not produce any result set or no call was issued yet.

Hmm, according to pep-0249, it looks like raising exception on "did not produce any result set" is in specification. However, as a user, I feel that raising exception on empty result set is troublesome than beneficial.... I guess that the point is that the interpretation of "any result set"; if empty result is some result set or not 🤔

[yyuu]

I briefly checked other DBAPI 2 implementations, and confirmed that returning empty list is common behavior at least.

fetchall()
Fetches all (remaining) rows of a query result, returning a list. Note that the cursor’s arraysize attribute can affect the performance of this operation. An empty list is returned when no rows are available.
https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.fetchall

fetchall()
Fetch all (remaining) rows of a query result, returning them as a list of tuples. An empty list is returned if there is no more record to fetch.
http://initd.org/psycopg/docs/cursor.html?highlight=fetchall#cursor.fetchall

I'll try modifying the code to change the behavior later. Thanks letting us know anyways!

[takisawa]

I briefly checked other DBAPI 2 implementations, and confirmed that returning empty list is common behavior at least.

I'll try modifying the code to change the behavior later. Thanks letting us know anyways!

Thank you very much!!!
I am looking forward to it!

[yyuu]

I've released 0.12.1.dev0 to PyPI with including the fix. Please give it a try if it is working as you expected.

https://pypi.org/project/td-client/0.12.1.dev0/

[takisawa]

@yyuu

Thank you for your quick and great work!!!
I will try using 0.12.1.dev0.