Forums

sqlalchemy.exc.ProgrammingError

Hi, I am running a Bottle app with sqlalchemy for persistence with MySQL.My engine is created like this: engine = create_engine('mysql+mysqldb://...', pool_recycle=280) And I installed: pip3.4 install --user https://github.com/davispuh/MySQL-for-Python-3/archive/1.0.tar.gz

I have the following error when I try to do a common session.query(...) operation:

2014-10-14 20:21:17,577 :Traceback (most recent call last):
2014-10-14 20:21:17,626 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 940, in     _execute_context
2014-10-14 20:21:17,630 :    context)
2014-10-14 20:21:17,633 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 435, in do_execute
2014-10-14 20:21:17,636 :    cursor.execute(statement, parameters)
2014-10-14 20:21:17,636 :  File "/home/jvadillo/.local/lib/python3.4/site-packages/MySQLdb/cursors.py", line 184, in execute
2014-10-14 20:21:17,636 :    self.errorhandler(self, exc, value)
2014-10-14 20:21:17,637 :  File "/home/jvadillo/.local/lib/python3.4/site-packages/MySQLdb/connections.py", line 37, in     defaulterrorhandler
2014-10-14 20:21:17,637 :    raise errorvalue
2014-10-14 20:21:17,637 :  File "/home/jvadillo/.local/lib/python3.4/site-packages/MySQLdb/cursors.py", line 171, in execute
2014-10-14 20:21:17,637 :    r = self._query(query)
2014-10-14 20:21:17,637 :  File "/home/jvadillo/.local/lib/python3.4/site-packages/MySQLdb/cursors.py", line 330, in _query
2014-10-14 20:21:17,637 :    rowcount = self._do_query(q)
2014-10-14 20:21:17,637 :  File "/home/jvadillo/.local/lib/python3.4/site-packages/MySQLdb/cursors.py", line 294, in _do_query
2014-10-14 20:21:17,637 :    db.query(q)
2014-10-14 20:21:17,637 :_mysql_exceptions.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' at line 3")
2014-10-14 20:21:17,637 :
2014-10-14 20:21:17,637 :The above exception was the direct cause of the following exception:
2014-10-14 20:21:17,637 :
2014-10-14 20:21:17,637 :Traceback (most recent call last):
2014-10-14 20:21:17,637 :  File "/usr/local/lib/python3.4/dist-packages/bottle.py", line 862, in _handle
2014-10-14 20:21:17,637 :    return route.call(**args)
2014-10-14 20:21:17,637 :  File "/usr/local/lib/python3.4/dist-packages/bottle.py", line 1729, in wrapper
2014-10-14 20:21:17,638 :    rv = callback(*a, **ka)
2014-10-14 20:21:17,641 :  File "/home/jvadillo/landatu/rest.py", line 34, in postDevice
2014-10-14 20:21:17,645 :    saveJSONData(deviceId, request.json)
2014-10-14 20:21:17,648 :  File "/home/jvadillo/landatu/persist_service.py", line 52, in saveJSONData
2014-10-14 20:21:17,648 :    parent = session.query(Device).get(deviceId) # will give you either Parent or None
2014-10-14 20:21:17,651 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", line 816, in get
2014-10-14 20:21:17,651 :    return loading.load_on_ident(self, key)
2014-10-14 20:21:17,651 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/loading.py", line 230, in     load_on_ident
2014-10-14 20:21:17,651 :    return q.one()
2014-10-14 20:21:17,651 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", line 2361, in one
2014-10-14 20:21:17,652 :    ret = list(self)
2014-10-14 20:21:17,652 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", line 2404, in __iter__
2014-10-14 20:21:17,652 :    return self._execute_and_instances(context)
2014-10-14 20:21:17,652 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", line 2419, in     _execute_and_instances
2014-10-14 20:21:17,652 :    result = conn.execute(querycontext.statement, self._params)
2014-10-14 20:21:17,652 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 720, in execute
2014-10-14 20:21:17,652 :    return meth(self, multiparams, params)
2014-10-14 20:21:17,652 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
2014-10-14 20:21:17,652 :    return connection._execute_clauseelement(self, multiparams, params)
2014-10-14 20:21:17,652 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 817, in     _execute_clauseelement
2014-10-14 20:21:17,652 :    compiled_sql, distilled_params
2014-10-14 20:21:17,652 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
2014-10-14 20:21:17,652 :    context)
2014-10-14 20:21:17,653 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
2014-10-14 20:21:17,653 :    exc_info
2014-10-14 20:21:17,653 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 174, in raise_from_cause
2014-10-14 20:21:17,653 :    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
2014-10-14 20:21:17,653 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 167, in reraise
2014-10-14 20:21:17,653 :    raise value.with_traceback(tb)
2014-10-14 20:21:17,653 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 940, in _execute_context
2014-10-14 20:21:17,653 :    context)
2014-10-14 20:21:17,653 :  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 435, in do_execute
2014-10-14 20:21:17,653 :    cursor.execute(statement, parameters)
2014-10-14 20:21:17,653 :  File "/home/jvadillo/.local/lib/python3.4/site-packages/MySQLdb/cursors.py", line 184, in execute
2014-10-14 20:21:17,654 :    self.errorhandler(self, exc, value)
2014-10-14 20:21:17,654 :  File "/home/jvadillo/.local/lib/python3.4/site-packages/MySQLdb/connections.py", line 37, in defaulterrorhandler
2014-10-14 20:21:17,654 :    raise errorvalue
2014-10-14 20:21:17,654 :  File "/home/jvadillo/.local/lib/python3.4/site-packages/MySQLdb/cursors.py", line 171, in execute
2014-10-14 20:21:17,654 :    r = self._query(query)
2014-10-14 20:21:17,654 :  File "/home/jvadillo/.local/lib/python3.4/site-packages/MySQLdb/cursors.py", line 330, in _query
2014-10-14 20:21:17,654 :    rowcount = self._do_query(q)
2014-10-14 20:21:17,654 :  File "/home/jvadillo/.local/lib/python3.4/site-packages/MySQLdb/cursors.py", line 294, in     _do_query
2014-10-14 20:21:17,654 :    db.query(q)
2014-10-14 20:21:17,654 :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' at line 3") b'SELECT device.id AS device_id, device.name AS device_name, device.`installDate` AS `device_installDate`, device.state AS device_state, device.lat AS device_lat, device.lon AS device_lon, device.network_id AS device_network_id \nFROM device \nWHERE device.id = %s' ('1001',)

Thank you very much, Jon

deleted-user-379326 | 1 post | Oct. 14, 2014, 8:29 p.m. | permalink

That looks like it may be a bug in MySQLdb for Python 3, or perhaps in SQLAlchemy. Try using mysql-connector-python to see whether that helps. Details are on this page under the heading "MySQL with Python 3 for Django". You'll probably have to change the connections string you use, so check the SQLAlchemy docs for details on that.

Staff glenn | 10036 posts | PythonAnywhere staff | Oct. 15, 2014, 9:54 a.m. | permalink

Update: the recommended MySQL library for Python 3.x has now changed. If you're not using a virtualenv then you don't need to install anything -- it's already installed and you just need to import MySQLdb. If you are using a virtualenv, then start a bash console inside the virtualenv and run

pip install mysqlclient
Staff giles | 12640 posts | PythonAnywhere staff | May 9, 2016, 11:52 a.m. | permalink