python - Sqlalchemy query parameters are not inserted -

i'm newbie in python, , have question. use sqlalchemy declarative work mysql. here simple code:

# -*- coding: utf-8 -*  sqlalchemy import * sqlalchemy.ext.declarative import declarative_base sqlalchemy.orm import relation, sessionmaker import datetime  engine = create_engine('mysql+mysqldb://root:mypass@localhost/somedb')  base = declarative_base() base.metadata.bind = engine dbsession = sessionmaker(bind=engine) session = dbsession()  class item(base):      __tablename__ = 'item'      id = column(integer, primary_key=true)     dt = column(datetime)     title = column(unicodetext)   base.metadata.create_all(engine)  = item() i.dt = datetime.datetime.now() i.title = "hello world" session.add(i) session.commit() 

this code throws exception on session.commit():

sqlalchemy.exc.programmingerror: (programmingerror) (1064, "you have error in sql syntax; check manual corresponds mysql server version right syntax use near '%s, %s)' @ line 1") b'insert item (dt, title) values (%s, %s)' (datetime.datetime(2014, 5, 16, 20, 58, 7, 729245), 'hello world') 

the full stack trace of exception in end of post.

i have found out parameters not being inserted query string properly. there line query = query.format( *db.literal(args) ) in file mysqldb/cursors.py, on line 163. query variable string. string.format function accepts replacement fields {}, {1}, {23}, not %s. replacement fields in query variable %s. here screenshot:

so, parameters not being inserted query.

i solve problem making changes in sqlalchemy/sql/compiler.py file, variable bind_templates. have changed 'format': "%%s" 'format': "{}"

but understand people use sqlalchemy without error. real cause of problem? maybe have installed incompatible versions?

i use python 3.4, on windows 7 x64, mysql connector: mysql_python-1.2.3-py3.4-win-amd64.egg

the full stack trace of exception:

traceback (most recent call last):   file "c:\program files (x86)\jetbrains\pycharm community edition 3.1.3\helpers\pydev\pydevd.py", line 1539, in <module>     debugger.run(setup['file'], none, none)   file "c:\program files (x86)\jetbrains\pycharm community edition 3.1.3\helpers\pydev\pydevd.py", line 1150, in run     pydev_imports.execfile(file, globals, locals) #execute script   file "c:\program files (x86)\jetbrains\pycharm community edition 3.1.3\helpers\pydev\_pydev_execfile.py", line 37, in execfile     exec(compile(contents+"\n", file, 'exec'), glob, loc) #execute script   file "c:/work/midmay/midmay.parser/scripts/test.py", line 30, in <module>     session.commit()   file "c:\python34\lib\site-packages\sqlalchemy\orm\session.py", line 765, in commit     self.transaction.commit()   file "c:\python34\lib\site-packages\sqlalchemy\orm\session.py", line 370, in commit     self._prepare_impl()   file "c:\python34\lib\site-packages\sqlalchemy\orm\session.py", line 350, in _prepare_impl     self.session.flush()   file "c:\python34\lib\site-packages\sqlalchemy\orm\session.py", line 1903, in flush     self._flush(objects)   file "c:\python34\lib\site-packages\sqlalchemy\orm\session.py", line 2021, in _flush     transaction.rollback(_capture_exception=true)   file "c:\python34\lib\site-packages\sqlalchemy\util\langhelpers.py", line 57, in __exit__     compat.reraise(exc_type, exc_value, exc_tb)   file "c:\python34\lib\site-packages\sqlalchemy\util\compat.py", line 168, in reraise     raise value   file "c:\python34\lib\site-packages\sqlalchemy\orm\session.py", line 1985, in _flush     flush_context.execute()   file "c:\python34\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 370, in execute     rec.execute(self)   file "c:\python34\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 523, in execute     uow   file "c:\python34\lib\site-packages\sqlalchemy\orm\persistence.py", line 64, in save_obj     mapper, table, insert)   file "c:\python34\lib\site-packages\sqlalchemy\orm\persistence.py", line 594, in _emit_insert_statements     execute(statement, params)   file "c:\python34\lib\site-packages\sqlalchemy\engine\base.py", line 720, in execute     return meth(self, multiparams, params)   file "c:\python34\lib\site-packages\sqlalchemy\sql\elements.py", line 317, in _execute_on_connection     return connection._execute_clauseelement(self, multiparams, params)   file "c:\python34\lib\site-packages\sqlalchemy\engine\base.py", line 817, in _execute_clauseelement     compiled_sql, distilled_params   file "c:\python34\lib\site-packages\sqlalchemy\engine\base.py", line 947, in _execute_context     context)   file "c:\python34\lib\site-packages\sqlalchemy\engine\base.py", line 1108, in _handle_dbapi_exception     exc_info   file "c:\python34\lib\site-packages\sqlalchemy\util\compat.py", line 174, in raise_from_cause     reraise(type(exception), exception, tb=exc_tb, cause=exc_value)   file "c:\python34\lib\site-packages\sqlalchemy\util\compat.py", line 167, in reraise     raise value.with_traceback(tb)   file "c:\python34\lib\site-packages\sqlalchemy\engine\base.py", line 940, in _execute_context     context)   file "c:\python34\lib\site-packages\sqlalchemy\engine\default.py", line 435, in do_execute     cursor.execute(statement, parameters)   file "c:\python34\lib\site-packages\mysql_python-1.2.3-py3.4-win-amd64.egg\mysqldb\cursors.py", line 184, in execute     self.errorhandler(self, exc, value)   file "c:\python34\lib\site-packages\mysql_python-1.2.3-py3.4-win-amd64.egg\mysqldb\connections.py", line 37, in defaulterrorhandler     raise errorvalue   file "c:\python34\lib\site-packages\mysql_python-1.2.3-py3.4-win-amd64.egg\mysqldb\cursors.py", line 171, in execute     r = self._query(query)   file "c:\python34\lib\site-packages\mysql_python-1.2.3-py3.4-win-amd64.egg\mysqldb\cursors.py", line 330, in _query     rowcount = self._do_query(q)   file "c:\python34\lib\site-packages\mysql_python-1.2.3-py3.4-win-amd64.egg\mysqldb\cursors.py", line 294, in _do_query     db.query(q) 

try adding __table_args__ = {}. works me:

from sqlalchemy import * sqlalchemy.ext.declarative import declarative_base, deferredreflection sqlalchemy.orm import relation, relationship  declarativebase = declarative_base(cls=deferredreflection)  class myitem(declarativebase):     __tablename__ = 'myitem'     __table_args__ = {} 

update: have ran code (apart changing mysql url string engine of course) using python 2.7 (x64, on linux) , runs without problem (it created table , inserted 'hello world' it). dependencies:

mysql-python==1.2.5 sqlalchemy==0.9.2 

reading post more see have used python 3.4. http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html states:

"all sqlalchemy modules , unit tests interpreted equally python interpreter 2.6 forward, including 3.1 , 3.2 interpreters."

this not mention 3.3 , 3.4 explicitly. might have encountered bug in sa or wasn't updated yet 3.4. i'd suggest installing 2.7 (they should able coexist on single system), use virtualenv 2.7, install deps , rerun code.

another possible source of problem mysql drivers 3.4. i'm sad support mysql under python 3.* severely lacking: team @ workplace found drivers not leaking, unable handle many connections under high load. might better off 2.7 until drivers fixed. that, or change postgres db.