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
>
>>> 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].
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.
