[Tutor] How to write database-agnostic python code? (Is this even possible?)

[Alan Gauld]

On 14/09/17 04:40, boB Stepp wrote:

>> mapping your logic layer object models to underlying
>> data tables.
> 
> My initial thoughts here are that typically a particular class would
> map to a particular table and that each object instance would
> correspond to a row in said table.  Is this typically how this is
> done?

Simplistically yes, but it gets more tricky with inheritance.
Does the superclass data live in the same table as the subclass
data or is there a separate table for the superclass?

The first solution implies a lot of duplication of fields
across tables and can also lead to problems if you are
saving changes to a list of superclass references using
class methods (but hopefully you would be using polymorphic
methods which will solve the problem for you!)

More seriously there is a problem when you want a query
that operates at the superclass level - all red cars for example.
You now have to query all tables representing subclasses of car
- and modify the query code every time you create a new subclass...

The second solution requires links between child and parent
tables and the queries will need to navigate this link for
any fields used within the query that exist in the superclass.
This can be a significant overhead, especially on deep
inheritance trees.

There is no simple solution and it depends on the nature of your
application which evil is worst. It's a fundamental disjoint
between OOP and the relational model. Many RDBMS now have
mechanisms to assist in this by, for example creating views
that join all superclass tables together into a virtual
single table.

I don't think SQLite has anything automatic yet, you need
to decide your solution yourself - which for a small dataset
might even involve instantiating all objects and doing the
query on the in-memory objects!. (itertools is your friend!)

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