0

I have a table and populating it with object list then I need to use their IDs, but I am getting an

Instance <location at 0x457f3b0> is not bound to a Session; attribute refresh operation cannot proceed

error.

I am populating a list with objects and send it to a function to insert all at once. Then I try to use IDs.

Here is my insert all function:

def insertlocations(locationlist):
    session.add_all(locationlist)
    session.commit()
    session.close()

then I try to get IDs:

insertlocations(neighbourhoodlist)
session.flush(neighbourhoodlist)
for neighbourhood in neighbourhoodlist:
    print neighbourhood.locationid

Session is global by the way. Any further info needed?

The data are inserted, as I look in the MySQL table.

jonsca
  • 10,218
  • 26
  • 54
  • 62
vardars
  • 541
  • 1
  • 8
  • 20

1 Answers1

0

Most likely your problem is that you already close() the session in your insertlocations() function.

When you then access neighbourhood.locationid, the session is closed and thatneighbourhood object isn't bound to a session any more.

For example, this should work:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///example.db')
engine.echo = True
Base = declarative_base()

class Location(Base):
    __tablename__ = 'locations'

    locationid = Column(Integer, primary_key=True)
    name = Column(String)
    address = Column(String)

    def __init__(self, name, address):
        self.name = name
        self.address = address


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

def insertlocations(locationlist):
    session.add_all(locationlist)
    session.commit()


loc1 = Location('loc1', 'Foostreet 42')
loc2 = Location('loc2', 'Barstreet 27')

neighbourhoodlist = [loc1, loc2]

insertlocations(neighbourhoodlist)
for neighbourhood in neighbourhoodlist:
    print neighbourhood.locationid

session.close()
  • Move session.close() out of your function and do it after you're done using that session.
  • Ditch the session.flush(), it's not needed since you already commit the session when you add the objects.
Lukas Graf
  • 30,317
  • 8
  • 77
  • 92
  • maybe, but i can't leave sessions without closing, that causes many open connections. any suggestions? – vardars Oct 07 '12 at 11:39
  • @vardars Of course. Close them after you're done operating on them, for example in your case after your `for/print` loop. – Lukas Graf Oct 07 '12 at 11:41
  • thanks for your great example and help. Actually I was using insertlocations function in other cases, and these cases don't need to use ids, and until this case I was using it all over. so if I remove session.close() from insertlocations, I need to update all of that function calling codes. I decided to create another function that doesnt close session, and I will call it, or use add_all() directly. I am trying to learn by doing small examples, maybe in the future i will use these in a bigger project. Thanks again for your help, it is really appreciated. – vardars Oct 07 '12 at 11:57
  • 1
    @vardars If for some reason you want to close your session early, but still have read access to some objects' attributes, have a look at [`session.expunge()`](http://www.sqlalchemy.org/docs/orm/session.html#expunging). There's also a [answer on SO](http://stackoverflow.com/a/8254472/1599111) with an example of how to use it. – Lukas Graf Oct 07 '12 at 12:01
  • 2
    you should keep the Session open for a consistent span enclosing a full series of operations. This create/close pattern should be ideally established just once in your application as a framework for all operations. This is in contrast to having many ad-hoc/redundant open/close blocks spread throughout the application. Some background on this is available in a recently reworked documentation section: http://docs.sqlalchemy.org/en/latest/orm/session.html#session-frequently-asked-questions – zzzeek Oct 07 '12 at 23:28