On Oct 5, 2011, at 10:13 PM, Simon Haines wrote:
> I'm fairly new to SQLAlchemy so I want to double-check this before filing
> what I think is a bug against SQLAlchemy 0.7.
>
> Consider a plain, declarative-mapped object:
>
> class Table(Base):
> __tablename__ = 'table'
> id = Column(Integer, primary_key=True, nullable=False)
>
> Now consider code to retrieve this item:
>
> function test_identity_map():
> db=Session() # scoped_session(sessionmaker(...))
> for x in range(5):
> print "%d: %s" % (x, db.query(Table).get(1))
>
> If I create a MSSQL engine with a connection string prefix of
> 'mssql+pyodbc://', set 'echo' to True, populate the table with a single item
> with an id of '1' and call the test function, five separate SQL statements
> are echoed to the console. However, if I repeat the process using the SQLite
> in-memory engine ('sqlite:///:memory:'), only one SQL statement is echoed to
> the console.
>
> I believe the mssql/pyodbc engine is not caching the item in the identity map.
The implementation for the identity map is agnostic of backend.
There are two things that come to mind that could specifically cause this
behavior.
One is, the identifier of "1" as an integer does not actually match the
datatype received back from the MSSQL database - such as if the database
returned a string "1", that would cause the get() above to not locate integer
"1" in the cache.
The other is, "x" is garbage collected in between each iteration of the loop,
and by the time get() is called again, the underlying state has fallen out of
the Session. The Session does not strongly reference items that have no
pending changes, but ultimately relies upon weakref callbacks to implement the
fact that "1" is no longer present in the identity map. The fact that
accessing MSSQL over PyODBC is orders of magnitude slower than a local SQLite
database may give it the time to garbage collect in one case that's not in the
other.
> Could it be that the behaviour of 'echo=True' when supplied to create_engine
> differs between engine types, such that SQL is always echoed for the mssql
> engine, and the item is retrieved from the identity map anyway
if you were doing filter_by(id=1).first(), then yes, but get() specifically
checks the cache first and will not emit a SELECT if the item is found.
> (I haven't checked whether the calls are actually emitted to the database,
> but the timing information leads me to believe they are)? Or could there be
> some other configuration setting I've overlooked? Could it be that I'm using
> FreeTDS on linux for the ODBC layer? Stores/retrievals otherwise work just
> fine. I've looked through the dialect code and can't find anything that might
> affect this. Am I missing something? I'm going to dig into the session
> identity map code now to see what I can find, but I'd appreciate it if anyone
> already knows where I'm going wrong.
in the echo=True, pay close attention to the formatting of the bound parameters
- setting echo='debug' will display result rows as they come in. Make sure the
"1" in the result row is of the same type as the one bound to the SELECT, and
also assign each "x" above to a local list.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
