[sqlalchemy] Common datetime call
Hi, Does a means exist to generically call a local datetime func such that it renders as SYSDATE in Oracle and GETDATE() in SQL Server? Thanks, jlc -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4f92c075-28b9-4c38-af10-5d21b8c83446n%40googlegroups.com.
Re: [sqlalchemy] Ambiguous joins, relationships, and aliases
On Thu, Jun 17, 2021, at 1:04 PM, Dane K Barney wrote:
> I have scoured the documentation looking for the preferred way of handling
> ambiguous joins, and it seems to be through use of aliased tables, but I
> still wonder if there's a better way. Intuitively, it seems like
> relationships hold enough information to make aliased tables unnecessary.
technically, they do.
>
>
>
>
> But the API doesn't behave in that way (AFAIK), and I'm curious why.
We have had an API that does what you seek here for many years and quite
frankly it's being removed in SQLAlchemy 2.0. It is one of the most complex
parts of the Query object and has for many years made adding new behaviors,
fixing issues , and refactoring code extremely painful.On top of that, it's
a feature that we've observed virtually nobody uses, is aware of, or
understands.
>
> Suppose I have the following the model:
>
> Base = declarative_base()
>
> class Parent(Base):
> __tablename__ = "parent"
> id = Column(Integer, primary_key=True)
>
> class Child(Base):
> __tablename__ = "child"
> id = Column(Integer, primary_key=True)
> mother_id = Column(Integer, ForeignKey("parent.id"))
> father_id = Column(Integer, ForeignKey("parent.id"))
>
> mother = relationship("Parent", foreign_keys=[mother_id])
> father = relationship("Parent", foreign_keys=[father_id])
>
> The Child table can join to the Parent table through 2 possible
> relationships: mother and father. When querying for Child entities, in order
> to reference columns through either of these relationships, it would appear
> that aliased() is required.
>
> Something like this:
>
> mother_alias = aliased(Parent)
> father_alias = aliased(Parent)
> statement = select(Child).join(mother_alias, Child.mother).join(father_alias,
> Child.father).where(mother_alias.id == "Martha", father_alias.id == "Bob")
>
> What I find strange about this is that the alias and relationship it's
> associated with are not inherently coupled together. I associate them with
> one another when I pass them together into the join method(), but this
> association is only implicit and not enforced. It's up to me to keep track of
> which alias belongs with which relationship.
>
> Given that I can join to a table using a relationship property, why is it not
> possible to also select columns and filter by columns using relationship
> properties? It seems like this would eliminate the need for aliased() in the
> above example entirely. This feels much more intuitive to me:
>
> statement =
> select(Child).join(Child.mother).join(Child.father).where(Child.mother.id ==
> "Martha", Child.father.id == "Bob")
>
> In other words, the relationship can act as a proxy to its target table, and
> you can access all columns on that table through the relationship. The
> concept of an "aliased table" would be internal to the relationship, and the
> developer would not have to manage these aliases themselves
Yes using legacy Query you would say:
q = session.query(Child).join(Child.mother, aliased=True).filter(Parent.id ==
"x").join(Child.father, aliased=True).filter(Parent.id == "y").
The order of join() / filter() is important as the Query has to keep track of
the last join(Child.x, aliased) as the current meaning of "Parent".
So what's different here is that there is no "magic namespace" introduced on
the relationship(), e.g. "Child.mother.id". The "Child.mother" attribute
already has its own set of query methods so is not appropriate as a namespace
of columns; also, people would intuitively want to do things like
"Child.mother.related_foo.related_bar.x.y.z" which would in theory represent
some kind of join but there's no simple way to manage that when any attribute
can span out like that (try out the Django ORM for more of that kind of
approach).Additionally, hardcoding these namespaces to "Child.mother" /
"Child.father" means the API cannot be used for the more common approach of
querying an adjacency list model, which looks like this:
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
parent_id = Column(ForeignKey("node.id"))
data = Column(String)
parent = relationship("Node", remote_side=id)
Above, to query Node many levels deep means we need "Node" itself to be
interpreted differently at each step, so "Node.related" would remain ambiguous:
q = session.query(Node).filter(Node.data=='child').join(Node.parent,
aliased=True).filter(Node.data == "parent").join(Node.parent,
aliased=True).filter(Node.data == "grandparent")
>
>
>
>
>
>
> .
>
> Is this possible? Is there an obvious reason I'm missing for why the API
> cannot function in this way?
the reason is non-obvious as it took me ten years at least to decide this API
wasn't worth the complexity. it's still there in 1.4 but I am super excited
to get rid of it.Explicit is better than implicit, your code will be easier
to
[sqlalchemy] Ambiguous joins, relationships, and aliases
I have scoured the documentation looking for the preferred way of handling
ambiguous joins, and it seems to be through use of aliased tables, but I
still wonder if there's a better way. Intuitively, it seems like
relationships hold enough information to make aliased tables unnecessary.
But the API doesn't behave in that way (AFAIK), and I'm curious why.
Suppose I have the following the model:
Base = declarative_base()
class Parent(Base):
__tablename__ = "parent"
id = Column(Integer, primary_key=True)
class Child(Base):
__tablename__ = "child"
id = Column(Integer, primary_key=True)
mother_id = Column(Integer, ForeignKey("parent.id"))
father_id = Column(Integer, ForeignKey("parent.id"))
mother = relationship("Parent", foreign_keys=[mother_id])
father = relationship("Parent", foreign_keys=[father_id])
The Child table can join to the Parent table through 2 possible
relationships: mother and father. When querying for Child entities, in
order to reference columns through either of these relationships, it would
appear that aliased() is required.
Something like this:
mother_alias = aliased(Parent)
father_alias = aliased(Parent)
statement = select(Child).join(mother_alias,
Child.mother).join(father_alias, Child.father).where(mother_alias.id ==
"Martha", father_alias.id == "Bob")
What I find strange about this is that the alias and relationship it's
associated with are not inherently coupled together. I associate them with
one another when I pass them together into the join method(), but this
association is only implicit and not enforced. It's up to me to keep track
of which alias belongs with which relationship.
Given that I can join to a table using a relationship property, why is it
not possible to also select columns and filter by columns using
relationship properties? It seems like this would eliminate the need for
aliased() in the above example entirely. This feels much more intuitive to
me:
statement =
select(Child).join(Child.mother).join(Child.father).where(Child.mother.id
== "Martha", Child.father.id == "Bob")
In other words, the relationship can act as a proxy to its target table,
and you can access all columns on that table through the relationship. The
concept of an "aliased table" would be internal to the relationship, and
the developer would not have to manage these aliases themselves.
Is this possible? Is there an obvious reason I'm missing for why the API
cannot function in this way?
Thanks for any help or insight you can provide!
-Dane
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/c9f82f00-bcbf-4a9b-aa33-8fce7ab63bbdn%40googlegroups.com.
Re: [sqlalchemy] versioned_history example uses deprecated Column.copy() method
Thanks Mike, I'll see what I can do. Simon On Wed, Jun 16, 2021 at 1:26 PM Mike Bayer wrote: > > HI Simon - > > I believe that example for now should vendor its own "copy()" function that > does what's needed. the function that's there is already un-doing some of > the work of the old copy() method in any case. I think for history table we > need column name, datatype, and maybe nullable constraint. > > We can accept PRs for this if this is something you would be interested in, > although your contributions to the SQLA mailing list are already many :) > > > > On Wed, Jun 16, 2021, at 7:15 AM, Simon King wrote: > > Hi all, > > I'm updating an app from SA 1.3 to 1.4 and getting a SADeprecationWarning: > > The Column.copy() method is deprecated and will be removed in a > future release. (deprecated since: 1.4) > > The code triggering the warning is based on the versioned_history example: > > https://docs.sqlalchemy.org/en/14/_modules/examples/versioned_history/history_meta.html > > ...and here's the offending function: > > def _col_copy(col): > orig = col > col = col.copy() > orig.info["history_copy"] = col > col.unique = False > col.default = col.server_default = None > col.autoincrement = False > return col > > For the moment I've switched to calling the private _copy() method > instead, but is there any recommendation of a better approach? > > (I did see the issue at > https://github.com/sqlalchemy/sqlalchemy/issues/5953 and understand > why the copy() method was deprecated) > > Thanks a lot, > > Simon > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAFHwexdUVb11FTq%3DzxuzNsp-FszVHrJ8KT-yDjhyNkWqhKoJnQ%40mail.gmail.com. > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/46661334-a2b9-4548-9003-b487b725615d%40www.fastmail.com. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexf42bqjXmG9TQPd%2B0h%2BZYZAoFHnxk-ZvN2OH0MiyLRJrw%40mail.gmail.com.
