SQLAlchemy error inserting multiple objects

Question

This likely something simple I'm missing, but I have no idea what it is.

Here's my code:

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://[email protected]/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()

And here's the output with errors that occur:

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)'})

It seems like something in the code isn't doing proper tag replacement.

Does it work if you do a add for each tz, rather than 1 add_all? — Gary van der Merwe
– Gary van der Merwe, Commented Nov 2, 2012 at 20:36
I see that your primary key is type of string. Maybe using a new column with type Integer could help. — vardars
– vardars, Commented Nov 3, 2012 at 18:01

Tim Tisdall · Accepted Answer · 2012-11-03 22:37:31Z

1

I filed a bug with the SQLAlchemy devs and found that the issue was with the mysqlconnector. Here's the bug report: http://www.sqlalchemy.org/trac/ticket/2599

Here's the short version... changing

engine = create_engine('mysql+mysqlconnector://[email protected]/mainserver?charset=utf8&use_unicode=0', echo=True)

into

engine = create_engine('mysql+mysqlconnector://[email protected]/mainserver?charset=utf8&use_unicode=0', paramstyle='format', echo=True)

will fix the problem by working around the issue with mysqlconnector.

answered Nov 3, 2012 at 22:37

Tim Tisdall

10.5k3 gold badges60 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLAlchemy error inserting multiple objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related