how to test python functions that use database connections using pytest?

Question

I am new to unit-testing and using Pytest for testing my code. Writing tests for basic functions is easy using pytest, but I am not able to wrap my head around the concept of "monkey-patching" and "mocking" for testing functions that query database.

This is my code without the use of monkeypatching and mocking, I am actually querying the DB, which I don't want to do -

def test_execute_sql():
    conn = data_platform.DataPlatformConnection()
    sql_out = conn.execute_sql("SELECT sid, name, code FROM prod.school WHERE sid = 1158;")
    # sql_out = [(1158, 'Lakeview Elementary School', '4141034')]
    assert sql_out[0] == (1158, 'Lakeview Elementary School', '4141034')

def test_execute_sql_return_single_value():
    conn = data_platform.DataPlatformConnection()
    sql_out = conn.execute_sql("SELECT code FROM prod.school WHERE sid = 1158;")
    # sql_out = [('4141034',)]
    assert sql_out[0][0] == '4141034'

    # testing exception
    with pytest.raises(Exception) as excinfo:
        conn.execute_sql("")
    assert 'The sql clause parameter is blank' in str(excinfo.value)

can someone please explain to me the concept of mocking in pytest? so that I can start using it.

Answer 1

this will work

@pytest.mark.django_db
def test_execute_sql():
    conn = data_platform.DataPlatformConnection()
    sql_out = conn.execute_sql("SELECT sid, name, code FROM prod.school WHERE sid = 1158;")
    # sql_out = [(1158, 'Lakeview Elementary School', '4141034')]
    assert sql_out[0] == (1158, 'Lakeview Elementary School', '4141034')

@pytest.mark.django_db
def test_execute_sql_return_single_value():
    conn = data_platform.DataPlatformConnection()
    sql_out = conn.execute_sql("SELECT code FROM prod.school WHERE sid = 1158;")
    # sql_out = [('4141034',)]
    assert sql_out[0][0] == '4141034'

    # testing exception
    with pytest.raises(Exception) as excinfo:
        conn.execute_sql("")
    assert 'The sql clause parameter is blank' in str(excinfo.value)