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

[Alan Gauld]

On 16/08/15 23:29, boB Stepp wrote:

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

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

> 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