是否有一种简单的方法来遍历列名和值对?
我的SQLAlchemy版本是0.5.6
下面是我尝试使用dict(row)的示例代码:
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
print "sqlalchemy version:",sqlalchemy.__version__
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
)
metadata.create_all(engine)
class User(declarative_base()):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
def __init__(self, name):
self.name = name
Session = sessionmaker(bind=engine)
session = Session()
user1 = User("anurag")
session.add(user1)
session.commit()
# uncommenting next line throws exception 'TypeError: 'User' object is not iterable'
#print dict(user1)
# this one also throws 'TypeError: 'User' object is not iterable'
for u in session.query(User).all():
print dict(u)
在我的系统输出上运行这段代码:
Traceback (most recent call last):
File "untitled-1.py", line 37, in <module>
print dict(u)
TypeError: 'User' object is not iterable
如OP所述，调用dict初始化器会引发一个异常，消息为“User”对象不可迭代。所以真正的问题是如何使一个SQLAlchemy模型可迭代?
We'll have to implement the special methods __iter__ and __next__, but if we inherit directly from the declarative_base model, we would still run into the undesirable "_sa_instance_state" key. What's worse, is we would have to loop through __dict__.keys() for every call to __next__ because the keys() method returns a View -- an iterable that is not indexed. This would increase the time complexity by a factor of N, where N is the number of keys in __dict__. Generating the dict would cost O(N^2). We can do better.
我们可以实现自己的基类，它实现所需的特殊方法，并存储可以通过索引访问的列名列表，从而降低生成O(N)字典的时间复杂性。这有一个额外的好处，我们可以定义一次逻辑，并在任何时候从基类继承，我们希望我们的模型类是可迭代的。
class IterableBase(declarative_base()):
__abstract__ = True
def _init_keys(self):
self._keys = [c.name for c in self.__table__.columns]
self._dict = {c.name: getattr(self, c.name) for c in self.__table__.columns}
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self._init_keys()
def __setattr__(self, name, value):
super().__setattr__(name, value)
if name not in ('_dict', '_keys', '_n') and '_dict' in self.__dict__:
self._dict[name] = value
def __iter__(self):
self._n = 0
return self
def __next__(self):
if self._n >= len(self._keys):
raise StopIteration
self._n += 1
key = self._keys[self._n-1]
return (key, self._dict[key])
现在User类可以直接从IterableBase类继承。
class User(IterableBase):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
您可以确认，以User实例作为参数调用dict函数将返回所需的字典，没有"_sa_instance_state"。你可能已经注意到在IterableBase类中声明的__setattr__方法。这确保在初始化后属性发生变化或设置时更新_dict。
def main():
user1 = User('Bob')
print(dict(user1))
# outputs {'id': None, 'name': 'Bob'}
user1.id = 42
print(dict(user1))
# outputs {'id': 42, 'name': 'Bob'}
if __name__ == '__main__':
main()
正在迭代的表达式求值为模型对象列表，而不是行。下面是正确的用法:
for u in session.query(User).all():
print u.id, u.name
你真的需要把它们转换成字典吗?当然，有很多方法，但是你不需要SQLAlchemy的ORM部分:
result = session.execute(User.__table__.select())
for row in result:
print dict(row)
更新:看一下sqlalchemy.orm.attributes模块。它有一组处理对象状态的函数，这可能对您很有用，特别是instance_dict()。
我对马可·马里亚尼(Marco Mariani)的回答有一个变体，以装饰者的身份表达。主要的区别是它将处理实体列表，以及安全地忽略一些其他类型的返回值(这在使用mock编写测试时非常有用):
@decorator
def to_dict(f, *args, **kwargs):
result = f(*args, **kwargs)
if is_iterable(result) and not is_dict(result):
return map(asdict, result)
return asdict(result)
def asdict(obj):
return dict((col.name, getattr(obj, col.name))
for col in class_mapper(obj.__class__).mapped_table.c)
def is_dict(obj):
return isinstance(obj, dict)
def is_iterable(obj):
return True if getattr(obj, '__iter__', False) else False
