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

[boB Stepp]

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.

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

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?


--
boB
_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Alan Gauld]

Post by boB Stepp
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...

Post by boB Stepp
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.

Post by boB Stepp
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.

Post by boB Stepp
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


_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Mark Lawrence]

Post by boB Stepp
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.
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.

Post by boB Stepp
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.

Post by boB Stepp
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 :)

Post by boB Stepp
--
boB

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

Mark Lawrence

_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor