On Mon, Nov 27, 2017 at 4:02 PM, <jens.troe...@gmail.com> 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 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.
