首页 / 问题 / 正文

Sqlalchemy 查询参数未插入

1 投票
2 回答
1604 浏览
提问于 2025-04-18 06:47

我在用SQLAlchemy的声明式方式来操作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()

这段代码在执行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')

异常的完整堆栈信息在帖子最后。

我发现参数没有正确地插入到查询字符串中。在文件MySQLdb/cursors.py的第163行，有一行代码query = query.format( *db.literal(args) )。这里的query变量是一个字符串。string.format函数接受像{}、{1}、{23}这样的替换字段，而不是%s。但是在query变量中的替换字段是%s。

在这里输入图片描述

所以，参数没有被插入到查询中。

我通过修改sqlalchemy/sql/compiler.py文件中的BIND_TEMPLATES变量解决了这个问题。我把'format': "%%s"改成了'format': "{}"。

但我很清楚地知道，有人成功地使用SQLAlchemy而没有遇到这个错误。问题的真正原因是什么呢？也许是我安装了一些不兼容的版本？

我使用的是Python 3.4，操作系统是Windows 7 x64，
Mysql连接器是：MySQL_python-1.2.3-py3.4-win-amd64.egg

异常的完整堆栈信息：

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')
mysql 异常处理 sqlalchemy 版本兼容性 数据库操作 查询参数 声明式映射 python3.4

2 个回答

0

我找到了解决办法。也许这个办法不是最好的，也不一定适合所有人，但我还是分享出来。这个办法就是安装Mysql Connector，而不是MySQLDb，然后把连接字符串从 mysql+mysqldb://root:mypass@localhost/somedb 改成 mysql+connector://root:mypass@localhost/somedb。

如果你是Windows用户（就像我一样），并且遇到了这个问题：“安装mysql-python时出现错误：无法打开包含文件：'config-win.h'：没有这样的文件或目录”，那么你可以把Python版本从3.4降到3.3，并从这里下载适用于Python 3.3的预编译Mysql Connector：http://dev.mysql.com/downloads/connector/python/

这个办法对我有效。谢谢。

回答于 2025-04-18 由 Python大师
分享 举报
0

试着加上 __table_args__ = {}。这个对我有效：

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__ = {}

更新：我运行了你的代码（除了把mysql的连接字符串改成适合的格式），在Python 2.7（64位，Linux系统）上运行没有任何问题（它创建了表，并插入了'hello world'）。依赖项：

MySQL-python==1.2.5
SQLAlchemy==0.9.2

仔细阅读你的帖子后，我发现你使用的是Python 3.4。http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html 上写道：

“所有SQLAlchemy模块和单元测试现在都可以在2.6及以后的任何Python解释器上良好运行，包括3.1和3.2。”

这里没有明确提到3.3和3.4。你可能遇到了SQLAlchemy的一个bug，或者它还没有更新到3.4。我建议你安装2.7（它们应该可以在同一系统上共存），使用virtualenv来管理2.7的环境，安装依赖项，然后重新运行你的代码。

另一个可能导致问题的原因是3.4的MySQL驱动程序。很遗憾地说，Python 3.*下对MySQL的支持非常不足：我工作单位的团队发现这些驱动程序不仅有内存泄漏，而且在高负载下无法处理很多连接。你可能在2.7上会更好，直到驱动程序修复好。或者，干脆换用Postgres作为数据库。

回答于 2025-04-18 由 Python大师
分享 举报

撰写回答