SQLAlchemy Adds Objects to Collections Automatically
So I’ve been using SQLAlchemy for a while for Chukchi. Here’s a WTF I’ve got recently.
Imagine you have a basic foreign key relationship in your SQLAlchemy declarative models. For example, let’s take one from Chukchi’s models.py lines 83-121:
class Entry(Base):
id = Column(Integer, primary_key=True)
# <...>
class Content(Base):
id = Column(Integer, primary_key=True)
entry_id = Column(Integer, ForeignKey(Entry.id), nullable=False)
# <...>
entry = relationship(Entry, backref='content')
(note that I’ve skipped irrelevant parts)
Now let’s import and play with it for a bit:
>>> from chukchi.db.models import * >>> from chukchi.db import Session >>> db = Session() >>> entry = db.query(Entry).order_by(Entry.id.desc()).first() INFO sqlalchemy.engine.base.Engine SELECT <...> FROM entry ORDER BY entry.id DESC LIMIT %(param_1)s INFO sqlalchemy.engine.base.Engine {'param_1': 1}
(note that I’ve got echo=True enabled so that we see all the SQL)
Now let’s see what child objects are there in the database:
>>> print ", ".join(str(c.id) for c in entry.content) INFO sqlalchemy.engine.base.Engine SELECT <...> FROM content WHERE %(param_1)s = content.entry_id INFO sqlalchemy.engine.base.Engine {'param_1': 2662} 25223, 25224
Okay, so we have an Entry object and two Content objects that refer to it.
Now let’s create a new Content object:
>>> c = Content() >>>
As you probably know, SQLAlchemy executes write queries whenever the session is flushed. But if we flush the session now, nothing happens:
>>> db.flush() >>>
That’s because the state of our new object is “transient”. As long as we don’t add it explicitely to the Session, it won’t be inserted into the database.
Now let’s create a Content object with some data filled:
>>> c = Content(entry=entry, ... type='text/plain', ... hash="doesn't matter", ... data='')
Now what would happen if we flushed the session now? Nothing, right? Wrong!
INFO sqlalchemy.engine.base.Engine INSERT INTO content (<...>) VALUES (<...>) RETURNING content.id INFO sqlalchemy.engine.base.Engine {'hash': "doesn't matter", 'entry_id': 2961, 'type': 'text/plain', 'data': '', <...>}
This happens because when you initialize a relationship with a persistent object (that is, an object within a Session), it gets added to its parent object’s backref collection:
>>> print ", ".join(str(c.id) for c in entry.content) 25223, 25224, 25225 c = Content(entry=entry) >>> print ", ".join(str(c.id) for c in entry.content) 25223, 25224, 25225, None
(new Content object having no ID yet)
Interestingly enough, this doesn’t happen, if you initialize the table field, instead of a relationship:
>>> c = Content(entry_id=2961) >>> print ", ".join(str(c.id) for c in entry.content) 25223, 25224, 25225, None
So watch out if you initialize a relationship attribute: you might get some new unexpected objects in your database.
0 comments »
