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

[boB Stepp]

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?

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? Or am I
over-analyzing here?

TIA!

[Alan Gauld]

Post by boB Stepp
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..

Post by boB Stepp
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.

Post by boB Stepp
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.

Post by boB Stepp
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.

Post by boB Stepp
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


_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Ben Finney]

Post by boB Stepp
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!).

That's correct.

One of the primary benefits of Test-Driven Development is to enforce
good design on your code: you'll need to design your system so that it's
testable with clear, narrowly-defined interfaces.

SQL queries are *not* a clear, narrowly-defined interface. So, as you're
discovering, it is very difficult to write unit tests for code that
could execute some arbitrary query.

So that points to a need for better design: Don't directly issue
arbitrary SQL queries in code which implements higher-level features.
Instead, define a much more specific interface between your feature code
and the lower-level code that interacts with the database.

Put SQL queries only in very narrowly-defined database interaction
functions, where the input and output can be tested easily with unit
tests. The unit tests present mock database API functions, that
implement only enough to satisfy the narrow actions each low-level
function will perform.

If you can't set up a trivially-simple fixture to pretend to be the
database API, the function is doing too much. Break it down further,
possibly along the way finding duplication and refactoring those into
levels of abstraction.

Put feature code only in higher-level code, where the input and output
doesn't have anything to do with the details of SQL and can therefore be
tested easily with unit tests. The unit tests make trivially-simple
collections — basic types like mappings or sequences — to fake the
results from the lower-level code.

If you can't make a trivially-simple sequence or mapping to pretend to
be the result from the lower-level data code, the function is doing too
much. Break it down further, find duplications, refactor them to
abstration layers.

