Are consecutive query.get calls supposed to send a single SELECT query?

[Etienne Rouxel]

Hello

In my program, I was trying to guess why so many SQL queries were sent while some could have been avoided with the help of the identity map.

So, I reduced my program to what is below and wrote 3 times the same query.get call and 3 SQL queries were sent to the database server.

Why does SQLAlchemy send 3 times the SQL while it could have done it only 1 time for the first call and use the identity map for the 2 last calls?

from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

_descriptiontype_table = Table('descriptiontype', Base.metadata,

        Column('id', Integer, primary_key=True),

        Column('refno', Integer),

        Column('sortindex', Integer),

        Column('designation', String),

        schema='botany'

    )

class Descriptiontype(Base):

    __table__ = _descriptiontype_table

if __name__ == '__main__':

    engine = create_engine('postgresql://user@localhost:5432/mydatabase')

    Session = sessionmaker(bind=engine)

    session = Session()

    session.query(Descriptiontype).get(-2147483648)

    session.query(Descriptiontype).get(-2147483648)

    session.query(Descriptiontype).get(-2147483648)

[Charlie Clark]

Am 21.05.2013, 17:31 Uhr, schrieb Etienne Rouxel
<rouxel....@gmail.com>:

> if __name__ == '__main__':
> engine = create_engine('postgresql://user@localhost:5432/mydatabase')
> Session = sessionmaker(bind=engine)
> session = Session()
> session.query(Descriptiontype).get(-2147483648)
> session.query(Descriptiontype).get(-2147483648)
> session.query(Descriptiontype).get(-2147483648)

Each call to session.query() is creating a new query object, thus, a new
query will run on the DB.

Try:
q = session.query(Descriptiontype)
q.get(-24…)

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Simon King]

I suspect each object is being garbage collected before the next query
runs, because you aren't saving a reference to it. Try something like
this:

d1 = session.query(Descriptiontype).get(-2147483648)
d2 = session.query(Descriptiontype).get(-2147483648)
d3 = session.query(Descriptiontype).get(-2147483648)
assert (d1 is d2) and (d2 is d3)

Simon

[Etienne Rouxel]

@Charlie Clark:

In the documentation (http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions see "Is the Session a cache ?"), it is mentioned that it does not necessarily performs a query.

@Simon King:

I guess you're the King indeed, I tried:

1. to assign the result of the 3 queries to 3 different variables

2. to assign the result of the 3 queries to the same variable

3. to assign the result of the first query to a variable and not to assign the result of the 2 last queries

In all these cases, only 1 SQL query were sent so your suspicion looks correct.

Thank you both.

[Michael Bayer]

if I might make a suggestion -

why are negative numbers being used as surrogate primary key values ?

I don't believe there's any restriction in SQLAlchemy as to this, but it seems a bit suspect.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Etienne Rouxel]

@Michael Bayer:

The database type used is "integer" with a range from -2147483648 to +2147483647 according to the documentation (http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html).

I just started at the beginning of the range, that is why I have negative numbers.

I performed all the tests mentioned earlier by using positive numbers and I have the same results.

When you say that it is seems a bit suspect, are you talking about my problem in particular or about using negative number in general?

Le mardi 21 mai 2013 17:31:10 UTC+2, Etienne Rouxel a écrit :

[Michael Bayer]

OK if positive numbers do it too, then that's not the issue.

Here is an example script illustrating how session.query(X).get(num) will not emit subsequent SQL if the object is strongly referenced already, in this case it drops the test table to prove it's point, but SQL echoing could be used as well.  Try it on your system and see if you can modify it to reproduce your results.

from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):

    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)

e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)

sess = Session(e)

sess.add(A(id=1))

sess.commit()

sess.close()

# this line fails to hold onto "a1",

# subsequent queries fail

# sess.query(A).get(1)

# this line holds on, there's no issue

a1 = sess.query(A).get(1)

sess.execute("drop table a")

a2 = sess.query(A).get(1)

a3 = sess.query(A).get(1)

assert a1 is a2 is a3

[Etienne Rouxel]

@Michael Bayer:

I successfully ran your script and like my previous tests using variables to stored the results of the queries : only a single SQL query was sent.

Le mardi 21 mai 2013 17:31:10 UTC+2, Etienne Rouxel a écrit :

[Etienne Rouxel]

My real application seems to share the cause for why the identity map is not used. Indeed, I have a method that encapsulate the building of a form using queries. Once the execution goes out of this method, the instances are cleared out from the identity map.

I guess this is a very common concern, is there any appropriate design pattern to fully take advantage of the identity map?

Le mardi 21 mai 2013 17:31:10 UTC+2, Etienne Rouxel a écrit :

[Michael Bayer]

usually some kind of collection of objects you want to keep is maintained, it can be passed throughout those methods, or it can be attached to the Session.   It's a tradeoff between which objects you'd like to keep around between calls to things, and which you'd like to be garbage collected.

[Etienne Rouxel]

I would like that no garbage collection occurs within a session, is there a way to configure that behavior?

Le mardi 21 mai 2013 17:31:10 UTC+2, Etienne Rouxel a écrit :

[Michael Bayer]

there's a specific way that I've been trying to deprecate for years, sure, send weak_identity_map=False to the Session constructor.   

If it were me, I'd use an on_load event and just stick each object into a dictionary associated with the Session.