[Tutor] How to test my code's interactions with SQLite db?

[Steven D'Aprano]

On Sun, Aug 16, 2015 at 01:18:06AM -0500, boB Stepp wrote:
> Being committed to TDD for this project, I am not yet ready to write
> code till I figure out how to write the tests.  It occurs to me that
> writing tests for code that interacts with the SQLite db may be
> non-trivial (At least for me!).  After doing some online research
> tonight, it appears that I need to create a test SQLite db.  I don't
> see any other way that I can test such code without having a db to
> test against.  So this leads to several questions:
> 
> 1) It would seem that I need to install a stand-alone version of
> SQLite, so that I can create this test db.  Either that or write a
> separate Python program whose sole purpose would be to create this
> test db.  But if I go with installing a stand-alone SQLite, will I run
> into version conflicts with whatever version of SQLite is bundled in
> the standard library of Python 3.4.3?

*scratches head*

I'm not sure what you mean by this. You create a sqlite database like 
this:

import sqlite3
conn = sqlite3.connect("spam.db")

To create a second database, you do this:

another = sqlite3.connect("eggs.db")


So I expect that you could do something like this:

- In your test directory, write a script which creates, and populates, a 
small database; you only need to run this script once, although if it is 
small enough, there's no harm to running it each time the test suite 
runs. It could even be part of the unit test setup code.

- Since you create the database, you know what the content will be.

- Write your application so that the database location is configurable 
when the application starts, not hard-coded. The easiest way to do this, 
although not the best way, is to make the database connection a global 
variable, then monkey-patch it from your test suite:

import myapplication
import sqlite3
db = sqlite3.connect("eggs.db")
myapplication.DB.close()
myapplication.DB = db
# run tests

- Your unit tests can set the database to the test DB and you can now 
check that functions return the results you expect.

This, by the way, is a good idea even if you aren't testing the DB 
layer. You don't want a bug or badly-thought out test in your test suite 
to mess up the actual database used for live data.


-- 
Steve