Result checks¶

allow_error(state)¶

Allow submission to pass, even if it originally caused a database error.

This function has been renamed.

Use Ex().allow_errors() in your SCT if the intent of the exercise is to generate an error.

check_all_columns(state, allow_extra=True, too_many_cols_msg=None, expand_msg=None)¶

Zoom in on the columns that are specified by the solution

Behind the scenes, this is using check_column() for every column that is in the solution query result. Afterwards, it’s selecting only these columns from the student query result and stores them in a child state that is returned, so you can use has_equal_value() on it.

This function does not allow you to customize the messages for check_column(). If you want to manually set those, simply use check_column() explicitly.

Parameters:

allow_extra – True by default, this determines whether students are allowed to have included other columns in their query result.
too_many_cols_msg – If specified, this overrides the automatically generated feedback message in case allow_extra is False and the student’s query returned extra columns when comparing the so the solution query result.
expand_msg – if specified, this overrides the automatically generated feedback message that is prepended to feedback messages that are thrown further in the SCT chain.

Example:

Consider the following solution and SCT:

# solution
SELECT artist_id as id, name FROM artists

# sct
Ex().check_all_columns()

# passing submission
SELECT artist_id as id, name FROM artists

# failing submission (wrong names)
SELECT artist_id, name FROM artists

# passing submission (allow_extra is True by default)
SELECT artist_id as id, name, label FROM artists

check_column(state, name, missing_msg=None, expand_msg=None)¶

Zoom in on a particular column in the query result, by name.

After zooming in on a column, which is represented as a single-column query result, you can use has_equal_value() to verify whether the column in the solution query result matches the column in student query result.

Parameters:

name – name of the column to zoom in on.
missing_msg – if specified, this overrides the automatically generated feedback message in case the column is missing in the student query result.
expand_msg – if specified, this overrides the automatically generated feedback message that is prepended to feedback messages that are thrown further in the SCT chain.

Example:

Suppose we are testing the following SELECT statements

solution: SELECT artist_id as id, name FROM artists
student : SELECT artist_id, name FROM artists

We can write the following SCTs:

# fails, since no column named id in student result
Ex().check_column('id')

# passes, since a column named name is in student_result
Ex().check_column('name')

check_query(state, query, error_msg=None, expand_msg=None)¶

Run arbitrary queries against to the DB connection to verify the database state.

For queries that do not return any output (INSERTs, UPDATEs, …), you cannot use functions like check_col() and has_equal_value() to verify the query result.

check_query() will rerun the solution query in the transaction prepared by sqlbackend, and immediately afterwards run the query specified in query.

Next, it will also run this query after rerunning the student query in a transaction.

Finally, it produces a child state with these results, that you can then chain off of with functions like check_column() and has_equal_value().

Parameters:

query – A SQL query as a string that is executed after the student query is re-executed.
error_msg – if specified, this overrides the automatically generated feedback message in case the query generated an error.
expand_msg – if specified, this overrides the automatically generated feedback message that is prepended to feedback messages that are thrown further in the SCT chain.

Example:

Suppose we are checking whether an INSERT happened correctly:

INSERT INTO company VALUES (2, 'filip', 28, 'sql-lane', 42)

We can write the following SCT:

Ex().check_query('SELECT COUNT(*) AS c FROM company').has_equal_value()

check_result(state)¶

High level function which wraps other SCTs for checking results.

check_result()

uses lowercase(), then
runs check_all_columns() on the state produced by lowercase(), then
runs has_equal_value on the state produced by check_all_columns().

check_row(state, index, missing_msg=None, expand_msg=None)¶

Zoom in on a particular row in the query result, by index.

After zooming in on a row, which is represented as a single-row query result, you can use has_equal_value() to verify whether all columns in the zoomed in solution query result have a match in the student query result.

Parameters:

index – index of the row to zoom in on (zero-based indexed).
missing_msg – if specified, this overrides the automatically generated feedback message in case the row is missing in the student query result.
expand_msg – if specified, this overrides the automatically generated feedback message that is prepended to feedback messages that are thrown further in the SCT chain.

Example:

Suppose we are testing the following SELECT statements

solution: SELECT artist_id as id, name FROM artists LIMIT 5
student : SELECT artist_id, name FROM artists LIMIT 2

