On Fri, Nov 24, 2017 at 6:32 PM, <[email protected]> wrote:
> Hi,
>
> My question is based on this answer on Stackoverflow to the question MySQL
> Conditional Insert. In my current SA implementation I have the following
> code:
>
> token = Token(
> user=user,
> client_sig=sigstring,
> )
> session.add(token)
> # session.flush()
>
> Now I would like to prevent duplicate client_sig entries to prevent the same
> user having the same client_sig, but I can’t set that column to unique.
that's the first red flag here, why can't you put a unique constraint here?
It
> looks like the following SQL statement achieves what I want:
>
> INSERT INTO tokens(id, user_id, client_sig)
> SELECT '51…bb', 'fd…b3', 'some string'
> FROM dual
> WHERE NOT EXISTS (
> SELECT *
> FROM tokens
> WHERE user_id='fd…b3'
> AND client_sig='some string'
> );
>
> I found some documentation on insert … from select but can not quite put the
> two together (I don't think they're the same).
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)
)
How would I go about
> implementing the above SQL statement using SA, or will I have to issue raw
> SQL in this case?
>
> Thank you!
> 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.
--
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.
