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

[Alan Gauld]

On 16/08/15 07:18, boB Stepp wrote:

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

Correct. And it is a non trivial task but immensely useful since you can 
populate it with sample data representing every possible tricky scenario 
- multiple parents, no parents, duplicate students, etc. etc.
You can then test the code that handles those scenarios easily by 
referencing the appropriate test data..

> 1) It would seem that I need to install a stand-alone version of
> SQLite, so that I can create this test db.

You should do that anyway.
You will inevitably want to run SQL queries directly.
In fact you will probably have batch reports to create that are
much easier done using raw sql in a file rather than going
through Python. Even inserting your data will quite likely be
easier done with raw SQL.

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

Theoretically yes. But in practice I've never had an issue. The
SQL doesn't change much. But if you are really, really worried
there is a trick you can pull with the Python interpreter. The
cursor object has an executescript() method that takes a SQL
file as an argument.

> 2) If I install the test db I can conceptually see that I should be
> able to test all of the Python code that interacts with it.  However,
> do I need to figure out some way to test what will eventually become
> the *real* db that the program will generate and use?  How will I know
> if my test db structure and the resulting actual db structure that the
> ultimate user(s) will populate are in agreement?

Create the structure with one SQL script.
Insert the data with another one.

Use the same creation script for test and production databases.
That ensures both have the same structure but with different content.

> Or am I over-analyzing here?

No, they are valid concerns. Fortunately, more of an issue in
theory than in practice.

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos