Sqlalchemy query parameters are not inserted

Question

I use SQLAlchemy declarative to work with MySQL.

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from 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)

i = Item()
i.dt = datetime.datetime.now()
i.title = "hello world"
session.add(i)
session.commit()

This code throws an exception on session.commit():

sqlalchemy.exc.ProgrammingError: (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 '%s, %s)' at line 1") b'INSERT INTO item (dt, title) VALUES (%s, %s)' (datetime.datetime(2014, 5, 16, 20, 58, 7, 729245), 'hello world')

The full stack trace of exception is in the end of the post.

I have found out that the parameters are not being inserted into the query string properly. There is as a line query = query.format( *db.literal(args) ) in the file MySQLdb/cursors.py, on the line 163. The query variable is a string. The string.format function accepts replacement fields like {}, {1}, {23}, not %s. But the replacement fields in the query variable are %s.

enter image description here

So, parameters are not being inserted into the query.

I could solve the problem by making changes in the sqlalchemy/sql/compiler.py file, variable BIND_TEMPLATES. I have changed 'format': "%%s" to 'format': "{}".

But I clearly understand that people use successfully sqlalchemy without this error. What is the real cause of the problem? Maybe I have installed some 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 the 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 the 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 the 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)
sqlalchemy.exc.ProgrammingError: (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 '%s, %s)' at line 1") b'INSERT INTO item (dt, title) VALUES (%s, %s)' (datetime.datetime(2014, 5, 16, 20, 58, 7, 729245), 'hello world')

LetMeSOThat4U · Answer 1 · 2014-05-17T15:04:39.223

Try adding __table_args__ = {}. This works for me:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy.orm import relation, relationship

DeclarativeBase = declarative_base(cls=DeferredReflection)

class MyItem(DeclarativeBase):
    __tablename__ = 'myitem'
    __table_args__ = {}

UPDATE: I have ran your code as is (apart from changing mysql url string for engine of course) using Python 2.7 (x64, on Linux) and it runs without any problem (it created the table and inserted 'hello world' into it). dependencies:

MySQL-python==1.2.5
SQLAlchemy==0.9.2

Reading your post more carefully i see you have used Python 3.4. http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html states:

"All SQLAlchemy modules and unit tests are now interpreted equally well with any Python interpreter from 2.6 forward, including the 3.1 and 3.2 interpreters."

This does not mention 3.3 and 3.4 explicitly. You might have encountered a bug in SA or it just wasn't updated yet for 3.4. I'd suggest installing 2.7 (they should be able to coexist on a single system), use virtualenv for 2.7, install deps and rerun your code.

Another possible source of the problem are MySQL drivers for 3.4. I'm sad to say this but support for MySQL under Python 3.* is severely lacking: the team at my workplace found the drivers not just leaking, but unable to handle many connections under high load. You might be better off with 2.7 until the drivers are fixed. That, or just change to Postgres as db.

\_\_table_args__ = {} doesn't solve the problem. Adding column names doesn't too. — Pavel L, May 16 '14 at 20:29

score 0 · Answer 2 · edited May 23 '17 at 10:26

I have found a solution. Maybe it isn't the best one, and it isn't suitable for all. But here it is. The solution is to install Mysql Connector instead of MySQLDb and to change the connection string from mysql+mysqldb://root:mypass@localhost/somedb to mysql+connector://root:mypass@localhost/somedb.

If you are Windows user (as I am), and you have this problem: "Cannot open include file: 'config-win.h': No such file or directory" while installing mysql-python , so you can downgrade Python version from 3.4 to 3.3 and to download the pre-compiled Mysql Connector for python 3.3 from here: http://dev.mysql.com/downloads/connector/python/

It worked for me. Thanks.

Sqlalchemy query parameters are not inserted

2 Answers2