Testing SQL

In brief

hs-test-python library allows testing SQL queries. It parses SQL files, sets up a database and executes queries within the database.

Extend class

SQLTest class contains needed tools to test SQL queries. It can be imported from the hstest modules:

from hstest import SQLTest

To start writing tests, create a class that extends the SQLTest class:

class Test(SQLTest):
    pass

Testing algorithm

After running the tests:

1. SQLTest finds *.sql file that is placed at the same directory where SQLTest class' module is placed.
2. Parses queries from the SQL file and saves them into the queries dict, where a key is a query name and the value is a query itself.
3. Sets up the database, and creates connection with it. The database connection object is stored in self.db field.
4. Runs test cases as it described in Writing tests guideline

Queries dictionary

SQLTest has queries dictionary where you should specify what queries should be parsed from the SQL file. While parsing SQL file, it takes queries keys, then look for queries with these names in SQL file. If a needed query doesn't exist in the file, the WrongAnswer exception is thrown with feedback Can't find '{name}' query from SQL files!

class TestSQLProject(SQLTest):
    queries = {
        'create_table': None,
        'insert_data': None
    }

The keys of the dict are query names to look for in the SQL file, the values should be None by default. After parsing SQL file the dict values will be replaced by queries.

SQL file queries format

SQL file should contain queries in the following format query_name = "query itself", to be parsed correctly.

Example of correct SQL file:

create_table = "CREATE TABLE contacts (
	contact_id INTEGER PRIMARY KEY,
	first_name TEXT NOT NULL,
	last_name TEXT NOT NULL,
	email TEXT NOT NULL UNIQUE,
	phone TEXT NOT NULL UNIQUE
);"

insert_data = "INSERT INTO contacts VALUES(1, 'first_name', 'last_name', 'email', 'phone');"

Example of wrong SQL file:

create_table = CREATE TABLE contacts (
	contact_id INTEGER PRIMARY KEY,
	first_name TEXT NOT NULL,
	last_name TEXT NOT NULL,
	email TEXT NOT NULL UNIQUE,
	phone TEXT NOT NULL UNIQUE
); # doesn't have quotes

INSERT INTO contacts VALUES(1, 'first_name', 'last_name', 'email', 'phone'); # doesn't have key