A database is a very heavy external dependency. You should not be
attempting to “mock the world” in order to make your tests run. Instead,
you should be making your code well designed: small and simple and
narrowly-defined functions, each one of which is easy to test with
simple fixtures.
--
\ “I call him Governor Bush because that's the only political |
`\ office he's ever held legally.” —George Carlin, 2008 |
_o__) |
Ben Finney

_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
h

[Alan Gauld]

Post by Ben Finney
So that points to a need for better design: Don't directly issue
arbitrary SQL queries in code which implements higher-level features.
Instead, define a much more specific interface between your feature code
and the lower-level code that interacts with the database.

This is a good point.
You may recall my earlier messages talking about MVC and the
fact that only the Model should interact with the database
via SQL? So the model interface is effectively your app's
API into the database. And when unit testing the Model you
will probably use mocks for the SQL calls in most cases.
So only a very small number of tests need a real test database.

Where you do need a test database is in testing the database
itself (data structure, constraints, relationships etc).
Much of that you can(and should) do from raw SQL,
but at some point (integration testing) you need to run
the actual SQL in your Model...

Another point worth mentioning that does NOT apply with Sqlite
is that most server DBs provide a Stored Procedure concept and
many database designers prefer to make all updates to data via
stored procedures and expose all read access via read-only
views. This is especially so when exposing the API over a
network (eg as web services). SQlite does not offer stored
procedures (but does offer views) and expects to work on
a local machine rather than over a network so it doesn't
apply there. But if you ever move to Firebird, Postgres
or MySQL it would be a thing to consider.

Post by Ben Finney
tests. The unit tests present mock database API functions, that
implement only enough to satisfy the narrow actions each low-level
function will perform.

This is also good advise but remember that unit tests only
form a small part of the overall testing (although they are
the bit normally used for TDD) and for system tests you
will need a test database.
--
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


_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Steven D'Aprano]

Post by boB Stepp
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
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.

[boB Stepp]

Post by Steven D'Aprano

Post by boB Stepp
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.

Post by Steven D'Aprano
- 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.

Post by Steven D'Aprano
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
_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Alan Gauld]

Post by boB Stepp
http://www.sqlite.org/download.html

You definitely want this.
You treat it like the >>> prompt in Pyython.

A place to try out SQL queries before you put
them into your Python code. Also you can write
long sql code in a .sql filer and read them
into the interpreter using the .read command

sqlite3> .read createdb.sql
sqlite3> .read populate_tests.sql
sqlite3> .read clear_tables.sql
sqlite3> .read populate_base_data.sql

etc etc.

Also you can ask the interpreter to describe table
structures and constraints etc. Think TDD here...

Remember to use semi-colons to terminate statements!

Post by boB Stepp
design tool as the command-line SQLite would allow me to easily get a
visual representation of the tables, etc.,

Depends what you mean by "visual"!

But there are several GUI tools available that will
provide an admin GUI for your DB, this is useful for visual
representation of tables(like a spreadsheet) and for ad-hoc
updates to fields. In practice I only use these on my smart
phone (I store the database on Dropbox) but then I'm fairly
fluent in SQL at the sqlite3> prompt!

Post by boB Stepp
is that I would unknowingly structure my test db differently than what
my program would generate.

As I said, create separate .sql files to create an empty database and to
populate the structure with initial data. That way you use the exact
same structure file in your code as in your tests (remember the
cursor.executescript() method I mentioned earlier! - that works
from inside your code as well as the >>> prompt!)
--
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


_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[boB Stepp]

Post by Alan Gauld

Post by boB Stepp
http://www.sqlite.org/download.html

You definitely want this.
You treat it like the >>> prompt in Pyython.

I had just finished installing and testing the installation just
before your email arrived. Very easy to do!

Post by Alan Gauld
A place to try out SQL queries before you put
them into your Python code. Also you can write
long sql code in a .sql filer and read them
into the interpreter using the .read command
sqlite3> .read createdb.sql
sqlite3> .read populate_tests.sql
sqlite3> .read clear_tables.sql
sqlite3> .read populate_base_data.sql

I am assuming that the .read command would be replaced inside the
program by the cursor.executescript() method you mentioned? This will
be quite handy, I think.

Post by Alan Gauld
Also you can ask the interpreter to describe table
structures and constraints etc. Think TDD here...

Would you mind giving a specific example illustrating how this would
aid testing? Being an insane accumulator of books, I have acquired,
"The Definitive Guide to SQLite, 2nd ed.", by Grand Allen and Mike
Owens, c. 2010. I haven't made it yet to the commands you are
alluding to.

Post by Alan Gauld

Post by boB Stepp
design tool as the command-line SQLite would allow me to easily get a
visual representation of the tables, etc.,

Depends what you mean by "visual"!

I just meant getting a text display of table contents in neat aligned
columns. The book I mentioned has already showed me how to do this.
This will be more than plenty for me. At this time, while
concentrating on learning SQL, I don't want to take any GUI shortcuts.

Post by Alan Gauld

Post by boB Stepp
is that I would unknowingly structure my test db differently than what
my program would generate.

As I said, create separate .sql files to create an empty database and to
populate the structure with initial data. That way you use the exact same
structure file in your code as in your tests (remember the
cursor.executescript() method I mentioned earlier! - that works
from inside your code as well as the >>> prompt!)

This looks okay from the bare bones standpoint. However, I am
wondering how much pre-existing structure I should impose on the user,
and how much flexibility I should allow the user to have to extend the
db structure as "shipped". For instance I can easily see the user
wanting to add new types of information she wants to track for her
student. I think the program should allow her to do things like this,
but while totally hiding the SQL interactions behind the scenes from
the user. But allowing this sort of functionality automatically
creates differences between what I've been testing in development and
what the user creates through normal program use.

I suppose, however, that I can design test cases that mimics all
allowed such functionality. But in this example, how many new columns
of table data do I cut off the tests at, for instance? Eventually RAM
or some kind of overflow condition will transpire, which suggests if I
want to test that a *lot* of table columns can be added without
breaking anything will require me to set some reasonable upper limit
for what constitutes a *lot*.

boB
_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Danny Yoo]

Hi Bob,

By the way, when you're unit testing with Sqlite, you might find it
convenient to use the ":memory:" option, which keeps the database in
RAM rather than on disk. That should make the "setup" and "tear-down"
of the testing environment easier to maintain.

The principle is similar to that of when we're unit testing functions
whose inputs or outputs are file-like objects; although we can use
real files, we can find in-memory structures like io.StringIO useful,
since they leave no residue once the tests are completed.
_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[boB Stepp]

Post by Danny Yoo
By the way, when you're unit testing with Sqlite, you might find it
convenient to use the ":memory:" option, which keeps the database in
RAM rather than on disk. That should make the "setup" and "tear-down"
of the testing environment easier to maintain.

I had noted this option, but I don't think I would have ever come up
with your idea on my own. Thanks!

Post by Danny Yoo
The principle is similar to that of when we're unit testing functions
whose inputs or outputs are file-like objects; although we can use
real files, we can find in-memory structures like io.StringIO useful,
since they leave no residue once the tests are completed.

Likewise thanks for opening my eyes to this perspective as well!

[Alan Gauld]

Post by boB Stepp

Post by Alan Gauld
sqlite3> .read populate_base_data.sql

I am assuming that the .read command would be replaced inside the
program by the cursor.executescript() method you mentioned? This will
be quite handy, I think.

No.
The executescript() method *replaces* .read
.read is only used within the interpreter.
There are a whole bunch of these pseudo-commands,
including .quit to exit.

In executescript() you just pass in the name
of the file you want executed. Very easy.
But like os.system() not so easy to check
it all worked OK... an exercise for the reader,
as they say...

Post by boB Stepp

Post by Alan Gauld
Also you can ask the interpreter to describe table
structures and constraints etc. Think TDD here...

Again the . commands.
Try

sqlite3> .help

You'll like it :-)

In particular try the .tables, .trace and .schema commands.

Post by boB Stepp
aid testing? Being an insane accumulator of books, I have acquired,
"The Definitive Guide to SQLite, 2nd ed.", by Grand Allen and Mike
Owens, c. 2010.

I don;t know it - I own "Using SQLite" from O'Reilly.
But the web docs are good too.

Post by boB Stepp
and how much flexibility I should allow the user to have to extend the
db structure as "shipped". For instance I can easily see the user
wanting to add new types of information she wants to track for her
student.

I'd allow a small amount of leeway here.
The usual approach is to use string fields for the data
and a meta table with field, type, name information.

So if you have a student table with three user defined fields
UDF1, UDF2,UDF3. You define a meta table called meta_student
that has field, name, type columns containing things like:

UDF1, BOOL, hasTattoos
UDF2, FILENAME, Mugshot
UDF3, STRING, Birthmark

(You could have a single meta_data table with table as
first column but that just makes your SQL even more
complex IMHO! Small tables are OK.
Also some implementations allow user specific definitions
but that breaks badly if records are shared if, for example,
user1.UDF1 is hasTattoos and user2.UDF1 is isPregnant)

BUT! You need a lot of infra-structure around this since these
fields will be global to all users. And you need to manually
do any data validation of input to ensure that only valid
data gets stored. You will need a bank of supported
types/validator functions. And your UI needs to ensure users
can only create appropriate options (think drop down lists
and dictionaries).

Personally I'd only add this flexibility if you really need it.
Its a major code overhead and a big performance hit too.

Post by boB Stepp
allowed such functionality. But in this example, how many new columns
of table data do I cut off the tests at, for instance?

I've seen commercial systems that allow up to 100 UDFs.
In practice I'd go for 3-10. if they need more than 10
then your requirements capture and user testing was
very poor!

Post by boB Stepp
Eventually RAM or some kind of overflow condition will transpire,

With a database that tends to be disk space so think Terabytes.
Its not normally an issue these days!

Post by boB Stepp
want to test that a *lot* of table columns can be added without
breaking anything will require me to set some reasonable upper limit
for what constitutes a *lot*.

The biggest issue, as with user defined attributes in objects
is that your static code doesn't know what these new fields are.
Imagine how they play with a search function? Building a
SQL query against a boolean value in the UDF1 column means
translating the boolean result into its string equivalent
and doing a fill scan text search. Slow....

You really want database tables to be defined as fully as
possible as soon as possible. UDFs are a (sometimes necessary)
cost of failure!
--
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


_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[boB Stepp]

Post by Alan Gauld

Post by boB Stepp

Post by Alan Gauld
sqlite3> .read populate_base_data.sql

I am assuming that the .read command would be replaced inside the
program by the cursor.executescript() method you mentioned? This will
be quite handy, I think.

Oops! I wrote the above paragraph in a different form, thinking that
.read was in the Python DB API in some form, too, then went to the
docs to verify this, and saw that the method you mentioned,
executescript(), was serving the interpreter's .read command's
function, and did not rewrite the paragraph appropriately. I knew
what you meant, though.

Post by Alan Gauld

Post by boB Stepp
and how much flexibility I should allow the user to have to extend the
db structure as "shipped". For instance I can easily see the user
wanting to add new types of information she wants to track for her
student.

I'd allow a small amount of leeway here.
The usual approach is to use string fields for the data
and a meta table with field, type, name information.
So if you have a student table with three user defined fields
UDF1, UDF2,UDF3. You define a meta table called meta_student
UDF1, BOOL, hasTattoos
UDF2, FILENAME, Mugshot
UDF3, STRING, Birthmark
(You could have a single meta_data table with table as
first column but that just makes your SQL even more
complex IMHO! Small tables are OK.
Also some implementations allow user specific definitions
but that breaks badly if records are shared if, for example,
user1.UDF1 is hasTattoos and user2.UDF1 is isPregnant)
BUT! You need a lot of infra-structure around this since these
fields will be global to all users. And you need to manually
do any data validation of input to ensure that only valid
data gets stored. You will need a bank of supported
types/validator functions. And your UI needs to ensure users
can only create appropriate options (think drop down lists
and dictionaries).

Hmm. This is much more complex than I imagined, and I'm cynical by nature.

Post by Alan Gauld
Personally I'd only add this flexibility if you really need it.
Its a major code overhead and a big performance hit too.

Well, while this is a one-teacher project only, there will be constant
back-and-forth between me and said teacher, so I really would not need
this for her. However, if this program actually gets created and
works well, then I can see other teachers in the school using it, and
they may have different data they want to track. So I was thinking of
making it very flexible in this regard. Maybe I should hold off on
this until I see how what I need to do now plays out.

Post by Alan Gauld

Post by boB Stepp
allowed such functionality. But in this example, how many new columns
of table data do I cut off the tests at, for instance?

I've seen commercial systems that allow up to 100 UDFs.
In practice I'd go for 3-10. if they need more than 10
then your requirements capture and user testing was
very poor!

[...]

Post by Alan Gauld
The biggest issue, as with user defined attributes in objects
is that your static code doesn't know what these new fields are.
Imagine how they play with a search function? Building a
SQL query against a boolean value in the UDF1 column means
translating the boolean result into its string equivalent
and doing a fill scan text search. Slow....
You really want database tables to be defined as fully as
possible as soon as possible. UDFs are a (sometimes necessary)
cost of failure!

I'm glad I think to ask these questions! And that you folks are here
to answer them!!

boB
_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor