How should I rewrite my database execute/commit to make it amenable to unit testing?

I've been trying to get started with unit-testing while working on a little cli program.

My program basically parses the command line arguments and options, and decides which function to call. Each of the functions performs some operation on a database.

So, for instance, I might have a create function:

def create(self, opts, args):
    #I've left out the error handling.
    strtime = datetime.datetime.now().strftime("%D %H:%M")
    vals = (strtime, opts.message, opts.keywords, False)
    self.execute("insert into mytable values (?, ?, ?, ?)", va开发者_开发问答ls)
    self.commit()

Should my test case call this function, then execute the select sql to check that the row was entered? That sounds reasonable, but also makes the tests more difficult to maintain. Would you rewrite the function to return something and check for the return value?

Thanks

Alex's answer covers the dependency injection approach. Another is to factor your method. As it stands, it has two phases: construct a SQL statement, and execute the SQL statement. You don't want to test the second phase: you didn't write the SQL engine or the database, you can assume they work properly. Phase 1 is your work: constructing a SQL statement. So you can re-organize the code so that you can test just phase 1:

def create_sql(self, opts, args):
    #I've left out the error handling.
    strtime = datetime.datetime.now().strftime("%D %H:%M")
    vals = (strtime, opts.message, opts.keywords, False)
    return "insert into mytable values (?, ?, ?, ?)", vals

def create(self, opts, args):
    self.execute(*self.create_sql(opts, args))
    self.commit()

The create_sql function is phase 1, and now it's expressed in a way that lets you write tests directly against it: it takes values and returns values, so you can write unit tests that cover its functionality. The create function itself is now simpler, and need not be tested so exhaustively: you could have a few tests that show that it really does execute SQL properly, but you don't have to cover all the edge cases in create.

BTW: This video from Pycon (Tests and Testability) might be interesting.

I would definitely refactor this method for ease of testing -- for example, dependency injection might help:

def create(self, opts, args, strtime=None, exec_and_commit=None):
    #I've left out the error handling.
    if strtime is None:
        strtime = datetime.datetime.now().strftime("%D %H:%M")
    vals = (strtime, opts.message, opts.keywords, False)
    if exec_and_commit is None:
        exec_and_commit = self.execute_and_commit
    exec_and_commit("insert into mytable values (?, ?, ?, ?)", vals)

this of course assumes you have an execute_and_commit method that calls execute and then commit methods.

This way, the testing code can inject a known value for strtime, and inject its own callable as exec_and_commit to verify that it gets called with the expected arguments.

Generally, you like to have unit tests in place before refactoring, to ensure no breaking changes are made. And yet some refactoring may be needed to enable testability...an unfortunate paradox, one which has bit me before.

That said, there are some small refactorings that may be done safely, without changing behaviour. Rename and extract are two.

I recommend you take a look at Michael Feathers' book, Working with Legacy Code. It focuses on refactoring code for testability. The examples are in Java, but the concepts would apply just as well to Python.

Not familiar with python syntax but if you are new to unit testing, the easiest way to start could be to extract a method that you pass in the command line args and get back the sql command. On this method you can test the part of your code where the real logic lies. Pass in different types of args and check the results against what the sql command should be. Once you start to get the taste of unit testing you can learn a bit about mocking and dependency injection to test if you correctly call the functions that updates the db.

Hopefully you are more familiar with java c# syntax than i am with python syntax:)

public string GetSqlCommand(string[] commandLineArgs)
{
    //do your parsing here
    return sqlCommand;
}
[Test]
public void emptyArgs_returnsEmptySqlCommand()
{
    string expectedSqlCommand="";
    assert.AreEqual(expectedSqlCommand, GetSqlCommand(new string[])
}