[Tutor] How to design object interactions with an SQLite db?

[Alan Gauld]

On 01/08/15 17:34, boB Stepp wrote:
> I have never written programs to interact with a db.  I have never written an OO program.

The mapping opf OOP to Relational DB is one of the on going debates
in OOP land, and has been since I started with OOP in 1984...

> 1)  Create my various objects normally, but have their data attributes
 > fetched through some sort of db manager class I would design.

Personally I tend to create a load() method that is used like a 
constructor but fetches the data from the database

myObj = MyClass().load(ID)

Where load() returns self if successful.
Alternatively in Python you could define the ID as a parameter of init 
with a None default

def __init__(self,att1=None,att2=SomeDefault,...,ID=None):
     if ID
        self.load(ID)
     else:
        self.att1 = att1 # etc...

Its conceptually simple and gives you full control of the SQL, but 
things like inheritance can get tricky.

> 2)  Use an ORM (Object-Relational Manager) such as SQLAlchemy to manage
 >     interactions between my objects and the SQLite db.

This is ultimately the best solution since a lot of the hard work has 
been done for you and it can handle (I assume) inheritance etc. But
it's yet another framework to learn.

> Option (2), if I am understanding things correctly, would be more
 > likely to make it relatively easy to change from SQLite to a more
> sophisticated server-based db in the future incarnations of
 > this project.

Possibly, although IMHO its rarely seamless. But YMMV :-)

Equally, its rarely a huge job, even SQLite to Oracle say,
is not a massive hit - especially if you know which server
you will aim for because SQLite SQL has lots of options
for types that are compatible with various servers.
So strings can be represented as TEXT, VARCHAR(N), CHARACTER(N) NCHAR(N) 
etc depending on what server database you are trying
to emulate(or are accustomed to. Similarly with integers
(INT, INTEGER, INT2, etc). Look up Affinity types in the
SQLite docs.

http://sqlite.org/datatype3.html#affinity

The more 'clever' stuff you put in the harder the translation.
Stick to standard SQL and it should be fairly painless.

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