Print each queries with Sqlite & Python

Question

I have a python script with Tkinter and I want to print in background each queries did in Sqlite database (just for the fun):

I have one Database Object:

import sqlite3

class Database():

    def __init__(self):
        try:
            sqlite3.enable_callback_tracebacks(True)
            self.connection = sqlite3.connect('databases.sqlite')
            self.cursor = self.connection.cursor()
            self.cursor.execute( """ CREATE TABLE ....  """ )
        except:
            print('error in database connection')

    def __del__(self):
        self.cursor.close()

And a Task object

class Task():

    def __init__(self, name="no_name"):
        self.database = Database()
        data = {"name" : name }
        self.database.cursor.execute("INSERT INTO tasks(name) VALUES(:name)" , data )
        self.database.connection.commit()

And when I did this new_task = Task('Hello') I want an automatic outpout in CLI like this:

* executed in 4ms :
    INSERT INTO tasks (name) VALUES('Hello');

Any idea? Thank you in advance!

As a side note, I am not sure, about using `__del__` for closing the cursor. Maybe more importantly, do you `close` the connection? Maybe better to have method `close..` doing `self.cur.close(); self.conn.close()`. And do you need to use `self` inside `Task`'s `__init__` method (as it looks, like there is nothing more to this class? Just get rid of self. And maybe even the `Task` is too much, function might suffice [see](https://www.youtube.com/watch?v=o9pEzgHorH0). — quapka, May 13 '16 at 12:55

quapka · Accepted Answer · 2021-03-18T11:52:35.773

Is this what you are looking for? I've thought about using decorator, some kind of stopwatch:

import time

def stopwatch(func):
    def wrapper(*args,**kwargs):
        start = time.time()
        func(*args,**kwargs)
        end = time.time()
        timed = int((end - start)*1000)
        print(timed)
    return wrapper

But then I thought about context managers, maybe (I am not the right person to judge) more suitable for this kind of job. Borrowing code from [here][1] I've ended up with (haha) this:

class Timer:    
    def __enter__(self):
        self.start = time.clock()
        return self

    def __exit__(self, *args):
        self.end = time.clock()
        # format as milliseconds 
        self.interval = int((self.end - self.start) * 1000)
        
    
class Task():

    def __init__(self, name="no_name"):
        data = {"name" : name }
        sql_template = "INSERT INTO tasks(name) VALUES(:name)"
        # do the database thingy inside the Timer context
        with Timer() as t:
            self.database = Database()
            self.database.cursor.execute(sql_template, data)
            self.database.connection.commit()
        print("* executed in {}ms :".format(t.interval))
        print("    {}".format(sql_template))

I've test it a little bit, but applying it to your case I might have done some mistakes as I had to change the Task __init__ a little bit to be able to reuse the SQL command. [1]: http://preshing.com/20110924/timing-your-code-using-pythons-with-statement/

Yes it's an good idea to use decorator but how get automaquely SQL commands executed on tasks methods (like UPDATE or DELETE)? — alexandre-rousseau, May 13 '16 at 13:57
Yes I think you're right for the decorator function. Is there any way to intercept each cursor method (like `__setattr__()` method) and print the query? — alexandre-rousseau, May 14 '16 at 08:59
Late side note: Using string format on execute can lead for SQL injections, bad for the security of application. — Niklas, Mar 17 '21 at 08:42
@Niklas Definitely! Thanks for the catch. I've updated the code. It does not match 100% with the expectation, as the final print does not contain the interpolated values. But I can't fix it at the moment. It's also not a minimal working example anyway. — quapka, Mar 18 '21 at 11:56

Print each queries with Sqlite & Python

1 Answers1