the answer is here:
https://stackoverflow.com/a/12943155/34549
so "Token" has to be aliased and both token and the alias need to be
locked individually, demo is below. But keep in mind, since you are
LOCKing the whole table, the whole "INSERT..SELECT" approach is
unnecessary, you could just SELECT first, get the result back, then
INSERT. Also make sure you unlock in a try/finally block.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import quoted_name
dual = table(quoted_name("dual", quote=False))
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)
ta = aliased(Token, name='ta')
stmt = select([
literal_column("'abc'"),
literal_column("'def'"),
literal_column("'ghi'"),
]).select_from(dual).where(
~exists().where(and_(ta.user_id == 'def', ta.client_sig == 'ghi'))
)
with e.begin() as conn:
conn.execute("LOCK TABLES tokens WRITE, tokens as ta WRITE")
try:
conn.execute(stmt)
conn.execute(
insert(Token).from_select(['id', 'user_id', 'client_sig'], stmt)
)
finally:
conn.execute("UNLOCK TABLES")
On Wed, Dec 6, 2017 at 8:30 PM, <[email protected]> wrote:
>
>> is that session using "autocommit" mode?
>
>
> print("--> autocommit", dbsession.autocommit) gives a False.
>
>>
>> it looks like the error is raised on the UNLOCK ?
>
>
> When I comment out the UNLOCK, the exception still raises. Here is SQLA’s
> verbose logging:
>
> --> autocommit False
> 2017-12-07 11:23:52,101 INFO [sqlalchemy.engine.base.Engine][MainThread]
> LOCK TABLES tokens WRITE
> 2017-12-07 11:23:52,101 INFO [sqlalchemy.engine.base.Engine][MainThread] {}
> 2017-12-07 11:23:52,102 INFO [sqlalchemy.engine.base.Engine][MainThread]
> INSERT INTO tokens (id, user_id, client_sig) SELECT '84…20', '39…30',
> '0b…87'
> FROM dual
> WHERE NOT (EXISTS (SELECT *
> FROM tokens
> WHERE tokens.user_id = %(user_id_1)s AND tokens.client_sig =
> %(client_sig_1)s))
> 2017-12-07 11:23:52,102 INFO [sqlalchemy.engine.base.Engine][MainThread]
> {'user_id_1': '39…30', 'client_sig_1': '0b…87'}
> 2017-12-07 11:23:52,103 ERROR [srv.views.exceptions][MainThread] Internal
> server error detected, stacktrace follows.
>
>
> --
> 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.
