iBATIS for Python?

Question

At my current gig, we use iBATIS through Java to CRUD our databases. I like the abstract qualities of the tool, especially when working with legacy databases, as it doesn't impose its own syntax on you.

I'm looking for a Python analogue to this library, since the website only has Java/.NET/Ruby versions available. I don't want to have to switch to Jython if I don't need to.

Are there any other projects similar to iBATIS functionality out there for Python?

Answer 1

iBatis sequesters the SQL DML (or the definitions of the SQL) in an XML file. It specifically focuses on the mapping between the SQL and some object model defined elsewhere.

SQL Alchemy can do this -- but it isn't really a very complete solution. Like iBatis, you can merely have SQL table definitions and a mapping between the tables and Python class definitions.

What's more complete is to have a class definition that is also the SQL database definition. If the class definition generates the SQL Table DDL as well as the query and processing DML, that's much more complete.

I flip-flop between SQLAlchemy and the Django ORM. SQLAlchemy can be used in an iBatis like manner. But I prefer to make the object design central and leave the SQL implementation be derived from the objects by the toolset.

I use SQLAlchemy for large, batch, stand-alone projects. DB Loads, schema conversions, DW reporting and the like work out well. In these projects, the focus is on the relational view of the data, not the object model. The SQL that's generated may be moved into PL/SQL stored procedures, for example.

I use Django for web applications, exploiting its built-in ORM capabilities. You can, with a little work, segregate the Django ORM from the rest of the Django environment. You can provide global settings to bind your app to a specific database without using a separate settings module.

Django includes a number of common relationships (Foreign Key, Many-to-Many, One-to-One) for which it can manage the SQL implementation. It generates key and index definitions for the attached database.

If your problem is largely object-oriented, with the database being used for persistence, then the nearly transparent ORM layer of Django has advantages.

If your problem is largely relational, with the SQL processing central, then the capability of seeing the generated SQL in SQLAlchemy has advantages.