miso miso - 1 month ago 6
Python Question

Why SQLAlchemy send extra SELECTs when accessing a persisted model property

Given a simple declarative based class;

class Entity(db.Model):

__tablename__ = 'brand'

id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), nullable=False)


And the next script

entity = Entity()
entity.name = 'random name'
db.session.add(entity)
db.session.commit()

# Just by accessing the property name of the created object a
# SELECT statement is sent to the database.

print entity.name


When I enable
echo
mode in SQLAlchemy, I can see in the terminal the
INSERT
statement and an extra
SELECT
just when I access a property (column) of the model (table row).

If I don't access to any property, the query is not created.

What is the reason for that behavior? In this basic example, We already have the value of the
name
property assigned to the object. So, Why is needed an extra query? It to secure an up to date value, or something like that?

Answer

By default, SQLAlchemy expires objects in the session when you commit. This is controlled via the expire_on_commit parameter.

The reasoning behind this is that the row behind the instance could have been modified outside of the transaction, so if you are not careful you could run into data races, but if you know what you are doing you can safely turn it off.

Comments