1. Mike Bayer Avatar Mike Bayer
  2. Untitled project
  3. sqlalchemy_old
  4. Issues

adding multiple objects results in bad SQL for mysql

Issue #2599 resolved
Former user created an issue

I apologize in advance if this is something I'm doing wrong, but it seems like this code should work:

from sqlalchemy import (Column, String, create_engine)
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([= "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)"),
    ](
        Timezone(tz_name))
    session.flush()
    session.commit()

I get the following back:

2012-11-02 19:07:07,423 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2012-11-02 19:07:07,423 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 19:07:07,426 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2012-11-02 19:07:07,426 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 19:07:07,427 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
2012-11-02 19:07:07,427 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 19:07:07,447 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2012-11-02 19:07:07,447 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 19:07:07,448 INFO sqlalchemy.engine.base.Engine DESCRIBE `timezone`
2012-11-02 19:07:07,448 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 19:07:07,450 INFO sqlalchemy.engine.base.Engine 
DROP TABLE timezone
2012-11-02 19:07:07,450 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 19:07:07,456 INFO sqlalchemy.engine.base.Engine COMMIT
2012-11-02 19:07:07,456 INFO sqlalchemy.engine.base.Engine DESCRIBE `timezone`
2012-11-02 19:07:07,457 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 19:07:07,457 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-11-02 19:07:07,458 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 19:07:07,458 INFO sqlalchemy.engine.base.Engine {}
2012-11-02 19:07:07,487 INFO sqlalchemy.engine.base.Engine COMMIT
2012-11-02 19:07:07,489 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-11-02 19:07:07,490 INFO sqlalchemy.engine.base.Engine INSERT INTO timezone (tz_name, description) VALUES (%(tz_name)s, %(description)s)
2012-11-02 19:07:07,490 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 19:07:07,491 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 31, 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)'})

It looks like something is not properly filling in the %-tags and passing it straight on to mysql.

Comments (3)

  1. Mike Bayer repo owner

    yeah this is very similar to that MySQL-connector Python bug I told you about, and is probably a py3k incarnation of the same thing. On my Py2K version I get the current incarnation of it, "incomplete format". It does not implement pyformat parameters correctly, but reports "pyformat" as the default paramstyle.

    Luckily I noticed that I made the "paramstyle" configurable (way back in 2005, even), so we can override that, works for me on py2k:

        engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/test', paramstyle='format', echo=True)

    so you might be able to stick with mysql-connector if that works.

    also, a very good driver that supports Python3 is OurSQL: http://packages.python.org/oursql/ as well as PyMySQL, a pure-Python driver that also supports Py3k: https://github.com/petehunt/PyMySQL

  4. Log in to comment
Assignee
–
Type
bug
Priority
major
Status
resolved
Component
mysql
Milestone
–
Votes
0
Watchers
0
Jira: the preferred issue tracker for Bitbucket. Join the team!