CODEWITHSUNDEEP
pythonmysqlormsqlalchemy
lollercoaster
lollercoaster

Reputation: 16493

Retrieving ForeignKey mapped objects in Python with SqlAlchemy

I have an existing database that I'm trying to map into SqlAlchemy's ORM. I want it to just figure out the ForiegnKey relations that already exist in the database itself.

Here's the code I have so far:

from sqlalchemy import create_engine, MetaData, Table 
from sqlalchemy.orm import create_session
from sqlalchemy.ext.declarative import declarative_base

# connect to database and infer from structure
Base = declarative_base()
engine = create_engine("XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX")
metadata = MetaData(bind=engine)

class Club(Base):
    __table__ = Table('clubs', metadata, autoload=True)
    def __repr__(self):
        return "<Club: %s>" % (self.name,)

class Member(Base):
    __table__ = Table('members', metadata, autoload=True)
    def __repr__(self):
        return "<Member: %s of %d>" % (self.name, self.club_id)

Here's the SQL table dump:

CREATE TABLE `clubs` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(45) collate utf8_bin default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `members` (
  `id` int(11) NOT NULL auto_increment,
  `club_id` int(11) default NULL,
  `name` VARCHAR(100) default NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_members_club_idx` (`club_id`),
  CONSTRAINT `fk_members_club` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My problem is that in the Member __repr__ function, for example, instead of printing the club_id (which is useless to human), I'd like to print the Club name. Something like member.club.name would be ideal.

I couldn't find out how to do this on the SqlAlchemy docs unless I defined my own tables, not merely reflecting them in my code, which is what I'm doing here.

Upvotes: 1

Views: 123

Answers (1)

van
van

Reputation: 76962

Just change your Member class to look like below:

class Member(Base):
    __table__ = Table('members', metadata, autoload=True)
    club = relationship(Club, backref="members")
    def __repr__(self):
        return "<Member: %s of %s>" % (self.name, self.club.name)

The point being that the reflection (autoload) will not automatically create relationships between classes, so you have to define them explicitly.

Upvotes: 3

Related Questions