We can write the following SCTs:

# fails, since row 3 at index 2 is not in the student result
Ex().check_row(2)

# passes, since row 2 at index 1 is in the student result
Ex().check_row(0)

lowercase(state)¶

Convert all column names to their lower case versions to improve robustness

Example:

Suppose we are testing the following SELECT statements

solution: SELECT artist_id as id FROM artists
student : SELECT artist_id as ID FROM artists

We can write the following SCTs:

# fails, as id and ID have different case
Ex().check_column('id').has_equal_value()

# passes, as lowercase() is being used
Ex().lowercase().check_column('id').has_equal_value()

has_equal_value(state, ordered=False, ndigits=None, incorrect_msg=None)¶

Verify if a student and solution query result match up.

This function must always be used after ‘zooming’ in on certain columns or records (check_column, check_row or check_result). has_equal_value then goes over all columns that are still left in the solution query result, and compares each column with the corresponding column in the student query result.

Parameters:

ordered – if set to False, the default, all rows are sorted (according to the first column and the following columns as tie breakers). if set to True, the order of rows in student and solution query have to match.
ndigits – if specified, number of decimals to use when comparing column values.
incorrect_msg – if specified, this overrides the automatically generated feedback message in case a column in the student query result does not match a column in the solution query result.

Example:

Suppose we are testing the following SELECT statements

solution: SELECT artist_id as id, name FROM artists ORDER BY name
student : SELECT artist_id, name FROM artists

We can write the following SCTs:

# passes, as order is not important by default
Ex().check_column('name').has_equal_value()

# fails, as order is deemed important
Ex().check_column('name').has_equal_value(ordered=True)

# check_column fails, as id is not in the student query result
Ex().check_column('id').has_equal_value()

# check_all_columns fails, as id not in the student query result
Ex().check_all_columns().has_equal_value()

has_ncols(state, incorrect_msg="Your query returned a table with {{n_stu}} column{{'s' if n_stu > 1 else ''}} while it should return a table with {{n_sol}} column{{'s' if n_sol > 1 else ''}}.")¶

Test whether the student and solution query results have equal numbers of columns.

Parameters:

incorrect_msg – If specified, this overrides the automatically generated feedback message in case the number of columns in the student and solution query don’t match.

Example:

Consider the following solution and SCT:

# solution
SELECT artist_id as id, name FROM artists

# sct
Ex().has_ncols()

# passing submission
SELECT artist_id as id, name FROM artists

# failing submission (too little columns)
SELECT artist_id as id FROM artists

# passing submission (two columns, even though not correct ones)
SELECT artist_id, label FROM artists

has_no_error(state, incorrect_msg='Your code generated an error. Fix it and try again!')¶

Check whether the submission did not generate a runtime error.

Simply use Ex().has_no_error() in your SCT whenever you want to check for errors. By default, after the entire SCT finished executing, sqlwhat will check for errors before marking the exercise as correct. You can disable this behavior by using Ex().allow_error().

Parameters:	incorrect_msg – If specified, this overrides the automatically generated feedback message in case the student’s query did not return a result.

has_nrows(state, incorrect_msg="Your query returned a table with {{n_stu}} row{{'s' if n_stu > 1 else ''}} while it should return a table with {{n_sol}} row{{'s' if n_sol > 1 else ''}}.")¶

Test whether the student and solution query results have equal numbers of rows.

Parameters:	incorrect_msg – If specified, this overrides the automatically generated feedback message in case the number of rows in the student and solution query don’t match.

has_result(state, incorrect_msg='Your query did not return a result.')¶

Checks if the student’s query returned a result.

Parameters:	incorrect_msg – If specified, this overrides the automatically generated feedback message in case the student’s query did not return a result.

AST Checks¶

check_edge(state, name, index=0, missing_msg='Check the {ast_path}. Could not find the {index}{field_name}.')¶

Select an attribute from an abstract syntax tree (AST) node, using the attribute name.

Parameters:

state – State instance describing student and solution code. Can be omitted if used with Ex().
name – the name of the attribute to select from current AST node.
index – entry to get from a list field. If too few entires, will fail with missing_msg.
missing_msg – feedback message if attribute is not in student AST.

Example:

If both the student and solution code are..

SELECT a FROM b; SELECT x FROM y;

then we can get the from_clause using

