On Sun, Nov 26, 2017 at 3:49 AM, <jens.troe...@gmail.com> wrote:
> 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()?
flush() has no relationship to an INSERT..SELECT construct as above.
I"m not sure what "fails" means here, are you saying the INSERT
actually raises an error if the SELECT returns no rows (I've never
tried this, so not sure) ? if that is the case it would happen when
you invoke the statement. when some kind of error happens upon
COMMIT that is usually within a narrow range of deadlock conditions
that for MySQL usually occur for a certain kind of DELETE scenario,
for an INSERT I've only seen fail-on-commit as a behavior of Galera
when a writeset conflicts with another node.
>
> 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 which I’ll have to register?
I'd need to see more detail about this failure mode since I haven't
tried doing an empty insert...select on MySQL.
>
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
