query vs select

[jo]

Hi all,

I'm sorry for this simple question.
What's the difference between query and select ?
are they interchangeable?
which of the two, it is best to use?

-------> print(session.query(Azienda.c.data_inizio).limit(1))
SELECT azienda_data_inizio
FROM (SELECT azienda.data_inizio AS azienda_data_inizio
FROM azienda)
WHERE ROWNUM <= :ROWNUM_1

-------> print(select([Azienda.c.data_inizio]).limit(1))
SELECT data_inizio
FROM (SELECT azienda.data_inizio AS data_inizio
FROM azienda)
WHERE ROWNUM <= :ROWNUM_1

j

[Michael Bayer]

There's some degree of history here as SQLAlchemy initially didn't have the whole "generative" notion of things, and the Mapper object itself would accept arguments which it passed mostly straight to a select() object. So you saw similar interfaces and it was kind of like switching between table.select(<all the arguments>) and mapper.select(<all the arguments>). There was a great emphasis on using regular select() constructs with mappers, and queries were always done in terms of Table objects, not classes - the idea of MyClass.foo=='bar' was introduced much later, even though this Table access was somewhat convenient via the ".c." attribute on classes (so MyClass.c.foo == 'bar' - this is actually completely different than what MyClass.foo is today). Overall there was a lot of SQLObject influencing how things were done.

If I were doing this again today, I might try to have a select() object that somehow morphs more smoothly into an ORM-centric object - though this would be challenging as the ORM Query does a lot of things with it's state before generating a much more rudimentary select() construct. The Query has a lot of opinions about things that the select() does not, even though our Query is still much closer to SQL than that of any other ORM. I have thought recently about this subject. But each time I try to consider there being just one construct that can move between them, the details of how that would work become apparently very hazy and unclear - it would take a lot of thinking. Still could be worth it, though.

So as things turned out in 0.5, 0.6 and onwards, we've made the Query object be the "main" thing you work with when using the ORM. There's very little emphasis on using a select() construct directly except when embedding a subquery into an existing Query - but even then we have you build up the select() using the Query interface. The select() and Query still play together pretty well but we try to say when you're actually building select() constructs directly, you're working in a "schema-centric" fashion as opposed to a "domain-centric" fashion. If you're considering your query in terms of tables, and you want plain tuples back, that leans towards select([]), and if you're querying in terms of the object model, you use Query and can get back any combination of objects/tuples.

I know it's not 100% "pure" but it does seem to work out pretty well.

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

[jo]

Thanks Michael, your explanation is comprehensive, currently I'm using
both of them,
but I feared that one of them could become obsolete in the future.
I'm trying to translate some queries that I had done with engine
but I find it hard to do.
For example, a query like this one, I can not set it with query neither
with select,
what do you suggest?

SELECT max(sopralluogo.data_sopralluogo),
scadenza_malattia.cod_malattia,
scadenza_malattia.id_unita_aziendale
FROM scadenza_malattia
JOIN scadenziario ON scadenza_malattia.id_scadenziario = scadenziario.id
JOIN sopralluogo ON scadenziario.id_sopralluogo = sopralluogo.id
WHERE scadenza_malattia.cod_malattia IS NOT NULL AND
sopralluogo.data_sopralluogo > '2011-01-01' AND
scadenza_malattia.id_unita_aziendale = 2 AND
scadenza_malattia.cod_malattia = '012'
GROUP BY scadenza_malattia.id_unita_aziendale,
scadenza_malattia.cod_malattia
ORDER BY scadenza_malattia.id_unita_aziendale,
scadenza_malattia.cod_malattia

--
Jose Soares _/_/
Sferacarta Net
Via Bazzanese 69 _/_/ _/_/_/
40033 Casalecchio di Reno _/_/ _/_/ _/_/
Bologna - Italy _/_/ _/_/ _/_/
Ph +39051591054 _/_/ _/_/ _/_/ _/_/
fax +390516131537 _/_/ _/_/ _/_/ _/_/
web:www.sferacarta.com _/_/_/ _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l�integrit� e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.

[Michael Bayer]

there's nothing particularly advanced in that query and either system can accomplish it in a straightforward way.

What's the part of it that isn't clear ? Here you'd be using funx.max(), join(), "x != None", group_by() and order_by().

> Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

>
> This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.
>

[jo]

Michael Bayer wrote:
> there's nothing particularly advanced in that query and either system can accomplish it in a straightforward way.
>
> What's the part of it that isn't clear ? Here you'd be using funx.max(), join(), "x != None", group_by() and order_by().
>
>
>

II did it in this way:

session.query(sa.func.max(Sopralluogo.c.data_sopralluogo),
ScadenzaMalattia.c.cod_malattia,
ScadenzaMalattia.c.id_unita_aziendale).select_from(
ScadenzaMalattia).join(
Scadenziario).join(
Sopralluogo).group_by(

ScadenzaMalattia.c.id_unita_aziendale,ScadenzaMalattia.c.cod_malattia).order_by(

ScadenzaMalattia.c.id_unita_aziendale,ScadenzaMalattia.c.cod_malattia).filter(
sa.and_(ScadenzaMalattia.c.cod_malattia!=None,
Sopralluogo.c.data_sopralluogo > '2011-01-01'),
ScadenzaMalattia.c.id_unita_aziendale == 2,
ScadenzaMalattia.c.cod_malattia == '012'))

>> Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l�integrit� e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

>>
>> This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.
>>
>> --
>> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
>> To post to this group, send email to sqlal...@googlegroups.com.
>> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
>> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>>
>>
>
>

--
Jose Soares _/_/
Sferacarta Net
Via Bazzanese 69 _/_/ _/_/_/
40033 Casalecchio di Reno _/_/ _/_/ _/_/
Bologna - Italy _/_/ _/_/ _/_/
Ph +39051591054 _/_/ _/_/ _/_/ _/_/
fax +390516131537 _/_/ _/_/ _/_/ _/_/
web:www.sferacarta.com _/_/_/ _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l�integrit� e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.