How to pytest a POST request that calls a function containing a sql call?

user_11200273 0
Viewed 79

I have a POST request that calls a function, passing in an ID as an argument which the child function uses in their sql query. How do I test the post request function? I get a no such table error after getting to pd.read_sql(query, engine) but when I SQL using MySQLworkbench, the table does exist. The function also works when ran normally without pytest.

The function I want to test:

getMovieBudget(movieId):
    engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI']
    query = """
            SELECT *
            FROM movies
            WHERE id = '{}';
            """.format(movieId)
    data = pd.read_sql(query, engine)
    return data.iloc[0]['building_id']

@function.route('/upload', methods=['POST'])
def test_post():
    // .. some code here
    movieId = request.form['movieId']
    movieBudget = getMovieBudget(movieId)

My test file:

def test_post(client, capsys):
    url = f'/{helpers.get_domain()}/{helpers.get_version()}/movies/'

    data = {
        'movieId': 144,
    }

    response = client.post(url, data=data)

    assert response.status_code == 200

Running app.config['SQLALCHEMY_DATABASE_URI'] returns sqlite:////tmp/test.db

It's because my config test file has the following:

os.environ['TEST_ENV'] = 'TRUE'

@pytest.fixture(autouse=True)
def mock_patch(monkeypatch):
    monkeypatch.setattr(SQLAlchemy, "create_all", mock.Mock(return_value=True))

@pytest.fixture
def client():
    from movie_handler import app

    app.config['TESTING'] = True
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
    return app.test_client()
flask-sqlalchemy pytest mocking sql python
1 Answers

The return value of pandas.read_sql needs to be mocked like

@mock.patch('pandas.read_sql')
def test_post(query_mock, client, capsys):
   \\ ... some code
    d = {'movieId': [114102]}
    df = pd.DataFrame(data=d)
    query_mock.return_value = df
user_112002730
Related