SQLAlchemy插入数据报错：'method' object is not iterable 如何解决？

问题场景 #

使用SQLAlchemy向数据库插入数据时触发如下错误：

TypeError: 'method' object is not iterable

原代码 #

with engine.connect() as conn:
    conn.execute(
    insert(text('users')).values(
    {"fio": name, "datar": '11.08.1984', "id_role": randint(1, 2)}
    ))
    conn.commit()

完整错误栈 #

File "C:\Users\gibso\PycharmProjects\test_task\main.py", line 41, in registration
conn.execute(
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1011, in execute
return meth(self, multiparams, params)
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1115, in _execute_clauseelement
compiled_sql = elem.compile(
File "<string>", line 1, in <lambda>
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 481, in compile
return self._compiler(dialect, bind=bind, **kw)
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 487, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 592, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 322, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 352, in process
return obj._compiler_dispatch(self, **kwargs)
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 96, in _compiler_dispatch
return meth(self, **kw)
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 2467, in visit_insert
crud_params = crud._setup_crud_params(
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\crud.py", line 64, in _setup_crud_params
return _get_crud_params(compiler, stmt, **kw)
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\crud.py", line 158, in _get_crud_params
_scan_cols(
File "C:\Users\gibso\PycharmProjects\test_task\venv\lib\site-packages\sqlalchemy\sql\crud.py", line 316, in _scan_cols
for c in cols:
TypeError: 'method' object is not iterable

问题原因 #

错误出在insert(text('users'))这一行：insert()函数的参数应该是表名字符串或者SQLAlchemy定义的Table对象，而你传入了text('users')。text()用于构造原生SQL语句片段，不能直接作为insert()的目标表参数，这会导致SQLAlchemy无法正确解析表的列结构，进而在内部扫描列时触发“方法不可迭代”的错误。

解决方案 #

提供两种可行的修正方式：

方式1：直接传入表名字符串 #

去掉text()包装，直接用表名作为insert()的参数：

with engine.connect() as conn:
    conn.execute(
        insert('users').values(
            {"fio": name, "datar": '11.08.1984', "id_role": randint(1, 2)}
        ))
    conn.commit()

方式2：使用SQLAlchemy的Table对象（推荐） #

如果已经通过SQLAlchemy定义了表结构，直接传入对应的Table对象：

# 先定义表结构（示例）
from sqlalchemy import Table, Column, String, Date, Integer, MetaData

metadata = MetaData()
users_table = Table(
    'users', metadata,
    Column('fio', String),
    Column('datar', Date),
    Column('id_role', Integer)
)

# 插入数据
with engine.connect() as conn:
    conn.execute(
        insert(users_table).values(
            {"fio": name, "datar": '11.08.1984', "id_role": randint(1, 2)}
        ))
    conn.commit()

这两种方式都能让SQLAlchemy正确识别目标表的列信息，避免迭代错误。

内容的提问来源于stack exchange，提问作者jiezzzzzzzzz