我的会话中没有添加一些对象

Question

我使用SQLAlchemy中的声明式样式创建了一些映射对象。我有一个名为ThermafuserReading的映射，它有一个由Time_stamp列（DateTime）和ThermafuserId列（一个整数）组成的主键，它还充当另一个名为Thermafuser的表的外键。这是类的定义

class ThermafuserReading(Base):
"""Class to map to the Thermafuser Readings table in the HVAC DB"""

__tablename__ = 'Thermafuser_Reading'

_timestamp = Column('Time_stamp', DateTime, primary_key = True)
_thermafuserId = Column('ThermafuserId', Integer, ForeignKey("Thermafuser.ThermafuserId"), primary_key = True)
_roomOccupied = Column('RoomOccupied', Boolean)
_zoneTemperature = Column('ZoneTemperature', Float)
_supplyAir = Column('SupplyAir', Float, nullable=True)
_airflowFeedback = Column('AirflowFeedback', Float, nullable=True)
_CO2Input = Column('CO2Input', Float, nullable=True)
_maxAirflow = Column('MaxAirflow', Float, nullable=True)
_minAirflow = Column('MinAirflow', Float, nullable=True)
_unoccupiedHeatingSetpoint = Column('UnoccupiedHeatingSetpoint', Float, nullable=True)
_unoccupiedCoolingSetpoint = Column('UnoccupiedCoolingSetpoint', Float, nullable=True)
_occupiedCoolingSetpoint = Column('OccupiedCoolingSetpoint', Float, nullable=True)
_occupiedHeatingSetpoint = Column('OccupiedHeatingSetpoint', Float, nullable=True)
_terminalLoad = Column('TerminalLoad', Float, nullable=True)

#Relationship between Thermafuser Reading and Thermafuser
_thermafuser = relationship("Thermafuser", back_populates = "_thermafuserReadings",  cascade = "all, delete-orphan", single_parent = True)

我用以下方式创建一个会话

^{pr2}$

在我的代码中，我创建了一个名为Thermafuser读数读数的列表，并将该列表添加到会话中session.add\u全部（读数）

以下是从阅读列表中打印的一些示例元素：

<ThermafuserReading(thermafuserId = '21', timestamp = '2016-12-31 23:30:00')>
<ThermafuserReading(thermafuserId = '21', timestamp = '2016-12-31 23:35:00')>
<ThermafuserReading(thermafuserId = '21', timestamp = '2016-12-31 23:40:00')>
<ThermafuserReading(thermafuserId = '21', timestamp = '2016-12-31 23:45:00')>
<ThermafuserReading(thermafuserId = '21', timestamp = '2016-12-31 23:50:00')>
<ThermafuserReading(thermafuserId = '21', timestamp = '2016-12-31 23:55:00')>
<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-01 00:00:00')>

问题是，即使我做了，会话也只保留了列表中的最后一项session.add\u全部（阅读材料）例如，这是本节课的内容：

<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-01 00:00:00')>

我知道会话跟踪具有相同主键的对象，因此在会话中只插入一个此类对象的实例，但在这种情况下，主键（thermafuserId，timestamp）在每个实例中都是不同的。我不知道为什么会话只添加了我列表中的最后一个元素而忽略了其他元素。在

有什么想法吗？在

编辑：

我一直在做一些测试，找出了为什么只有列表的最后一个元素被添加到会话中的原因。问题在于我列表中每个对象的identity_键。这是我用于测试的代码：

for reading in readings:
    print(reading, mapper.identity_key_from_instance(reading))

这是一些结果

