[Tutor] Proper SQLite cursor handling?

[Alan Gauld]

On 06/07/2021 00:10, Cameron Simpson wrote:

>> All of the above. Most big projects I've worked on we created a
>> specific test database that could be easily reloaded. It was
>> carefully designed to have data of all the different scenarios
>> we might need(including broken values)
> 
> The scenario Alan's describing here implies making the "oriiginal" test 
> db (or just maintaining one) with the test situations and having a dump 
> of it lying around. During testing you load the dump into a fresh 
> scratch database and test, not affecting the original.

Exactly so. Usually you can run an entire suite of tests before
having to reload. (Since the test database is itself several
hundred MB in size (still very small compared to the 3-4 terabytes
of the production one!)

> I've been working in a Django dev env recently, and the test suites make 
> a new empty db per test run, and the test creates the scenario to be 
> tested (eg makes some database entries with the scenario for the test).  
> I think this is arranged to be cheap yet isolated by doing tests inside 
> a transaction: 

I've worked on projects using that approach too, but the danger lies in
the fact that there is no spurious data lying around in the database,
since you tend to only create the rows you need. If a query is overly
inclusive you don't realize that it's harvesting more than it should
because there are no other rows, or at least only the "expected" other
rows. You have much less chance of collecting data accidentally. You
are also less likely to spot performance hot-spots because you only
have the basic data, not any huge tables - but arguably you would
construct a performance-test database specifically for that purpose.

But if the database is a fully loaded one and your errant queries
do start returning many more rows than expected it becomes obvious
sooner rather than later.

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