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

[Mark Lawrence]

On 01/08/2015 17:34, boB Stepp wrote:
> I have never written programs to interact with a db.  I have never written an OO program.  So this is getting interesting rather quickly!
>
> As I continue to ponder my project design, I see many of the classes I wish to create map naturally to db tables.  For instance the class Student can potentially have many data attributes, which fit naturally into its own db table.
>
> My current thoughts are that I have two main ways of approaching this:
>
> 1)  Create my various objects normally, but have their data attributes fetched through some sort of db manager class I would design.

This is the interface that I use on my own personal, mission critical, 
cashflow forecast.

con = sqlite3.connect(sqliteFileName, 
detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
con.row_factory = sqlite3.Row

What this gives you is documented here 
https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory 
and here https://docs.python.org/3/library/sqlite3.html#sqlite3.Row

I also create views in the database and select from them rather than do 
the join within code.

All very simple but very effective.

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

Take a look at the comparison here 
http://www.pythoncentral.io/sqlalchemy-vs-orms/  It mentions peewee 
which I played with in my cashflow forecast but found it to be overkill 
for my simple needs. I've also heard good things about Storm and 
PonyORM.  SQLAlchemy and SQLObject are the big names, the rest I can't 
comment on.

>
> Both routes will be quite educational for me.  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.
>
> Thoughts?

Start prototyping with my simple approach.  If that works for you just 
stick with it.  If not try one of the simpler ORMs like peewee or 
PonyORM.  Stick with it if it's good enough, else go for one of the 
heavyweights.  I prefer this approach as it's fairly easy in Python to 
throw something away and have another go, and I like to keep things as 
simple and straight forward as possible.  I'm sure others would take the 
opposite approach and start with a heavyweight.  For you I'd suggest the 
best path to take depends on the number of tables you'll actually be 
creating.  If it's small cutting over from SQLite to some other db 
should be relatively easy.  If it's medium to large perhaps you're 
better off starting off with the heavyweight of your choice at the 
start.  I do know one thing, you won't find out until you try it :)

> --
> boB

-- 
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.

Mark Lawrence