Automap and naming of relationship attributes

[Adrian Robert]

Hi,

I'm new to sqlalchemy though I've used other ORMs (e.g. Hibernate) before, and I'm trying to use the new automap feature.

However it seems to be using the foreign table name rather than a suffixed version of the column name when naming relationships.  Is there a reason for doing it this way?

SQL (using postgres 9.3, psycopg2):

CREATE TABLE room (

       id serial NOT NULL PRIMARY KEY,

       name varchar(80) NOT NULL,

       left_neighbor integer REFERENCES room(id),

       right_neighbor integer REFERENCES room(id)

);

CREATE TABLE location (

       id serial NOT NULL PRIMARY KEY,

       room integer NOT NULL REFERENCES room(id),

       description varchar(200) NOT NULL

);

This gave me on automap_base().prepare():

sqlalchemy.exc.ArgumentError: WARNING: when configuring property 'room' on Mapper|location|location, column 'room' conflicts with property '<RelationshipProperty at 0x10267ba10; room>'. 

After temporarily renaming the 'location.room' column to 'roomx', it loaded and I found I could see a room attribute on the auto-created Location class.  However with the Room class itself there is a problem, since I have two self references.  This appears to create room and room_collection attributes.  I haven't experimented to see which/how these get filled in for instances, but clearly the desired distinction between 'left' and 'right' neighbors is lost.

If the naming needs to be done this way for general consistency with how things are done elsewhere in sqlalchemy, is there any way I can override it, preferably by monkeying on the DB side (explicit naming of foreign key constraints, etc.), not manually tweaking on the python side (which would defeat the purpose of using automap)?

[Michael Bayer]

On Feb 1, 2014, at 11:50 AM, Adrian Robert <adrian....@gmail.com> wrote:

> Hi,
>
> I'm new to sqlalchemy though I've used other ORMs (e.g. Hibernate) before, and I'm trying to use the new automap feature.
>
> However it seems to be using the foreign table name rather than a suffixed version of the column name when naming relationships. Is there a reason for doing it this way?

this is only a default. All the naming schemes are configurable as documented at http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html#overriding-naming-schemes . In this case you’d be doing the name_for_scalar_relationship callable: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html#sqlalchemy.ext.automap.name_for_scalar_relationship

> If the naming needs to be done this way for general consistency with how things are done elsewhere in sqlalchemy,

SQLAlchemy avoids automatic naming schemes like the plague, and there are very few places they are present. This extension is clearly one of them as it is necessary, but it’s entirely open ended.

[Adrian Robert]

Thanks, that works beautifully.

I had noticed name_for_scalar_relationship parameter but I guess wasn't confident enough that I understood what was going on to try it.  :-[

[Adrian Robert]

One other point, I was trying out the dogpile cache example and ran into (after I stuck a ".encode(‘utf-8’)” into the key mangler since I’m using Python-3 and pylibmc):

_pickle.PicklingError: Can't pickle <class 'sqlalchemy.ext.automap.Person'>: attribute lookup sqlalchemy.ext.automap.Person failed

This was fixed by a hack

sqlalchemy.ext.automap.__dict__[cls.__name__] = cls

run over all the automap-created classes. It might be I’m only having to do this because I’m doing something wrong elsewhere, but I just thought I’d mention it in case it comes up for someone.

On 2014.2.2, at 14:22, Adrian Robert <adrian....@gmail.com> wrote:

> Thanks, that works beautifully.
>
> I had noticed name_for_scalar_relationship parameter but I guess wasn't confident enough that I understood what was going on to try it. :-[
>
>

> --
> You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/p6YkPuCs_Ks/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.

[Michael Bayer]

Python pickle can’t pickle class instances where the class isn't locatable as module-level imports. As automap necessarily creates classes on the fly, these classes aren’t part of any module. to have them part of a module you’d want to use an event to place them in the namespace of one of your own modules, or you can implement a custom `__reduce__()` method on them (see the Python docs for __reduce__()).

a good event to use here might be instrument_class:

http://docs.sqlalchemy.org/en/rel_0_9/orm/events.html#sqlalchemy.orm.events.MapperEvents.instrument_class

> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

[Adrian Robert]

Well, using the mapper event would be nicer, but in any case I was already iterating over Base.classes and adding them to my own module’s namespace like so:

globals()[cls.__name__] = cls

It works for the rest of my application being able to see the classes by importing the module, but apparently not for this. I’m not really expert at Python class and namespace innards, but from the error message as well as the default str() output it seems the automap-generated classes considers themselves to be in the sqlalchemy.ext.automap module but are not registered in that namespace.

Is there a way to tell the classes to use a different namespace from an instrument_class handler? (And incidentally I’m already using my own base class through automap_base(declarative_base(cls=…)) but that doesn’t make any difference.)

[Michael Bayer]

On Feb 6, 2014, at 1:56 PM, Adrian Robert <adrian....@gmail.com> wrote:

> Well, using the mapper event would be nicer, but in any case I was already iterating over Base.classes and adding them to my own module's namespace like so:
>
> globals()[cls.__name__] = cls
>
> It works for the rest of my application being able to see the classes by importing the module, but apparently not for this. I'm not really expert at Python class and namespace innards, but from the error message as well as the default str() output it seems the automap-generated classes considers themselves to be in the sqlalchemy.ext.automap module but are not registered in that namespace.
>

> Is there a way to tell the classes to use a different namespace from an instrument_class handler? (And incidentally I'm already using my own base class through automap_base(declarative_base(cls=...)) but that doesn't make any difference.)

I’m seeing that and working with a rudimental example I’m not seeing a solution to it. You can as automap suggests create the classes explicitly.