Is this a good approach to avoid using SQLAlchemy/SQLObject?

Question

Rather than use an ORM, I am considering the following approach in Python and MySQL with no ORM (SQLObject/SQLAlchemy). I would like to get some feedback on whether this seems likely to have any negative long-term consequences since in the short-term view it seems fine from what I can tell.

Rather than translate a row from the database into an object:

• each table is represented by a class
• a row is retrieved as a dict

• an object representing a cursor provides access to a table like so:

  cursor.mytable.get_by_ids(low, high)

• removing means setting the time_of_removal to the current time

So essentially this does away with the need for an ORM since each table has a class to represent it and within that class, a separate dict represents each row.

Type mapping is trivial because each dict (row) being a first class object in python/blub allows you to know the class of the object and, besides, the low-level database library in Python handles the conversion of types at the field level into their appropriate application-level types.

If you see any potential problems with going down this road, please let me know. Thanks.

Answer 1

That doesn't do away with the need for an ORM. That is an ORM. In which case, why reinvent the wheel?

Is there a compelling reason you're trying to avoid using an established ORM?

Answer 2

You will still be using SQLAlchemy. ResultProxy is actually a dictionary once you go for .fetchmany() or similar.

Use SQLAlchemy as a tool that makes managing connections easier, as well as executing statements. Documentation is pretty much separated in sections, so you will be reading just the part that you need.

Answer 3

web.py has in a decent db abstraction too (not an ORM). Queries are written in SQL (not specific to any rdbms), but your code remains compatible with any of the supported dbs (sqlite, mysql, postresql, and others).

from http://webpy.org/cookbook/select:

myvar = dict(name="Bob")
results = db.select('mytable', myvar, where="name = $name")