CODEWITHSUNDEEP
python-2.7sqlalchemy
John Kerfoot
John Kerfoot

Reputation: 23

Defining ORM relationships in sqlalchemy

I'm diving head-first into sqlalchemy ORM and am trying to understand relationships between objects.

I've created 3 classes that represent log files (Log), records parsed from the log files (LogRecord) and users (User) that each log record belong to.

Here's how I defined the classes:

from sqlalchemy import Table, Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import random

engine = create_engine('sqlite://')
# Create the session class
Session = sessionmaker(bind=engine)
# Create the session
session = Session()

Base = declarative_base()

class Log(Base):

    __tablename__ = 'logs'

    id = Column(Integer, primary_key=True, unique=True)
    log_name = Column(String, unique=True)
    records = relationship("LogRecord", backref="logs")

    def __repr__(self):
        return '<Log(log_name={:s}, id={:d}>'.format(
            self.log_name, self.id)

class LogRecord(Base):

    __tablename__ = 'log_records'

    id = Column(Integer, primary_key=True, unique=True)
    record_name = Column(String)
    log_id = Column(Integer, ForeignKey('logs.id'))
    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship("User", backref="log_records")

    def __repr__(self):
        return '<LogRecord(record_name={:s}, id={:d}>'.format(
            self.record_name,
            self.id)

class User(Base):

    __tablename__ = 'user'

    id = Column(Integer, primary_key=True, unique=True)
    name = Column(String, unique=True)

    def __repr__(self):
        return '<User(name={:s}, id={:d}>'.format(self.name, self.id)

And then populated them with some phony data:

Base.metadata.create_all(engine)

users = ['John', 'Alex', 'Nikki']

# Add 10 log files to Log
for x in range(0,10):
    log = Log(log_name='log{:d}.txt'.format(x))
    session.add(log)

# Add users
for user in users:
    session.add(User(name=user))

# Select all users
users = session.query(User).all()

# Add log records
for log in session.query(Log):
    # Assign a random user to each log record
    user = users[int(random.random() * len(users))]
    for x in range(0,10):
        # Assign a random user to each log record
        user = users[int(random.random() * len(users))]
        r = LogRecord(record_name='{:s}-{:d}'.format(log.log_name, int(random.random()*100)),log_id=log.id,user=user)

        session.add(r)

Each log file can contain records from one or more users. I'd like to get a distinct list of log files which contain at least one record from a specified user. I tried:

In [8]: user = session.query(User).filter(User.name == 'John').one()

In [9]: user
Out[9]: <User(name=John, id=1>

and then tried to find the logs using:

In [10]: session.query(Log).filter(Log.records.user == user).all()

But got the following:

session.query(Log).filter(Log.records.user == user).distinct().all()
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-13-d6d323e178f8> in <module>()
----> 1 session.query(Log).filter(Log.records.user == user).distinct().all()

/Users/kerfoot/code/venvs/sqlalchemy/lib/python2.7/sitepackages/sqlalchemy/orm/attributes.pyc in __getattr__(self, key)
192                     type(self.comparator).__name__,
193                     self,
--> 194                     key)
195             )
196 

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Log.records has an attribute 'user'

Not sure if there's something wrong with my class defs or the query itself.

Any help much appreciated!

Upvotes: 2

Views: 106

Answers (1)

van
van

Reputation: 77082

You cannot make filters on the properties of the properties, or simply put: you are not able to have more than one dot in the expression: Log.records.user does not work.

Option-1: You can do this the same way you would do it in the SQL:

session.query(Log).join(LogRecord, Log.records).filter(LogRecord.user == user).all()

and sqlalchemy is smart enough to return you only the list of Log instances without any duplication (even though SQL statement might result in repetition).

Option-2: Another way is to use any(...), which is logically cleaner:

session.query(Log).filter(Log.records.any(LogRecord.user == user)).all()

Transcript:
-> get all Log instances,
-> which contains at least one LogRecord,
-> that belongs to given user.

Upvotes: 1

Related Questions