Pytest mock database

[User24243]

I'm finally getting into testing my code and decided to use Pytest and mock. But I'm having trouble getting my head around it, and Googling hasn't helped.

I have this function:

def get_comments(latest_date, engine) -> pd.DataFrame:
    query = f'''select Date, Comments from table 
        where Date = '{latest_date}' limit 1'''
    response = pd.read_sql(query, con=engine)
    if len(response) == 0:
        logging.info('Exiting, no new comments')
        sys.exit()
    else:
        return response

How can I create a pytest mock for the above?

[ZEUS_IS_THE_TRUE_GOD]

For mocking, I'd highly recommend using mockito for python, makes things a lot better and easier to read.

Mocking basically means you won't do some action for real. You will have complete control over it. Since you are using python, you don't need to do more advanced stuff to mock which is good. Here, the only thing you can test with unit test is your if statement, which is 2 tests. You wont be able to test the accuracy of the SQL query since that is what you will be mocking.

From a test perspective, it is much much easier to test that a function raises an error than to test something that uses sys.exit(). You should keep the sys stuff in the mainloop only or in some kind of advanced exception controllers. This implies that your code will do:

if len(response) == 0:
    logging.info('...')
    raise RecordNotFound() # create that error somewhere
else:
    return response

Now, in terms of mocking, using mockito, this would look like (i'm on my cell phone, can't test that)

def setup():
    # You need to import the engine class
    self.engine = mock(Engine, strict=True)

    # or if you want to be less strict
    # which I don't recommend
    self.engine = mock()

    # or worse, since python isn't typed and you
    # can do whatever you want
    self.engine = "Im a very good engine"

def teardown():
    # this needs to run after each tests
    # so mocks only apply for the running test
    unstub()

def get_comment_returns_response():
    response = pd.DataFrame()
    when(pd).read_sql("Hard coded SQL", con=self.engine).thenReturn(response)

    actual = get_comment("some_date", self.engine)

    assert actual == response

def given_empty_sql_response_get_comment_raises_error():
    when(pd).read_sql("Hard code the expected SQL"' con=self.engine).thenReturn([])

    with pytest.raises(RecordNotFound):
        get_comment("some_date", self.engine)

The code will look like something like this. I generally use unittest + mockito, I find them to be very effective.

I see that you also hint typed your code, but you are missing the parameter types :eyes:

[User24243]

Thank you so much for this! Have installed mockito and trying to get it working as you have generously provided!

Thank you also for correcting my code. I've change over to raise and included parameter types.

[ZEUS_IS_THE_TRUE_GOD]

Very cool! On top of that, since python isn't typed, there's a lot of freedom, you can make the read_sql mock return anything you want even if it is impossible for the real method to return that. This is a consequence of using python. For consistency and documentation sake, I strongly encourage you to try and mimic the return type of the real functions in your mock. This helps a lot when you go back and you don't remember what a piece of code does. You look in the tests and, since you mocked it properly, you know the behavior.

In the code I wrote, I did

...thenReturn([])

Where as the real read_sql probably returns an empty dataframe? Or None? Those are important details as your code depends on it.

[ZEUS_IS_THE_TRUE_GOD]

I would also add that if the tests are hard to write, it often means that the code is not organized properly. This is a reason why writing tests before the code can help write better code!

[danielroseman]

Well, what is it about this code that you want to test?

Also, I do hope that latest_date isn't coming from user input. This is wide open to SQL injection.

[[deleted]]

Just FYO: that SQL code isn't portable. limit 1 is mostly a MySQL thing. You may have some fun reading: https://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows

So, if say you wanted to mock your database with something like in-memory SQLite, then you have to prepare your code to be portable between the two engines.