CODEWITHSUNDEEP
pythonsessionsqlalchemy
jcalero
jcalero

Reputation: 73

Instantiating object automatically adds to SQLAlchemy Session. Why?

From my understanding of SQLAlchemy, in order to add a model to a session, I need to call session.add(obj). However, for some reason, in my code, SQLAlchemy seems to do this automatically.

Why is it doing this, and how can I stop it? Am I approaching session in the correct way?

example

>>> from database import Session as db
>>> import clients
>>> from instances import Instance
>>> from uuid import uuid4
>>> len(db.query(Instance).all())
>>> 0 # Note, no instances in database/session
>>> i = Instance(str(uuid4()), clients.get_by_code('AAA001'), [str(uuid4())])
>>> len(db.query(Instance).all())
>>> 1 # Why?? I never called db.add(i)!

database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base

import config

Base = declarative_base()

class Database():

    def __init__(self):
        db_url = 'postgresql://{:s}:{:s}@{:s}:{}/{:s}'.format(
            config.database['user'],
            config.database['password'],
            config.database['host'],
            config.database['port'],
            config.database['dbname']
        )
        self.engine = create_engine(db_url)
        session_factory = sessionmaker(bind=self.engine)
        self.session = scoped_session(session_factory)

Database = Database()

Session = Database.session

instance.py

from sqlalchemy import Column, Text, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.dialects.postgresql import UUID, ARRAY

import database

Base = database.Base

class Instance(Base):
    __tablename__ = 'instances'

    uuid = Column(UUID, primary_key=True)
    client_code = Column(
        Text, ForeignKey('clients.code', ondelete='CASCADE'), nullable=False)
    mac_addresses = Column(ARRAY(Text, as_tuple=True),
                           primary_key=True)

    client = relationship("Client", back_populates="instances")

    def __init__(self, uuid, client, mac_addresses):
        self.uuid = uuid
        self.client = client
        self.mac_addresses = tuple(mac_addresses)

client.py

from sqlalchemy import Column, Text
from sqlalchemy.orm import relationship

import database
from database import Session as db

Base = database.Base

class Client(Base):
    __tablename__ = 'clients'

    code = Column(Text, primary_key=True)
    name = Column(Text)

    instances = relationship("Instance", back_populates='client')

    def __init__(self, code, name=None):
        self.code = code
        self.name = name

def get_by_code(code):
   client = db.query(Client).filter(Client.code == code).first()
   return client

Upvotes: 7

Views: 2721

Answers (1)

Erik Oosterwaal
Erik Oosterwaal

Reputation: 4374

When you create a SQLAlchemy object and link it directly to another SQLAlchemy object, both objects end up in the session.

The reason is that SQLAlchemy needs to make sure you can query these objects. Take, for example, a user with addresses.

If you create a user in code, with an address, both the user and the address end up in the session, because the address is linked to the user and SQLAlchemy needs to make sure you can query all addresses of a user using user.addresses.all().

In that case all (possibly) existing addresses need to be fetched, as well as the new address you just added. For that purpose the newly added address needs to be saved in the database.
To prevent this from happening (for example if you only need objects to just calculate with), you can link the objects with their IDs/Foreign Keys:

address.user_id = user.user_id

However, if you do this, you won't be able to access the SQLAlchemy properties anymore. So user.addresses or address.user will no longer yield results.

The reverse is also true; I asked a question myself a while back why linking two objects by ID will not result in SQLAlchemy linking these objects in the ORM:

relevant stackoverflow question

another description of this behavior

Upvotes: 7

Related Questions