Database profiler - Integration with SQLTap

[tng10]

Hi, I am trying to integrate SQLTap into a project with Sanic and GINO.

• GINO version: 0.8.0
• Python version: 3.6.6
• asyncpg version: 0.17.0
• aiocontextvars version: 0.2.0
• PostgreSQL version: 10.6

Description

I am basically using the most basic example of SQLTap, which is the following:

import sqltap

async def get_users():
    profiler = sqltap.start()
    all_users = await User.query.gino.all()
    statistics = profiler.collect()
    sqltap.report(statistics, "report.html")
    return all_users

What is going on behind the scenes on SQLTap is the class QueryStats is being instantiated and the parameter results which is suppose to be an instance of sqlalchemy.engine.ResultProxy, actually is a instance of gino.dialects.base._ResultProxy. I did a very basic comparison between ResultProxy class from SQLAlchemy and _ResultProxy class from Gino and I noticed there are methods/attributes omitted. Is there any reason to not have these attributes there? Is there a way to access the ResultProxy instance from SQLAlchemy?

When I execute this code, I receive the error described underneath.

Traceback

  File "/home/jclacerda/PycharmProjects/dgae/colombus/venv/lib/python3.6/site-packages/sqltap/sqltap.py", line 63, in __init__
    self.rowcount = results.rowcount
AttributeError: '_ResultProxy' object has no attribute 'rowcount'

Thank you!

[fantix]

The current (Dec 2018) GINO implementation of SQLAlchemy engine is different comparing to vanilla SQLAlchemy engine over DB-API. Vanilla SQLAlchemy calls cursor.execute() before creating ResultProxy, but GINO shall create a lazy _ResultProxy before execute with no knowledge about the actual result, in order to fit in the asynchronous database driver. Therefore, attributes like rowcount is actually unknown on _ResultProxy. SQLAlchemy ResultProxy instances do exist in GINO, but their references are never stored - they are created on-the-fly for process_rows() and discarded right away.

Let me see what can be done for SQLTap, I'll get back to you a bit later.

---

In short, GINO should modify the time when the after_execute hook is called. This has been something I wanted to fix, so please keep this issue open.

[fantix]

@tng10 This PR should at least make sqltap run. But if you use first(), scalar(), prepare(), iterate() or "executemany", the row count may be less than expected. Would that be a big problem in your case?

[tng10]

@fantix Thank you for the quick response. I think this PR will cover a lot of cases when analyzing database queries, for sure it will be interesting to have things like prepare() and iterate(), mainly because we can identify n+1 queries issues. This could be something to be improved in the near future, but for now I think this will suffice.

Thanks again!

[fantix]

Okay good to know! Let's get this merged and address the rest in a new issue. Thanks for the info!