I would like to log of all SQL commands web2py sends to SQLite. Is it possible?

[João Matos]

Hello,

I would like to log of all SQL commands web2py sends to SQLlite. Is it possible?

Thanks,

JM

[Anthony]

Commands issued during migration operations are already saved to /databases/sql.log. If you want all commands (i.e., queries), note that on each request, there is db._timings, which is a list of tuples, where the first element of each tuple is the SQL command issued and the second element is the amount of time it took to execute. At the end of every request, you could dump the contents of db._timings to a log. You can do that by assigning a callback function to response._caller(), which wraps all calls to controller actions. Note, it will therefore miss any queries that are run in views, but it's probably not a good idea to run queries in views anyway. You could also set up middleware to do the logging: http://web2py.com/books/default/chapter/29/04/the-core?search=_caller#WSGI.

Anthony

[Anthony]

Also, if you set DAL(..., debug=True), I believe all SQL commands will be logged to the "pyDAL" logger.

Anthony

[João Matos]

Thanks Anthony, I activated debug=True in DAL but I can't find any log file.

Do you have any idea where it is created?

[Anthony]

See http://web2py.com/books/default/chapter/29/04/the-core#Logging. For the logging.conf file format, see https://docs.python.org/2/library/logging.config.html#logging-config-fileformat. You'll need to set up a handler for the "pyDAL" logger, which is what the DAL uses for logging.

[João Matos]

Thanks Anthony.

I went with a simple solution that serves my purpose (it's a temporary need).

I added this code starting with # JM to the gluon\packages\dal\pydal\adapters\base.py at line 413.

    @with_connection_or_raise
    def execute(self, *args, **kwargs):
        command = self.filter_sql_command(args[0])
        handlers = self._build_handlers_for_execution()
        for handler in handlers:
            handler.before_execute(command)

        # JM
        with open('c:\\web2py\\logs\\sql.log', 'a', encoding='utf-8') as f_out:  # type: TextIO
            if str(command) == 'PRAGMA foreign_keys=ON;':
                f_out.write('***' + '\r\n')
                f_out.write(command + '\r\n')
            else:
                f_out.write(command + '\r\n')

        # if str(command) == 'PRAGMA foreign_keys=ON;':
            # print('***')
            # print(command)
        # else:
            # print(command)

[Dave S]

On Monday, April 15, 2019 at 4:27:22 AM UTC-7, João Matos wrote:

Thanks Anthony.

I went with a simple solution that serves my purpose (it's a temporary need).

I added this code starting with # JM to the gluon\packages\dal\pydal\adapters\base.py at line 413.

Good thing it's a temporary need, because this tweak will get lost when you upgrade.  Using the logging configuration, however, can be used without getting clobbered by updates.  And turning on logging is simple (it's the native Python logging, basically), and could be turned on or off by your appini file.

/dps

[jcrm...@gmail.com]

Just to give you some feedback, I tried adding debug=True to the DAL command and creating a pyDAL logger. Nothing is shown in the logging file. It gets created (so the logging is working) but nothing shows up.

Also tried pydal instead of pyDAL as the name of the logger with the same result.

domingo, 17 de Março de 2019 às 00:26:54 UTC, Anthony escreveu:

[Massimo Di Pierro]

All queries are logged and timed in:

db._timings

Should also be logged by the DB itself.

[Anthony]

Logger is defined here: https://github.com/web2py/pydal/blob/master/pydal/base.py#L254

Debug logging is set up here: https://github.com/web2py/pydal/blob/9d30c5a7c65d2fbcf52a9254b9523cba500269dd/pydal/adapters/base.py#L373

Not sure what the problem is. Anyway, as already noted, you can just use db._timings.

Anthony