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

[boB Stepp]

On Sun, Aug 16, 2015 at 4:03 AM, Steven D'Aprano <steve at pearwood.info> wrote:
> On Sun, Aug 16, 2015 at 01:18:06AM -0500, boB Stepp wrote:

>> 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...

The thought is to download and install the SQLite command-line shell
program available at

http://www.sqlite.org/download.html

which is currently at version 3.8.11.1.  The SQLite in my standard
library for Python 3.4.3 is version 3.8.3.1.  (Until just now checking
what the actual versions are, I did not realize how close they are.)
Using this command-line version  (which I was calling "stand alone") I
would independently of my program create a test db for use in my
program's tests.


> 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.

And this was my second thought for creating a test db for use in my
unit tests.  However, I was thinking that the first approach would be
better as then the test db itself would be totally independent of
whatever code I write in the actual program.  It would also work as a
design tool as the command-line SQLite would allow me to easily get a
visual representation of the tables, etc., which I think would be
helpful as I start to code.  Also, per Alan's suggestions it would be
more easy to add the tricky cases that I would want to be certain my
tests handle.

>
> 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.

Yeah, I am really concerned about this possibility.  My other concern
is that I would unknowingly structure my test db differently than what
my program would generate.

boB