mysql - SQLAlchemy 错误插入多个对象

Question

这可能是我遗漏的一些简单的东西，但我不知道它是什么。

这是我的代码:

from sqlalchemy import (Column, String, create_engine)
from sqlalchemy.schema import FetchedValue
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (scoped_session, sessionmaker)


DBSession = scoped_session(sessionmaker())
Base = declarative_base()

class Timezone(Base):
    __tablename__ = 'timezone'
    tz_name = Column(String(100), primary_key=True)
    description = Column(String(100), nullable=False)


if __name__ == '__main__':
    engine = create_engine('mysql+mysqlconnector://root@127.0.0.1/mainserver?charset=utf8&use_unicode=0', echo=True)
    DBSession.configure(bind=engine)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    session = DBSession()
    session.add_all([
        Timezone(tz_name = "Canada/Atlantic", description = "Atlantic (UTC-4)"),
        Timezone(tz_name = "Canada/Central", description = "Central (UTC-6)"),
        Timezone(tz_name = "Canada/Eastern", description = "Eastern (UTC-5)"),
        Timezone(tz_name = "Canada/Mountain", description = "Mountain (UTC-7)"),
        Timezone(tz_name = "Canada/Newfoundland", description = "Newfoundland (UTC-3:30)"),
        Timezone(tz_name = "Canada/Pacific", description = "Pacific (UTC-8)"),
        Timezone(tz_name = "Canada/Saskatchewan", description = "Saskatchewan (UTC-6)"),
        Timezone(tz_name = "Canada/Yukon", description = "Yukon (UTC-8)"),
    ])
    session.flush()
    session.commit()

下面是发生错误的输出:

2012-11-02 18:52:32,343 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2012-11-02 18:52:32,343 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 18:52:32,346 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2012-11-02 18:52:32,346 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 18:52:32,347 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
2012-11-02 18:52:32,348 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 18:52:32,364 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2012-11-02 18:52:32,364 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 18:52:32,365 INFO sqlalchemy.engine.base.Engine DESCRIBE `timezone`
2012-11-02 18:52:32,366 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 18:52:32,366 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-11-02 18:52:32,367 INFO sqlalchemy.engine.base.Engine DESCRIBE `timezone`
2012-11-02 18:52:32,367 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 18:52:32,368 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-11-02 18:52:32,369 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE timezone (
    tz_name VARCHAR(100) NOT NULL, 
    description VARCHAR(100) NOT NULL, 
    PRIMARY KEY (tz_name)
)


2012-11-02 18:52:32,369 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 18:52:32,434 INFO sqlalchemy.engine.base.Engine COMMIT
2012-11-02 18:52:32,436 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-11-02 18:52:32,437 INFO sqlalchemy.engine.base.Engine INSERT INTO timezone (tz_name, description) VALUES (%(tz_name)s, %(description)s)
2012-11-02 18:52:32,437 INFO sqlalchemy.engine.base.Engine ({'tz_name': 'Canada/Atlantic', 'description': 'Atlantic (UTC-4)'}, {'tz_name': 'Canada/Central', 'description': 'Central (UTC-6)'}, {'tz_name': 'Canada/Eastern', 'description': 'Eastern (UTC-5)'}, {'tz_name': 'Canada/Mountain', 'description': 'Mountain (UTC-7)'}, {'tz_name': 'Canada/Newfoundland', 'description': 'Newfoundland (UTC-3:30)'}, {'tz_name': 'Canada/Pacific', 'description': 'Pacific (UTC-8)'}, {'tz_name': 'Canada/Saskatchewan', 'description': 'Saskatchewan (UTC-6)'}, {'tz_name': 'Canada/Yukon', 'description': 'Yukon (UTC-8)'})
2012-11-02 18:52:32,438 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 1680, in _execute_context
    context)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/engine/default.py", line 330, in do_executemany
    cursor.executemany(statement, parameters)
  File "/sites/metrics_dev/lib/python3.3/site-packages/mysql/connector/cursor.py", line 493, in executemany
    return self._batch_insert(operation,seq_params)
  File "/sites/metrics_dev/lib/python3.3/site-packages/mysql/connector/cursor.py", line 449, in _batch_insert
    return self.execute(stmt)
  File "/sites/metrics_dev/lib/python3.3/site-packages/mysql/connector/cursor.py", line 418, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/sites/metrics_dev/lib/python3.3/site-packages/mysql/connector/connection.py", line 512, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/sites/metrics_dev/lib/python3.3/site-packages/mysql/connector/connection.py", line 434, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 '%(tz_name),(%(tz_name),(%(tz_name),(%(tz_name),(%(tz_name),(%(tz_name),(%(tz_nam' at line 1

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "bug2.py", line 32, in <module>
    session.flush()
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/orm/session.py", line 1718, in flush
    self._flush(objects)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/orm/session.py", line 1789, in _flush
    flush_context.execute()
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/orm/unitofwork.py", line 331, in execute
    rec.execute(self)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/orm/unitofwork.py", line 475, in execute
    uow
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/orm/persistence.py", line 64, in save_obj
    table, insert)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/orm/persistence.py", line 530, in _emit_insert_statements
    execute(statement, multiparams)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 1843, in _handle_dbapi_exception
    from e
sqlalchemy.exc.ProgrammingError: (ProgrammingError) 1064 (42000): 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 '%(tz_name),(%(tz_name),(%(tz_name),(%(tz_name),(%(tz_name),(%(tz_name),(%(tz_nam' at line 1 'INSERT INTO timezone (tz_name, description) VALUES (%(tz_name)s, %(description)s)' ({'tz_name': 'Canada/Atlantic', 'description': 'Atlantic (UTC-4)'}, {'tz_name': 'Canada/Central', 'description': 'Central (UTC-6)'}, {'tz_name': 'Canada/Eastern', 'description': 'Eastern (UTC-5)'}, {'tz_name': 'Canada/Mountain', 'description': 'Mountain (UTC-7)'}, {'tz_name': 'Canada/Newfoundland', 'description': 'Newfoundland (UTC-3:30)'}, {'tz_name': 'Canada/Pacific', 'description': 'Pacific (UTC-8)'}, {'tz_name': 'Canada/Saskatchewan', 'description': 'Saskatchewan (UTC-6)'}, {'tz_name': 'Canada/Yukon', 'description': 'Yukon (UTC-8)'})

似乎代码中的某些地方没有进行正确的标签替换。

Answer 1

我向 SQLAlchemy 开发人员提交了一个错误，发现问题出在 mysqlconnector 上。这是错误报告:http://www.sqlalchemy.org/trac/ticket/2599

这是简短的版本......正在改变

engine = create_engine('mysql+mysqlconnector://root@127.0.0.1/mainserver?charset=utf8&use_unicode=0', echo=True)

进入

engine = create_engine('mysql+mysqlconnector://root@127.0.0.1/mainserver?charset=utf8&use_unicode=0', paramstyle='format', echo=True)

将通过解决 mysqlconnector 的问题来解决问题。