Submission correctness tests for SQL


Keywords
fs, le
License
AGPL-3.0
Install
pip install sqlwhat==3.7.2

Documentation

sqlwhat

Build Status codecov PyPI version

sqlwhat enables you to write Submission Correctness Tests (SCTs) for interactive SQL exercises on DataCamp.

Installing

pip install sqlwhat     # install from pypi
make install            # install from source

Reference

Raising issues with how SQL is parsed

Please raise an issue on the respsective parser repo:

Basic Use

from sqlwhat.State import State    # State holds info needed for tests
from sqlwhat.Reporter import Reporter
from sqlwhat.checks import *       # imports all SCTs
from sqlalchemy import create_engine

code = "SELECT * FROM artists WHERE id < 100"

state = State(
    student_code = code,
    solution_code = code,
    pre_exercise_code = "",
    student_conn = create_engine('sqlite:///'),
    solution_conn = create_engine('sqlite:///'),
    student_result = {'id': [1,2,3], 'name': ['greg', 'jon', 'martha']},
    solution_result = {'id': [1,2,3], 'name': ['toby', 'keith', 'deb']},
    reporter = Reporter()
    )

# test below passes, since code is equal for student and solution
has_equal_ast(state)

# test below raises a TestFail error, since 'name' col of results
# doesn't match between student and solution results
check_result(state)
# shows error data
state.reporter.build_payload()

# can also be done using a chain
from sqlwhat.sct_syntax import Ex
Ex(state).check_result()

Running unit tests

pytest -m "not backend"

If you also want to run the backend tests, you need to set a GITHUB_TOKEN environment variable with access to the (private) sqlbackend repository. After this, you can:

make install
pytest

Rules of testing

  1. Running queries is the backend's job
  2. If a test doesn't run queries, it doesn't need the backend
  3. Very few tests should run queries

Building Docs

Install sqlwhat and run ..

cd docs
make html