Hah 🤔 Boy this is (not really) funny. Thank you for digging into this,
Mike!
I had to make two minor import adjustments
from sqlalchemy.sql.elements import quoted_name
from sqlalchemy.sql.expression import literal_column
but otherwise the code works now. I still think it’s a poor solution to my
problem. The proper solution, I think, would be a CHECK constraint
<https://en.wikipedia.org/wiki/Check_constraint> across the columns and a
simple INSERT which can fail the constraint.
Alas, MySQL doesn’t do CHECK constraints—another reason to migrate to
PostgreSQL as soon as possible.
Jens
On Tuesday, November 28, 2017 at 9:23:46 AM UTC+10, Mike Bayer wrote:
>
> On Mon, Nov 27, 2017 at 4:02 PM, <[email protected] <javascript:>>
> wrote:
> >
> >
> > No problem, here it is. To work with your initial code example...
> >
> >>>> e =
> >>>>
> create_engine("mysql+pymysql://jens@localhost/test?charset=utf8&unix_socket=/opt/local/var/run/mysql56/mysqld.sock",
>
>
> >>>> echo=True)
> >>>> Base.metadata.drop_all(e)
> > 2017-11-28 06:47:21,171 INFO sqlalchemy.engine.base.Engine SHOW
> VARIABLES
> > LIKE 'sql_mode'
> > 2017-11-28 06:47:21,171 INFO sqlalchemy.engine.base.Engine {}
> > 2017-11-28 06:47:21,173 INFO sqlalchemy.engine.base.Engine SELECT
> DATABASE()
> > 2017-11-28 06:47:21,173 INFO sqlalchemy.engine.base.Engine {}
> > 2017-11-28 06:47:21,174 INFO sqlalchemy.engine.base.Engine show
> collation
> > where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
> > 2017-11-28 06:47:21,174 INFO sqlalchemy.engine.base.Engine {}
> > 2017-11-28 06:47:21,175 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test
> > plain returns' AS CHAR(60)) AS anon_1
> > 2017-11-28 06:47:21,175 INFO sqlalchemy.engine.base.Engine {}
> > 2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test
> > unicode returns' AS CHAR(60)) AS anon_1
> > 2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine {}
> > 2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test
> > collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
> > 2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine {}
> > 2017-11-28 06:47:21,177 INFO sqlalchemy.engine.base.Engine DESCRIBE
> `tokens`
> > 2017-11-28 06:47:21,177 INFO sqlalchemy.engine.base.Engine {}
> > 2017-11-28 06:47:21,179 INFO sqlalchemy.engine.base.Engine ROLLBACK
> >>>> Base.metadata.create_all(e)
> > 2017-11-28 06:47:23,490 INFO sqlalchemy.engine.base.Engine DESCRIBE
> `tokens`
> > 2017-11-28 06:47:23,490 INFO sqlalchemy.engine.base.Engine {}
> > 2017-11-28 06:47:23,495 INFO sqlalchemy.engine.base.Engine ROLLBACK
> > 2017-11-28 06:47:23,496 INFO sqlalchemy.engine.base.Engine
> > CREATE TABLE tokens (
> > id VARCHAR(50) NOT NULL,
> > user_id VARCHAR(50),
> > client_sig VARCHAR(50),
> > PRIMARY KEY (id)
> > )
> > 2017-11-28 06:47:23,496 INFO sqlalchemy.engine.base.Engine {}
> > 2017-11-28 06:47:23,507 INFO sqlalchemy.engine.base.Engine COMMIT
> >>>> stmt = select([
> > ... literal_column("'abc'"),
> > ... literal_column("'def'"),
> > ... literal_column("'ghi'"),
> > ... ]).where(
> > ... ~exists().where(and_(Token.user_id == 'def', Token.client_sig ==
> > 'ghi'))
> > ... )
> >>>>
> >>>> print(stmt)
> > SELECT 'abc', 'def', 'ghi'
> > WHERE NOT (EXISTS (SELECT *
> > FROM tokens
> > WHERE tokens.user_id = :user_id_1 AND tokens.client_sig =
> :client_sig_1))
> >
> >
> >> OK I don't recall what conditions MySQL needs "FROM dual" can you share
> >> with me:
> >>
> >> 1. the full version of MySQL
>
> Well in standard MySQL / MariaDB fashion, they have made this as fun
> as possible (well, more fun would be one database *rejects* FROM DUAL,
> at least it isn't that bad):
>
> 1. MySQL 5.6 requires "FROM DUAL"
>
> 2. MySQL 5.7 does not require FROM DUAL but accepts it.
>
> 3. MariaDB 10.1 does not require FROM DUAL but accepts it.
>
> 4. MariaDB 10.2 *does* require FROM DUAL. The two vendors have
> **flip-flopped** on their preference of this issue.
>
> Anyway, here's your dual, as is typical, to make it work completely we
> need an esoteric trick to avoid quoting the "dual" word:
>
> from sqlalchemy.sql import quoted_name
> dual = table(quoted_name("dual", quote=False))
>
> then your statement:
>
> stmt = select([
> literal_column("'abc'"),
> literal_column("'def'"),
> literal_column("'ghi'"),
> ]).select_from(dual).where(
> ~exists().where(and_(Token.user_id == 'def', Token.client_sig ==
> 'ghi'))
> )
>
>
>
>
>
> >
> >
> >>>> e.dialect.name, e.dialect.driver, e.dialect.server_version_info
> > ('mysql', 'pymysql', (5, 6, 34))
> >
> >> 2. the output of "SHOW VARIABLES LIKE '%SQL_MODE%'
> >>
> >
> > mysql> show variables like '%sql_mode%';
> > +---------------+------------------------+
> > | Variable_name | Value |
> > +---------------+------------------------+
> > | sql_mode | NO_ENGINE_SUBSTITUTION |
> > +---------------+------------------------+
> > 1 row in set (0.00 sec)
> >
> >
> >> 3. stack trace + error message
> >
> >
> >>>> e.execute(
> > ... insert(Token).from_select(['id', 'user_id', 'client_sig'], stmt)
> > ... )
> > 2017-11-28 06:47:49,489 INFO sqlalchemy.engine.base.Engine INSERT INTO
> > tokens (id, user_id, client_sig) SELECT 'abc', 'def', 'ghi'
> > WHERE NOT (EXISTS (SELECT *
> > FROM tokens
> > WHERE tokens.user_id = %(user_id_1)s AND tokens.client_sig =
> > %(client_sig_1)s))
> > 2017-11-28 06:47:49,489 INFO sqlalchemy.engine.base.Engine
> {'client_sig_1':
> > 'ghi', 'user_id_1': 'def'}
> > 2017-11-28 06:47:49,491 INFO sqlalchemy.engine.base.Engine ROLLBACK
> > Traceback (most recent call last):
> > File "/…/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line
> > 1182, in _execute_context
> > context)
> > File "/…/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
> line
> > 470, in do_execute
> > cursor.execute(statement, parameters)
> > File "/…/lib/python3.5/site-packages/pymysql/cursors.py", line 166, in
> > execute
> > result = self._query(query)
> > File "/…/lib/python3.5/site-packages/pymysql/cursors.py", line 322, in
> > _query
> > conn.query(q)
> > File "/…/lib/python3.5/site-packages/pymysql/connections.py", line
> 856, in
> > query
> > self._affected_rows = self._read_query_result(unbuffered=unbuffered)
> > File "/…/lib/python3.5/site-packages/pymysql/connections.py", line
> 1057,
> > in _read_query_result
> > result.read()
> > File "/…/lib/python3.5/site-packages/pymysql/connections.py", line
> 1340,
> > in read
> > first_packet = self.connection._read_packet()
> > File "/…/lib/python3.5/site-packages/pymysql/connections.py", line
> 1014,
> > in _read_packet
> > packet.check_error()
> > File "/…/lib/python3.5/site-packages/pymysql/connections.py", line
> 393, in
> > check_error
> > err.raise_mysql_exception(self._data)
> > File "/…/lib/python3.5/site-packages/pymysql/err.py", line 107, in
> > raise_mysql_exception
> > raise errorclass(errno, errval)
> > pymysql.err.ProgrammingError: (1064, "You have an error in your SQL
> syntax;
> > check the manual that corresponds to your MySQL server version for the
> right
> > syntax to use near 'WHERE NOT (EXISTS (SELECT * \nFROM tokens \nWHERE
> > tokens.user_id = 'def' AND token' at line 2")
> >
> > The above exception was the direct cause of the following exception:
> >
> > Traceback (most recent call last):
> > File "<stdin>", line 2, in <module>
> > File "/…/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line
> > 2064, in execute
> > return connection.execute(statement, *multiparams, **params)
> > File "/…/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line
> 945,
> > in execute
> > return meth(self, multiparams, params)
> > File "/…/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line
> > 263, in _execute_on_connection
> > return connection._execute_clauseelement(self, multiparams, params)
> > File "/…/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line
> > 1053, in _execute_clauseelement
> > compiled_sql, distilled_params
> > File "/…/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line
> > 1189, in _execute_context
> > context)
> > File "/…/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line
> > 1402, in _handle_dbapi_exception
> > exc_info
> > File "/…/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line
> 203,
> > in raise_from_cause
> > reraise(type(exception), exception, tb=exc_tb, cause=cause)
> > File "/…/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line
> 186,
> > in reraise
> > raise value.with_traceback(tb)
> > File "/…/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line
> > 1182, in _execute_context
> > context)
> > File "/…/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
> line
> > 470, in do_execute
> > cursor.execute(statement, parameters)
> > File "/…/lib/python3.5/site-packages/pymysql/cursors.py", line 166, in
> > execute
> > result = self._query(query)
> > File "/…/lib/python3.5/site-packages/pymysql/cursors.py", line 322, in
> > _query
> > conn.query(q)
> > File "/…/lib/python3.5/site-packages/pymysql/connections.py", line
> 856, in
> > query
> > self._affected_rows = self._read_query_result(unbuffered=unbuffered)
> > File "/…/lib/python3.5/site-packages/pymysql/connections.py", line
> 1057,
> > in _read_query_result
> > result.read()
> > File "/…/lib/python3.5/site-packages/pymysql/connections.py", line
> 1340,
> > in read
> > first_packet = self.connection._read_packet()
> > File "/…/lib/python3.5/site-packages/pymysql/connections.py", line
> 1014,
> > in _read_packet
> > packet.check_error()
> > File "/…/lib/python3.5/site-packages/pymysql/connections.py", line
> 393, in
> > check_error
> > err.raise_mysql_exception(self._data)
> > File "/…/lib/python3.5/site-packages/pymysql/err.py", line 107, in
> > raise_mysql_exception
> > raise errorclass(errno, errval)
> > sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064,
> "You
> > have an error in your SQL syntax; check the manual that corresponds to
> your
> > MySQL server version for the right syntax to use near 'WHERE NOT (EXISTS
> > (SELECT * \nFROM tokens \nWHERE tokens.user_id = 'def' AND token' at
> line
> > 2") [SQL: "INSERT INTO tokens (id, user_id, client_sig) SELECT 'abc',
> 'def',
> > 'ghi' \nWHERE NOT (EXISTS (SELECT * \nFROM tokens \nWHERE tokens.user_id
> =
> > %(user_id_1)s AND tokens.client_sig = %(client_sig_1)s))"] [parameters:
> > {'client_sig_1': 'ghi', 'user_id_1': 'def'}]
> >
> > Please let me know of anything else you need from me!
> > 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] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > 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.
