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. — quapka
– quapka, Commented May 13, 2016 at 12:55

quapka · Accepted Answer · 2021-03-18 11:52:35Z

1

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/

edited Mar 18, 2021 at 11:52

answered May 13, 2016 at 12:48

quapka

2,9495 gold badges23 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

alexandre-rousseau Over a year ago

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 Over a year ago

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?

Niklas Over a year ago

Late side note: Using string format on execute can lead for SQL injections, bad for the security of application.

quapka Over a year ago

@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.

Collectives™ on Stack Overflow

Print each queries with Sqlite & Python

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related