# approach 1: with manually created State instance -----
state = State(*args, **kwargs)
select = check_node(state, 'SelectStmt', 0)
clause = check_edge(select, 'from_clause')

# approach 2: with Ex and chaining ---------------------
select = Ex().check_node('SelectStmt', 0)           # get first select statement
clause =  select.check_edge('from_clause', None)    # get from_clause (a list)
clause2 = select.check_edge('from_clause', 0)       # get first entry in from_clause

check_node(state, name, index=0, missing_msg='Check the {ast_path}. Could not find the {index}{node_name}.', priority=None)¶

Select a node from abstract syntax tree (AST), using its name and index position.

Parameters:

state – State instance describing student and solution code. Can be omitted if used with Ex().
name – the name of the abstract syntax tree node to find.
index – the position of that node (see below for details).
missing_msg – feedback message if node is not in student AST.
priority – the priority level of the node being searched for. This determines whether to descend into other AST nodes during the search. Higher priority nodes descend into lower priority. Currently, the only important part of priority is that setting a very high priority (e.g. 99) will search every node.

Example:

If both the student and solution code are..

SELECT a FROM b; SELECT x FROM y;

then we can focus on the first select with:

# approach 1: with manually created State instance
state = State(*args, **kwargs)
new_state = check_node(state, 'SelectStmt', 0)

# approach 2:  with Ex and chaining
new_state = Ex().check_node('SelectStmt', 0)

has_code(state, text, incorrect_msg='Check the {ast_path}. The checker expected to find {text}.', fixed=False)¶

Test whether the student code contains text.

Parameters:	state – State instance describing student and solution code. Can be omitted if used with Ex(). text – text that student code must contain. Can be a regex pattern or a simple string. incorrect_msg – feedback message if text is not in student code. fixed – whether to match text exactly, rather than using regular expressions.

Note

Functions like check_node focus on certain parts of code. Using these functions followed by has_code will only look in the code being focused on.

Example:

If the student code is..

SELECT a FROM b WHERE id < 100

Then the first test below would (unfortunately) pass, but the second would fail..:

# contained in student code
Ex().has_code(text="id < 10")

# the $ means that you are matching the end of a line
Ex().has_code(text="id < 10$")

By setting fixed = True, you can search for fixed strings:

# without fixed = True, '*' matches any character
Ex().has_code(text="SELECT * FROM b")               # passes
Ex().has_code(text="SELECT \\* FROM b")             # fails
Ex().has_code(text="SELECT * FROM b", fixed=True)   # fails

You can check only the code corresponding to the WHERE clause, using

