On Nov 28, 2017 12:13 AM, <jens.troe...@gmail.com> wrote: 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. MariaDB 10.2 does 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, <jens.t...@gmail.com> wrote: > > > > > > No problem, here it is. To work with your initial code example... > > > >>>> e = > >>>> create_engine("mysql+pymysql://jens@localhost/test?charset=u > tf8&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 sqlalchemy+...@googlegroups.com. > > To post to this group, send email to sqlal...@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. -- 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.