<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-14 23:15:00')> (<class 'hvacDBMapping.ThermafuserReading'>, (datetime.datetime(2017, 1, 15, 0, 0), 21))
<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-14 23:20:00')> (<class 'hvacDBMapping.ThermafuserReading'>, (datetime.datetime(2017, 1, 15, 0, 0), 21))
<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-14 23:25:00')> (<class 'hvacDBMapping.ThermafuserReading'>, (datetime.datetime(2017, 1, 15, 0, 0), 21))
<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-14 23:30:00')> (<class 'hvacDBMapping.ThermafuserReading'>, (datetime.datetime(2017, 1, 15, 0, 0), 21))
<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-14 23:35:00')> (<class 'hvacDBMapping.ThermafuserReading'>, (datetime.datetime(2017, 1, 15, 0, 0), 21))
<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-14 23:40:00')> (<class 'hvacDBMapping.ThermafuserReading'>, (datetime.datetime(2017, 1, 15, 0, 0), 21))
<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-14 23:45:00')> (<class 'hvacDBMapping.ThermafuserReading'>, (datetime.datetime(2017, 1, 15, 0, 0), 21))
<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-14 23:50:00')> (<class 'hvacDBMapping.ThermafuserReading'>, (datetime.datetime(2017, 1, 15, 0, 0), 21))
<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-14 23:55:00')> (<class 'hvacDBMapping.ThermafuserReading'>, (datetime.datetime(2017, 1, 15, 0, 0), 21))
<ThermafuserReading(thermafuserId = '21', timestamp = '2017-01-15 00:00:00')> (<class 'hvacDBMapping.ThermafuserReading'>, (datetime.datetime(2017, 1, 15, 0, 0), 21))

正如你所观察到的，功能sqlalchemy.orm.util.identity_key_from_instance（）无法为我的datetime对象正确创建标识密钥。在

有人能帮我弄清楚原因吗？在

编辑

这是一个说明问题的简化代码。此代码中没有与数据库的连接。这个问题第一次出现的代码要复杂得多，发布它只会造成混乱，但是这个代码会复制错误。在

Session = sessionmaker()
session = Session() 

mapper = inspect(ThermafuserReading)

#Open the csv file
csvFilePath = "/Users/davidlaredorazo/Box Sync/Data/Zone4/1C1A/1C1A 2016-12-31.csv"
with open(csvFilePath, 'r') as csvfile:

    reader = csv.reader(csvfile)
    componentId = 1
    count = 0

    reading = ThermafuserReading(None, componentId)

    for row in reader:

        if count == 0:
            count += 1
            continue

        #print(row)
        timestamp = parse(row[0], None, ignoretz = True)

        reading.timestamp = timestamp
        new_object = copy.copy(reading)
        new_object.timestamp = timestamp

        print(new_object, mapper.identity_key_from_instance(new_object))
        session.add(new_object)

print("new elements")
for new in session.new:
    print(new, mapper.identity_key_from_instance(new_object))

Answer 1

正如univerio在评论中提到的。我做错的是使用复制。复制要复制映射对象的实例，这会扰乱_sa_instance_state。解决方案是为我的实例创建一个“临时”复制函数。这是我使用的复制功能，这确实解决了问题。在

def copy_sqla_object(obj, omit_fk=True):
    """Given an SQLAlchemy object, creates a new object (FOR WHICH THE OBJECT
    MUST SUPPORT CREATION USING __init__() WITH NO PARAMETERS), and copies
    across all attributes, omitting PKs, FKs (by default), and relationship
    attributes."""
    cls = type(obj)
    mapper = class_mapper(cls)
    newobj = cls()  # not: cls.__new__(cls)
    pk_keys = set([c.key for c in mapper.primary_key])
    rel_keys = set([c.key for c in mapper.relationships])
    prohibited = pk_keys | rel_keys
    if omit_fk:
        fk_keys = set([c.key for c in mapper.columns if c.foreign_keys])
        prohibited = prohibited | fk_keys
    for k in [p.key for p in mapper.iterate_properties if p.key not in prohibited]:
        try:
            value = getattr(obj, k)
            setattr(newobj, k, value)
        except AttributeError:
            pass
    return newobj

有关此问题的详细讨论，请参见

https://groups.google.com/forum/#!topic/sqlalchemy/HVSxndh23m0