where = Ex().check_node('SelectStmt', 0).check_edge('where_clause')
where.has_code(text = "id < 10)

has_equal_ast(state, incorrect_msg='Check the {ast_path}. {extra}', sql=None, start='expression', exact=None)¶

Test whether the student and solution code have identical AST representations

Parameters:

state – State instance describing student and solution code. Can be omitted if used with Ex().
incorrect_msg – feedback message if student and solution ASTs don’t match
sql – optional code to use instead of the solution ast that is zoomed in on.
start – if sql arg is used, the parser rule to parse the sql code. One of ‘expression’ (the default), ‘subquery’, or ‘sql_script’.
exact – whether to require an exact match (True), or only that the student AST contains the solution AST. If not specified, this defaults to True if sql is not specified, and to False if sql is specified. You can always specify it manually.

Example:

Example 1 - Suppose the solution code is

SELECT * FROM cities

and you want to verify whether the FROM part is correct:

Ex().check_node('SelectStmt').from_clause().has_equal_ast()

Example 2 - Suppose the solution code is

SELECT * FROM b WHERE id > 1 AND name = 'filip'

Then the following SCT makes sure id > 1 was used somewhere in the WHERE clause.:

Ex().check_node('SelectStmt') \/
    .check_edge('where_clause') \/
    .has_equal_ast(sql = 'id > 1')

Logic¶

classmethod Ex.__call__(state=None)¶

Returns the current code state as a Chain instance.

This allows SCTs to be run without including their 1st argument, state.

When writing SCTs on DataCamp, no State argument to Ex is necessary. The exercise State is built for you.

Parameters:

state – a State instance, which contains the student/solution code and results.

Example:

code

# How to write SCT on DataCamp.com
Ex().has_code(text="SELECT id")

# Experiment locally - chain off of Ex(), created from state
state = SomeStateProducingFunction()
Ex(state).has_code(text="SELECT id")

# Experiment locally - no Ex(), create and pass state explicitly
state = SomeStateProducingFunction()
has_code(state, text="SELECT id")

check_correct(state, check, diagnose)¶

Allows feedback from a diagnostic SCT, only if a check SCT fails.

Parameters:	state – State instance describing student and solution code. Can be omitted if used with Ex(). check – An sct chain that must succeed. diagnose – An sct chain to run if the check fails.
Example:	The SCT below tests whether students query result is correct, before running diagnostic SCTs.. Ex().check_correct( check_result(), check_node('SelectStmt') )

check_not(state, *tests, msg)¶

Run multiple subtests that should fail. If all subtests fail, returns original state (for chaining)

This function is currently only tested in working with has_code() in the subtests.
This function can be thought as a NOT(x OR y OR ...) statement, since all tests it runs must fail
This function can be considered a direct counterpart of multi.

Parameters:

state – State instance describing student and solution code, can be omitted if used with Ex()
*tests – one or more sub-SCTs to run
msg – feedback message that is shown in case not all tests specified in *tests fail.

Example:

Thh SCT below runs two has_code cases..

Ex().check_not(
    has_code('INNER'),
    has_code('OUTER'),
    incorrect_msg="Don't use `INNER` or `OUTER`!"
)

If students use INNER (JOIN) or OUTER (JOIN) in their code, this test will fail.

check_or(state, *tests)¶

Test whether at least one SCT passes.

If all of the tests fail, the feedback of the first test will be presented to the student.

Parameters:

state – State instance describing student and solution code, can be omitted if used with Ex()
tests – one or more sub-SCTs to run

Example:

The SCT below tests that the student typed either ‘SELECT’ or ‘WHERE’ (or both)..

Ex().check_or(
    has_code('SELECT'),
    has_code('WHERE')
)

The SCT below checks that a SELECT statement has at least a WHERE c or LIMIT clause..

Ex().check_node('SelectStmt', 0).check_or(
    check_edge('where_clause'),
    check_edge('limit_clause')
)

disable_highlighting(state)¶

Disable highlighting in the remainder of the SCT chain.

Include this function if you want to avoid that pythonwhat marks which part of the student submission is incorrect.

fail(state, msg='fail')¶

Always fails the SCT, with an optional msg.

This function takes a single argument, msg, that is the feedback given to the student. Note that this would be a terrible idea for grading submissions, but may be useful while writing SCTs. For example, failing a test will highlight the code as if the previous test/check had failed.

multi(state, *tests)¶

Run multiple subtests. Return original state (for chaining).

This function could be thought as an AND statement, since all tests it runs must pass

Parameters:

state – State instance describing student and solution code, can be omitted if used with Ex()
tests – one or more sub-SCTs to run.

Example:

The SCT below checks two has_code cases..

Ex().multi(has_code('SELECT'), has_code('WHERE'))

The SCT below uses multi to ‘branch out’ to check that the SELECT statement has both a WHERE and LIMIT clause..

Ex().check_node('SelectStmt', 0).multi(
    check_edge('where_clause'),
    check_edge('limit_clause')
)

Electives¶

allow_errors(state)¶

Allow running the student code to generate errors.

This has to be used only once for every time code is executed or a different xwhat library is used. In most exercises that means it should be used just once.

Example:	The following SCT allows the student code to generate errors: Ex().allow_errors()

has_chosen(state, correct, msgs)¶

Verify exercises of the type MultipleChoiceExercise

Parameters:	state – State instance describing student and solution code. Can be omitted if used with Ex(). correct – index of correct option, where 1 is the first option. msgs – list of feedback messages corresponding to each option.
Example:	The following SCT is for a multiple choice exercise with 2 options, the first of which is correct.: Ex().has_chosen(1, ['Correct!', 'Incorrect. Try again!'])

success_msg(state, msg)¶

Changes the success message to display if submission passes.

Parameters:	state – State instance describing student and solution code. Can be omitted if used with Ex(). msg – feedback message if student and solution ASTs don’t match
Example:	The following SCT changes the success message: Ex().success_msg("You did it!")