How to create a non-persistent Elixir/SQLAlchemy object?

Question

Hi,

because of legacy data which is not available in the database but some external files, I want to create a SQLAlchemy object which contains data read from the external files, but isn't written to the database if I execute session.flush()

My code looks like this:

try:
  return session.query(Phone).populate_existing().filter(Phone.mac == ident).one()
except:
  return self.createMockPhoneFromLicenseFile(ident)

def createMockPhoneFromLicenseFile(self, ident):
  # Some code to read necessary data from file deleted....
  phone = Phone()
  phone.mac = foo
  phone.data = bar
  phone.state = "Read from legacy file"
  phone.purchaseOrderPosition = self.getLegacyOrder(ident)
  # SQLAlchemy magic doesn't seem to work here, probably because we don't insert the created
  # phone object into the database. So we set the id fields manually.
  phone.order_id = phone.purchaseOrderPosition.order_id
  phone.order_position_id = phone.purchaseOrderPosition.order_position_id
  return phone 

Everything works fine except that on a session.flush() executed later in the application SQLAlchemy tries to write the created Phone object to the database (which fortunatly doesn't succeed, because phone.state is longer than the data type allows), which breaks the function which issues the flush.

Is there any way to prevent SQLAlchemy from trying to write such an object?

Ciao, Steffen

Update

While I didn't find anything on

using_mapper_options(save_on_init=False)

in the elixir documentation (maybe you can provide a link?), it seemed to me worth a try (I would have preferred a way to prevent a single instance from being written instead of the whole entity).

At first it seemed that the statement has no effect and I suspected my SQLAlchemy/Elixir versions to be too old, but then I found out that the connection to the PurchaseOrderPosition entity (which I didn't modify) made with

phone.purchaseOrderPosition = self.getLegacyOrder(ident) 

causes the phone object to be written again. If I remove the statement, everything seems to be fine.

Thanks for the help!

Answer 1

Well, sqlalchemy doesn't, by default.

Consider the following self-contained example code.

from sqlalchemy import Column, Integer, Unicode, create_engine
from sqlalchemy.orm import create_session
from sqlalchemy.ext.declarative import declarative_base

e = create_engine('sqlite://')
Base = declarative_base(bind=e)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(50))

# create the empty table and a session
Base.metadata.create_all()
s = create_session(bind=e, autoflush=False, autocommit=False)

# assert the table is empty
assert s.query(User).all() == []

# create a new User instance but don't save it to database:
u = User()
u.name = 'siebert'
# I could run s.add(u) here but I won't

s.flush()
s.commit()

# assert the table is still empty
assert s.query(User).all() == []

So I'm not sure what's implicity adding your instances to the session. Normally you have to manually call s.add(u) to make it go to the session. I'm not familiar with elixir so perhaps this is some elixir trickery... Maybe you could remove it from the session, by using session.expunge().

Answer 2

You need to do

import elixir
elixir.options_defaults['mapper_options'] = { 'save_on_init': False }

to prevent Entity instances which you instantiate being auto-added to the session. Ideally, this should be done as early as possible in your code. You can also do this on a per-entity basis, via using_mapper_options(save_on_init=False) - see the Elixir documentation for more details.

Update:

See this post on the Elixir mailing list indicating that this is the solution.

Also, as Ants Aasma points out, you can use cascade options on the Elixir relationship to set up cascade options in SQLAlchemy. See this page for more details.