Advice: Best practice for working with an existing database

[Peter Herndon]

Hi all,

I'm new to SQLAlchemy, and looking for some advice on how to approach working with an existing database. The database will not be managed by Python, and I will need to maintain whatever library I write to keep up with the occasional schema change. I am looking to write a more-or-less definitive database access layer for myself and others to use if and when we write applications in Python -- the company is not primarily a Python shop. 

What's my best approach for building this library? Should I use Declarative, or should I use classical mapping -- which would be better, easier to maintain, easier for others to use? Should I be writing out the classes, or should I rely on reflection? Speaking of reflection, I've been using it to analyze what SQLAlchemy thinks of the schema. For a given table created by reflection, I'm seeing _autoincrement_column, columns, foreign_keys, indexes, and primary_key -- are there other table attributes I should be inspecting?

Thanks for your advice!

---Peter

[Jason]

Hi Peter,

I think using a the declarative reflected style is a great way to integrate an existing database into your application. It sounds like you are doing this already, but in case you are not the reflection setup is documented at http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#using-reflection-with-declarative. It's very easy to have it populate all of your models with the correct table attributes using the DeferredReflection base class. Then you just have to make (almost empty) class definitions for each model. In theory you could even make it so that the table name is inferred from the name of the Model class, but in practice I found this to be annoying (I would constantly forget how the table names compared to the class names).

--

Jason

[Peter Herndon]

On Friday, March 22, 2013 9:31:59 AM UTC-4, Jason wrote:

Hi Peter,

I think using a the declarative reflected style is a great way to integrate an existing database into your application. It sounds like you are doing this already, but in case you are not the reflection setup is documented at http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#using-reflection-with-declarative. It's very easy to have it populate all of your models with the correct table attributes using the DeferredReflection base class. Then you just have to make (almost empty) class definitions for each model. In theory you could even make it so that the table name is inferred from the name of the Model class, but in practice I found this to be annoying (I would constantly forget how the table names compared to the class names).

--

Jason

Thanks, Jason! 

[Waldemar Osuch]

On Monday, March 18, 2013 3:25:36 PM UTC-6, Peter Herndon wrote:

Hi all,

I'm new to SQLAlchemy, and looking for some advice on how to approach working with an existing database.

My preferred approach is to use http://code.google.com/p/sqlautocode/ and generate a module describing the schema.
Then I tweak/cleanup/reformat the schema.py to my liking. For example I may use "key" argument in the Column definition to change the name or comment out add_date, add_user columns.
Since the databases I work with are fairly stable it is an acceptable tradeoff.

Although sqlautocode can produce the Declarative format I tend to stick with a separate schema.py and model.py defining classical mapping.  I have been using SQLAlchemy since before Declarative was available (started with 0.1 series) and the classical mapping feels more natural.  Also if I ever need to re-genrate schema.py my model/mapping is not affected.

Waldemar

[Roberto Guerra]

My preferred approach is to use http://code.google.com/p/sqlautocode/ and generate a module describing the schema.
Then I tweak/cleanup/reformat the schema.py to my liking. For example I may use "key" argument in the Column definition to change the name or comment out add_date, add_user columns.
Since the databases I work with are fairly stable it is an acceptable tradeoff.

Although sqlautocode can produce the Declarative format I tend to stick with a separate schema.py and model.py defining classical mapping.  I have been using SQLAlchemy since before Declarative was available (started with 0.1 series) and the classical mapping feels more natural.  Also if I ever need to re-genrate schema.py my model/mapping is not affected.

Waldemar

+10000. I prefer classical mapping also. Much cleaner. Keeps persistence and business logic separate.  

[Michael Bayer]

On Apr 6, 2013, at 11:42 AM, Roberto Guerra <uri...@gmail.com> wrote:

+10000. I prefer classical mapping also. Much cleaner. Keeps persistence and business logic separate.  

the reason I ended up not agreeing with this is because mapping applies instrumentation to a class.   Using a separate mapper means you might see this:

class MyBusinessObject(object):

    pass

then elsewhere, you see some code doing this:

my_object = get_some_business_object()  # my_object is a MyBusinessObject

print (my_object.first_name)

but what's "first_name"?   That field isn't present at all on the class def for MyBusinessObject, it was applied by mapper().   Is the fact that "my_object.first_name" exists a detail of persistence or of business logic?   It's in fact both.  But we don't define it twice.    So the "ideal" of separate persistence and business design is already out the window.

In the classic usage patterns with Hibernate, things weren't as ambiguous.  In Java, we'd always have this:

class MyBusinessObject {

    public String getFirstName() {

    }

   public void setFirstName(String name) {

   }

}

then a hibernate .xml file defines the mapping.    In that case, we have a total separation of persistence and class design.    But even Hibernate moved away from this, in modern use you'd often use JPA annotations to mark the persistence for "firstName" inline.

The other enormous win with declarative is the mixin stuff.  You can produce patterns like that with mapper() as well, but not nearly as easily.

[Roberto Guerra]

I preferred the old way of doing things in Hibernate. I still do it that way when I work with Java. Sprinkling annotations in code is not a best practice in my opinion. It ties you to a framework.

I don't really have an issue having all the field definitions in the mapper. I just make sure I write proper doc strings. It allows me to cleanly separate business logic from persistence details. I normally keep all my persistence details in a separate logic that gets injected at the boundaries if I need to do persistence. It also allows me to mix different databases and even mix rdbms with nosql if I the need arises. I also get the added benefit of having very fast unit tests. My tests for persistence are separate and don't interfere if all I'm adding is a new feature or algorithm.

Most of the time, I wrap the fields in @property anyway, so in the end the fields do end up in the object after all. It is just an old Java habit of mine to do that.