python - sqlalchemy 核心和 sqlite 出现 %s 问题

Question

当我尝试将 %s 语法与 sqlalchemy 和 sqlite 一起使用时，出现错误。与 postgresql 相同的语法可以正常工作:

import sqlalchemy
e = sqlalchemy.create_engine('sqlite:////tmp/x.db?timeout=120000')
e.execute('select * from people where name = %s;', 'joe').fetchall()

我明白了:

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1396, in _cursor_execute
    context)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 301, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near "%": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1790, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1191, in execute
    params)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1287, in _execute_text
    return self.__execute_context(context)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1302, in __execute_context
    context.parameters[0], context=context)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1403, in _cursor_execute
    context)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1360, in _handle_dbapi_exception
    from e
sqlalchemy.exc.OperationalError: (OperationalError) near "%": syntax error 'select * from people where name = %s;' ('joe',)
>>> 

同样，postgres 也返回一个列表或行。这是一个错误吗？或者是预期的结果？

Answer 1

'select * from people where name = %s;', 'joe' 中的字符串格式存在一个小问题

%s 用于字符串格式化，%s 期望字符串后面有 % 某种要替换的数据

您应该从 %s;', 'joe' 中删除 , 并添加 % 来获取

In [10]: 'select * from people where name = %s;' %'joe'
Out[10]: 'select * from people where name = joe;'