are you using serializable isolation ? that would prevent an in-progress transaction from seeing this new row committed elsewhere.
other than that, the SELECT in the log will show you what rows it's selecting
and using echo='debug' will show the rows it finds on the way back.
On Oct 3, 2013, at 2:35 PM, Nathan Mailg <nathanma...@gmail.com> wrote:
> Sorry for this follow-up, but I'm really at a loss as to what to check next…
>
> What should I do if after I insert a new "Appl" row, the query we've worked
> on in this thread is *not* finding it, i.e. it's not being returned in the
> result set? I'm really surprised and thinking I must be missing something
> really basic?
>
> I've deleted the lastname, firstname indexes and re-created them, but the
> newly created row is still *not* being returned!
>
> Here's the sql insert from the SA log:
>
> 2013-10-03 12:26:37,638 INFO [sqlalchemy.engine.base.Engine][Dummy-3] BEGIN
> (implicit)
> 2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3] INSERT
> INTO appl (refid, appldate, lastname, firstname, cityid, cityid2) VALUES
> (nextval('appl_refid_seq'), %(appldate)s, %(lastname)s, %(firstname)s,
> %(cityid)s, %(cityid2)s) RETURNING appl.id
> 2013-10-03 12:26:37,643 INFO [sqlalchemy.engine.base.Engine][Dummy-3]
> {'cityid2': None, 'cityid': None, 'firstname': u'test3', 'lastname':
> u'test3', 'appldate': u'10/3/2013'}
> 2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Col
> ('id',)
> 2013-10-03 12:26:37,644 DEBUG [sqlalchemy.engine.base.Engine][Dummy-3] Row
> (14574,)
> 2013-10-03 12:26:37,645 INFO [sqlalchemy.engine.base.Engine][Dummy-3] COMMIT
>
> What's also really odd, is if I manually run the sql in psql, it works and
> returns the new row:
>
>
> WITH distinct_query AS (
> SELECT DISTINCT ON (refid) id, refid, lastname, firstname, appldate
> FROM appl WHERE lastname ILIKE 'test%'
> GROUP BY refid, id, lastname, firstname, appldate ORDER BY refid, appldate
> DESC
> )
> SELECT * from distinct_query ORDER BY lastname, firstname;
>
>
> Another odd thing is in my application, I have another search feature, and
> it's using an SA query that's not using the cte/distinct query below, and it
> works!
>
> Sorry to have to ask this, but I'm stuck and need to get this working.
>
> Thanks again for all the help!
>
>
> On Oct 1, 2013, at 1:22 PM, Nathan Mailg <nathanma...@gmail.com> wrote:
>
>> Thanks Mike, that works!
>>
>> I really appreciate you taking the time to show me how to get this working.
>>
>> Do you have an amazon wish list or something like that somewhere?
>>
>>
>> On Sep 30, 2013, at 2:09 PM, Michael Bayer <mike...@zzzcomputing.com> wrote:
>>
>>> qlast, qfirst = 'a', 'b'
>>> d = DBSession.query(Appl).\
>>> distinct(Appl.refid).\
>>> filter(Appl.lastname == qlast).\
>>> filter(Appl.firstname == qfirst).\
>>> group_by(Appl).\
>>> order_by(Appl.refid)
>>> d = d.cte('distinct_query')
>>>
>>> q = DBSession.query(Appl).select_entity_from(d).\
>>> join(Appl.city).\
>>> order_by(d.c.lastname, d.c.firstname)
>>>
>>> for row in q:
>>> print row.refid, row.firstname, row.lastname, row.city.name
>>
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
