Testing SQL

In brief

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

Supported databases

1. SQLite

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

Executing queries

SQLTest has functions that allow to execute queries in the database.

---

• execute(query_name) takes the query name as an argument, finds the query in the dict, then executes it in the database. If the given name doesn't exist in the dict, then the query executed as a plain text.

Examples

Example 1: execute query from the dict

class TestSQLProject(SQLTest):
    queries = {
        'create_table': None # None value will be replaced after parsing SQL file.
    }

    @dynamic_test
    def test_queries(self):
        self.execute('create_table')
        return correct()

self.execute('create_table')

is the same as:

self.execute(self.queries['create_table'])

Example 2: execute plain text query that selects the table that should be created by the query from the dict

class TestSQLProject(SQLTest):
    queries = {
        'create_table': None # None value will be replaced after parsing SQL file.
    }

    @dynamic_test
    def test_queries(self):
        self.execute('create_table')

        result = self.execute("SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name;")

        if 'contacts' not in result.fetchall()[0]:
            return wrong("Can't find 'contacts' table in the database")

        return correct()

self.execute("SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name;") executes the given query as a plain text, since the dict doesn't contain such a key.

---

• self.execute_and_fetch_all(query_name) is the same as self.execute(query_name).fetchall()