Re: [sqlalchemy] Transforming SQL to SQLAlchemy
On 7/26/15 12:56 PM, Dennis Ljungmark wrote: Hello, all. I am trying to reshape the (somewhat tricky) SQL query below into something SQLAlchemy can generate, and I just hit a wall: This is the query: SELECT (c).csr_id, (c).not_before, (c).not_after FROM (SELECT (SELECT c FROM certificate c WHERE c.csr_id=csr.id ORDER BY c.not_after DESC LIMIT 1) AS c FROM csr offset 0) s; And, I just can't seem to get SQLAlchemy to emit a correlated scalar subquery like this. correlated scalar subqueries are illustrated in the Core tutorial at http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#scalar-selects. I'm not sure why inklesspen's example isn't making use of as_scalar() or stmt.label(), which would be required. Query supports the same use if you are working with ORM queries. However, the statement SELECT c FROM certificate c and the (c).csr_id syntax appears to be using a very unusual postgresql-specific syntax which is rolling the whole inner row into a single-column tuple and then referencing it that way, which is odd because the row itself is already a tuple, not sure what forcing it into a tuple that contains exactly one tuple accomplishes here. While we can certainly use tuple_() and probably create some extra custom constructs around it to support this syntax, I see nothing in this specific statement to justify the use of these syntaxes, nor the need to produce correlated scalar subqueries when we just are looking for one row from the certificate table. I don't at the moment see why you can't use this statement, which is easier to read, platform agnostic, and way more efficient: SELECT c.csr_id, c.not_before, c.not_after FROM certificate AS c JOIN csr ON csr.id = c.csr_id ORDER BY c.not_after DESC LIMIT 1 There ought to be something, but for the life of me I can't seem to make it happen. InklessPen tried to help me on IRC, and we got something, but it's still not quite the same, https://gist.github.com/inklesspen/49e69e1f33f3852d348a A reduced model is below, class CSR(Base): certificates = _orm.relationship(Certificate, backref=csr) class Certificate(Base): not_before = _sa.Column(_sa.DateTime, nullable=False) not_after = _sa.Column(_sa.DateTime, nullable=False) csr_id = _fkcolumn(CSR.id, nullable=False) Regards, D.S. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout.
[sqlalchemy] Transforming SQL to SQLAlchemy
Hello, all. I am trying to reshape the (somewhat tricky) SQL query below into something SQLAlchemy can generate, and I just hit a wall: This is the query: SELECT (c).csr_id, (c).not_before, (c).not_after FROM (SELECT (SELECT c FROM certificate c WHERE c.csr_id=csr.id ORDER BY c.not_after DESC LIMIT 1) AS c FROM csr offset 0) s; And, I just can't seem to get SQLAlchemy to emit a correlated scalar subquery like this. There ought to be something, but for the life of me I can't seem to make it happen. InklessPen tried to help me on IRC, and we got something, but it's still not quite the same, https://gist.github.com/inklesspen/49e69e1f33f3852d348a A reduced model is below, class CSR(Base): certificates = _orm.relationship(Certificate, backref=csr) class Certificate(Base): not_before = _sa.Column(_sa.DateTime, nullable=False) not_after = _sa.Column(_sa.DateTime, nullable=False) csr_id = _fkcolumn(CSR.id, nullable=False) Regards, D.S. -- 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/d/optout.
Re: [sqlalchemy] Re: what is the way to achieve automatic conversion on each query?
On 7/26/15 4:39 PM, Jinghui Niu wrote: Thanks Mike, I've read the documentation on both, hybrid attributes seem to be easier and have a clearer logic in this use case. Although this is just my gut feeling as a layman. What is the rationale behind having two competing solutions? Also, I dimly remember someone recommended using property method, I didn't quite understand it back then, does it relate to the two techniques you suggested here? composites are a much older feature and very specific to the multiple-columns use case, but should work here. hybrids are more open ended, more flexible and probably easier to understand. sounds like a property method to me, e.g. a Python descriptor. On Sun, Jul 26, 2015 at 11:06 AM, Mike Bayer mike...@zzzcomputing.com mailto:mike...@zzzcomputing.com wrote: On 7/25/15 6:38 PM, Jinghui Niu wrote: By the way, the database driver that I'm using is SQLite, which doesn't support native timestamp I believe. On Saturday, July 25, 2015 at 2:21:44 PM UTC-7, Jinghui Niu wrote: I'm using two columns to store my datetime records: one column stores utc timestamp as a string, the other column stores a timezone offset as an integer. Now I find myself most of the time writing ad hoc functions to convert those UTC times to various local times. My code base has become really inconsistent and repetitive. I'm looking to write some code with sqlalchemy, natively sqlalchemy, to allow me to get the converted local time on each query, automatically. I've heard of that feature before. I'm kind of lost in the documentation. Could someone point a general direction here? I don't expect too much, just a general direction would be highly appreciated. Thanks a lot. -- there are multiple documented techniques for this kind of thing. Have you looked into composite attributes or hybrid properties ? Both can suit this use case. 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/qWh8o5bGzU4/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout.
Re: [sqlalchemy] Re: what is the way to achieve automatic conversion on each query?
Thanks Mike, I've read the documentation on both, hybrid attributes seem to be easier and have a clearer logic in this use case. Although this is just my gut feeling as a layman. What is the rationale behind having two competing solutions? Also, I dimly remember someone recommended using property method, I didn't quite understand it back then, does it relate to the two techniques you suggested here? On Sun, Jul 26, 2015 at 11:06 AM, Mike Bayer mike...@zzzcomputing.com wrote: On 7/25/15 6:38 PM, Jinghui Niu wrote: By the way, the database driver that I'm using is SQLite, which doesn't support native timestamp I believe. On Saturday, July 25, 2015 at 2:21:44 PM UTC-7, Jinghui Niu wrote: I'm using two columns to store my datetime records: one column stores utc timestamp as a string, the other column stores a timezone offset as an integer. Now I find myself most of the time writing ad hoc functions to convert those UTC times to various local times. My code base has become really inconsistent and repetitive. I'm looking to write some code with sqlalchemy, natively sqlalchemy, to allow me to get the converted local time on each query, automatically. I've heard of that feature before. I'm kind of lost in the documentation. Could someone point a general direction here? I don't expect too much, just a general direction would be highly appreciated. Thanks a lot. -- there are multiple documented techniques for this kind of thing. Have you looked into composite attributes or hybrid properties ? Both can suit this use case. 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/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/qWh8o5bGzU4/unsubscribe. To unsubscribe from this group and all its topics, 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/d/optout. -- 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/d/optout.
