Thanks Mike!
that's the first red flag here, why can't you put a unique constraint here?
>
Ordinarily I’d agree. In this case, there’s an additional column called
“deleted” which is NULL for active Tokens and contains utcnow() for deleted
Tokens. That makes for deleted and active tokens which can have the same
user_id/client_sig (one active, many deleted).
I doubt MySQL requires the "Dual" part, here is a demo
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
> class Token(Base):
> __tablename__ = 'tokens'
> id = Column(String(50), primary_key=True)
> user_id = Column(String(50))
> client_sig = Column(String(50))
>
> e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
> stmt = select([
> literal_column("'abc'"),
> literal_column("'def'"),
> literal_column("'ghi'"),
> ]).where(
> ~exists().where(and_(Token.user_id == 'def', Token.client_sig ==
> 'ghi'))
> )
>
> e.execute(
> insert(Token).from_select(['id', 'user_id', 'client_sig'], stmt)
> )
Ah ok, so from_select() would indeed be the correct approach here. Thank
you, I’ll tinker with this :-)
One more question in this context though (without having tried the above
yet). The INSERT fails if the SELECT returns an existing row (i.e. an
active Token exists already in my example above). I suspect that the
failure happens upon commit() of the transaction, not upon flush()?
Because the transaction and session are bound to a Pyramid request and the
commit happens outside of the view handler function, I’ll catch that
failure in a generic exception view
<https://docs.pylonsproject.org/projects/pyramid_cookbook/en/latest/pylons/exceptions.html#exception-views>
which I’ll have to register?
Cheers,
Jens
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
