Bug #87776 Wrong return type from fetch_all() and unknown situation:

• Submitted: 15 Sep 2017 14:10
• Modified: 28 Apr 2022 16:40
• Reporter: Shahriyar Rzayev
• Status: Not a Bug
• Category: Connector / Python
• Severity: S2 (Serious)
• Version: 8.0.4
• OS: Any
• CPU Architecture: Any

[15 Sep 2017 14:10] Shahriyar Rzayev

Description:
Hi dear all,

According to the DOC:

https://dev.mysql.com/doc/dev/connector-python/mysqlx.SqlStatement.html#mysqlx.SqlStatemen...

The return type from execute() is SqlResult object:

https://dev.mysql.com/doc/dev/connector-python/mysqlx.SqlResult.html#mysqlx.SqlResult

Which has another method called fetch_all():

 fetch_all()

    Fetch all items.
    Returns:	The list of items.
    Return type:	list

But while testing:

import mysqlx

class RocksBulk:
    # The Class for Rocksdb Bulk Load
    def __init__(self):
        # Connect to a dedicated MySQL server
        self.session = mysqlx.get_session({
            'host': 'localhost',
            'port': 33060,
            'user': 'bakux',
            'password': 'Baku12345',
            'ssl-mode': mysqlx.SSLMode.DISABLED
        })

    def run_select_statement(self, schema_name, table_name):
        try:
            command = "select count(*) from {}.{}"
            sql = self.session.sql(command.format(schema_name, table_name))
            cursor = sql.execute()
            print "Returned from sql.execute()"
            print cursor

            result = cursor.fetch_all()
            print "Returned from cursor.fetch_all()"
            print result

        except Exception as e:
            raise
        else:
            return cursor

k = RocksBulk()
k.run_select_statement('employees', 'salaries2')

I have spotted that the return type is list of Row object not the list of items:

$ python test.py
Returned from sql.execute()
<mysqlx.result.SqlResult object at 0xd12dd0>
Returned from cursor.fetch_all()
[<mysqlx.result.Row object at 0xd12f90>]

It turned out that I must do something like:

result = cursor.fetch_all()
for i in result:
    # to get Row object which has only get_string() with unknown explanation
    # get_string(str_index) 
        #Returns the value if the index by string.

Currently looking for an option to get value from select.

How to repeat:
See description

[20 Sep 2017 12:14] MySQL Verification Team

Hello Shahriyar,

Thank you for the report and feedback.

Thanks,
Umesh

[20 Sep 2017 13:53] Nuno Mariz

Hi Shahriyar,
Thank you for your report.
In fact, the documentation is not clear about this. But the reference docs refers to 'item', because it can be a 'mysqlx.DbDoc' if Collections are being used or 'mysqlx.Row' if it's a session.sql() execution.

- session.sql() will return a 'mysqlx.SqlResult' object and when 'fetch_all()' is called will return a list of 'mysqlx.Row'
- Collection.find().execute() will return a 'mysql.DocResult' object and when 'fetch_all()' is called will return a list of 'mysqlx.DbDoc'

For the 'mysqlx.Row.get_string()' method, you should provide the column name in the SQL for simplicity.

For example if you have:

    command = "select count(*) as total from {}.{}"

You can do

    self.session.sql(command.format(schema_name, table_name))
    result = sql.execute()
    for row in result.fetch_all():
        print(row.get_string("total"))
        or
        print(row["total"])