Re: [Tutor] How to test my code's interactions with SQLite db?
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 ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test my code's interactions with SQLite db?
On Sun, Aug 16, 2015 at 01:18:06AM -0500, boB Stepp wrote: 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? *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. -- Steve ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
[Tutor] How to test my code's interactions with SQLite db?
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! -- boB ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test my code's interactions with SQLite db?
boB Stepp robertvst...@gmail.com writes: 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 - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test my code's interactions with SQLite db?
On 16/08/15 09:10, Ben Finney wrote: 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. 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 - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test my code's interactions with SQLite db?
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 - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test my code's interactions with SQLite db?
On Sun, Aug 16, 2015 at 4:03 AM, Steven D'Aprano st...@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 ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test my code's interactions with SQLite db?
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 ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test my code's interactions with SQLite db?
On 17/08/15 00:52, boB Stepp wrote: 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... 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. 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. 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. 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! 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! 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 - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test my code's interactions with SQLite db?
On Sun, Aug 16, 2015 at 7:36 PM, Danny Yoo d...@hashcollision.org wrote: 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! 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! -- boB ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test my code's interactions with SQLite db?
On Sun, Aug 16, 2015 at 6:04 PM, Alan Gauld alan.ga...@btinternet.com wrote: 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. I had just finished installing and testing the installation just before your email arrived. Very easy to do! 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. 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. 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. 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 - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test my code's interactions with SQLite db?
On Sun, Aug 16, 2015 at 7:55 PM, Alan Gauld alan.ga...@btinternet.com wrote: On 17/08/15 00:52, boB Stepp wrote: 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. 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). Hmm. This is much more complex than I imagined, and I'm cynical by nature. 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. 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! [...] 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 - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
