CODEWITHSUNDEEP
flasksqlalchemyflask-sqlalchemy
Nik Barres
Nik Barres

Reputation: 543

SQLAlchemy: Specifying session to use for model

I am using Flask-SQLAlchemy and I need to create a session without auto-flushing for an operation. However, the default scoped session that is created by Flask-SQLAlchemy which is accessed using db.session has auto-flushing turned on.

I'm doing bulk updates for 100k rows, and the auto-flushing is causing severe performance issues.

I tried creating a new session with auto-flushing turned off using:

from flask_sqlalchemy import SignallingSession
db_session = SignallingSession(db, autoflush=False)

And then I tried creating a new row using:

from flask_sqlalchemy import SQLAlchemy, BaseQuery

db = SQLAlchemy(app)

class Tool(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(1024), nullable=True)

add_tool = Tool()
add_tool.title = title

db_session.add(add_tool)

However, doing this throws the following exception:

Traceback (most recent call last):
  File "app", line 182, in <module>
called for last item
    import_tools('x.json', 1)
  File "app", line 173, in import_tools
    x.add_to_database()
  File "app", line 126, in add_to_database
    title=metadata['title'], commit=True)
  File "app", line 150, in transfer
    self.db_session.add(import_tool)
  File ".virtualenvs/project/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1492, in add
    self._save_or_update_state(state)
  File ".virtualenvs/project/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1504, in _save_or_update_state
    self._save_or_update_impl(state)
  File ".virtualenvs/project/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1759, in _save_or_update_impl
    self._save_impl(state)
  File ".virtualenvs/project/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1731, in _save_impl
    self._attach(state)
  File ".virtualenvs/project/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1849, in _attach
    state.session_id, self.hash_key))
sqlalchemy.exc.InvalidRequestError: Object '<Tool at 0x11361f4d0>' is already attached to session '1' (this is '2')

What I believe is happening is that when the object is created, it is attached to the global scoped session that is provided by Flask-SQLAlchemy, while I want it to be attached to the session that I created. I tried looking through the code and documentation for Flask-SQLAlchemy and SQLAlchemy, but I couldn't find either the source of the problem or the solution to it.

Is there some way I can specify which session to use when the object is created using the model class? Or is there something totally different that I am missing which would be causing this problem?

Upvotes: 3

Views: 2455

Answers (1)

dirn
dirn

Reputation: 20709

SQLAlchemy sessions provide a no_autoflush context manager. This will suspend any flushes until after you exit the block.

model1 = Model(name='spam')
db.session.add(model1)  # This will flush

with db.session.no_autoflush:
    model2 = Model()
    db.session.add(model2)  # This will not

    model2.name = 'eggs'

db.session.commit()

Upvotes: 6

Related Questions