hypothesis+unittest tests lock sqlite database

Question

I'm trying to test my Database class. Here is simplified example of it.

class Database:
""" it has more methods but I show only the most important """
    def __init__(self, name):
        # let's think the db-file exists with tables
        self.conn = sqlite3.connect(name)
        self.cursor = self.conn.cursor()

    def __del__(self):
    """ Here I close connection if the object was destroyed """
        self.conn.close()

    def insert(self, col1, col2, col3):
    """ The key method where problem is """
        self.cursor.execute(insert_query.format(col1, col2, col3))
        self.conn.commit()  # here I do commit to apply changes with DB

So, I want to check insert method. The test case class is:

class DatabaseTestCase(unittest.TestCase):
""" it has other methods but the problem is here """
    @given(col1=text(col1_params), col2=text(col2_params), col3=text(col3_params))
    def test_db_insert(self, col1, col2, col3):
        db = Database("test.db")
        input_data = col1, col2, col3

        # insert with commit (see Database example above)
        db.insert(*input_data)

        # delete object and close connection
        del db

        # recreate the object to get sure my data was added and 
        # the changes were commited
        db = Database("test.db")

        # I use the way not to use my own methods of Database object
        cursor = db.conn.execute("SELECT * FROM mytable WHERE col1 = '{}'".format(col1))
        result = cursor.fetchone()

        for input_item, row_item in zip(input_data, result):
            pass  # assert here

        # close connection with deleting of the db object
        del db

The problem is "database is locked" in traceback when db.insert is called from test method. I see the code as next steps:

open 1st connection
insert data
commit and close connection
open 2nd connection (after first was closed)
get data inserted on step 2 using select
compare data
assert if input and selected data are not equalled.

But... I have not to get the message about database blocking if the connections work with database one by one, have I? I had a idea the libs (unittest or hypothesis) use threading but I found nothing in the documentation.

Also I tried to run it in usual for and insert enumerable data. It works fine.

If I am not wrong every call of commit method must unblock the database even the connection is opened, but it's seems it is not happened.

Could anyone help me to understand why I see the "database is locked" message?

One suggestion to use `setUp` and `tearDown` to setup and close db for every tests, the lock might be done in other tests, did not get closed properly. — Gang, Sep 23 '18 at 16:02
I use the name `test.db` only for `insert` method where I can put fake data. For other methods I use database with actual data. So, the open/close operations of "test.db" are executed right here. — Bogdan, Sep 23 '18 at 18:18

score 2 · Accepted Answer · edited Oct 03 '18 at 05:37

2

My suspicion is that your database connection isn't actually being closed. You shouldn't be using the same database file between test runs anyway - it's important that Hypothesis tests be repeatable - so the easiest thing to do would be to create a temporary file in your tests and use that instead of a fixed test.db and see if the problem goes away.

More generally, I think relying on things being closed in del tends to be a source of weird bugs and I would encourage the use of an explicit context manager or similar.

edited Oct 03 '18 at 05:37

Azat Ibrakov

9,998
9
38
50

answered Sep 24 '18 at 08:23

DRMacIver

2,259
1
17
17

@DRMaclver, `an explicit context manager or similar`... what do you mean here? – Bogdan Sep 24 '18 at 16:59
@Bogdan: you should read about `with` statement and context management in **Python** – Azat Ibrakov Oct 03 '18 at 05:38

score 2 · Answer 2 · answered Oct 08 '18 at 06:39

The db object doesn't actually run __del__ until it is garbage-collected, which you should not rely on happening at any particular time. As @DRMacIver suggested, it would be better to use a context manager for this.

You can check that this is the real problem by adding import gc; gc.collect() on the line after del db.

hypothesis+unittest tests lock sqlite database

2 Answers2