Re: [sqlalchemy] session.add() neglecting some of my objects to be added

[David Laredo Razo]

Thanks Mike, indeed, that was the problem. I solved it using an "ad hoc" 
copy function. This is the solution that worked for me in case somebody 
else incurs in the same mistake I did

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



On Friday, July 14, 2017 at 2:47:45 PM UTC-5, Mike Bayer wrote:
>
> On Fri, Jul 14, 2017 at 1:24 AM, David Laredo Razo 
>  wrote: 
>
>
> this code is the problem: 
>
> > 
> > new_object = copy.copy(reading) 
>
> copy() will copy the _sa_instance_state and prevent the session from 
> tracking the object correctly. 
>
> Correct pattern should be: 
>
> new_object = ThermafuserReading(None, componentId) 
>
> Only when you call the constructor (e.g. ThermafuserReading.__init__) 
> do you get a new InstanceState object dedicated to that object. So 
> don't use copy(). 
>
> There *are* ways to use copy() here instead but they are non-obvious 
> and not necessary for a simple case like this. 
>
>
>
>
> > new_object.timestamp = timestamp 
> > 
> > readings.append(new_object) 
> > 
> > #print(new_object, mapper.identity_key_from_instance(new_object)) 
> > #session.add(new_object) 
> > 
> > row_format = "{:>15}" * (len(header) + 1) 
> > 
> > print("Before adding to the session") 
> > print(row_format.format("", *header)) 
> > for reading in readings: 
> > insp = inspect(reading) 
> > row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent, 
> > insp.detached, insp.deleted, reading in session] 
> > print(row_format.format("", *row)) 
> > 
> > session.add_all(readings) 
> > 
> > print("\n#Elements in the session") 
> > print(session) 
> > for element in session: 
> > print(element) 
> > 
> > print("\nAfter adding to the session") 
> > print(row_format.format("", *header)) 
> > for reading in readings: 
> > insp = inspect(reading) 
> > row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent, 
> > insp.detached, insp.deleted, reading in session] 
> > print(row_format.format("", *row)) 
> > 
> > These are some results I obtained by comparing wheter the objects in my 
> list 
> > are in the session or not 
> > 
> > 
> > 
> > 
> > As you can observe, according to the results above the objects are 
> indeed 
> > inside the session but for some reason when I try to print whats 
> contained 
> > in the session by doing 
> > 
> > for element in session: 
> >print(element) 
> > 
> > I just get a None, what am I doing wrong? I dont see anything wrong in 
> my 
> > code, I hope you can help me clarify this. Thanks in advance. 
> > 
> > I will attach both my code and the tests data in case you want to try it 
> by 
> > yourself. 
> > 
> > 
> > 
> > 
> > 
> > On Thursday, July 13, 2017 at 8:27:04 AM UTC-5, Mike Bayer wrote: 
> >> 
> >> On Thu, Jul 13, 2017 at 12:31 AM, David Laredo Razo 
> >>  wrote: 
> >> > Hello, I am using SQLAlchemy version 1.2.0b1 
> >> > 
> >> > 
> >> > 
> >> > So far so go, the problem arises when I add readings to the session 
> via 
> >> > session.add_all(readings). I only get the last element in my list 
> added, 
> >> > e.g. 
> >> 
> >> there's no reason at all that would happen, other than what's in 
> >> "readings" is not what you'd expect. 
> >> 
> >> try iterating through every element in "readings" after the add_all(), 
> >> and do "obj in session". 
> >> 
> >> If some of these objects were from a different session, then they may 
> >> be "detached" as you put them in in which case they'd go into 
> >> session.identity_map, not session.new. 
> >> 
> >> 
> >> 
> >> 
> >> > 
> >> > for new in session.new: 
> >> >print(new, mapper.identity_key_from_instance(new_object)) 
> >> > 
> >> >  
> >> > (, (datetime.datetime(2017, 1, 
> 1, 
> >> > 0, 
> >> > 0), 1)) 
> >> > 
> >> > 
> >> > Why is this behavior? I have a test code and the test data in case 
> its 
> >> > needed to reproduce this behavior 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > -- 
> >> > SQLAlchemy - 
> >> > The Python SQL Toolkit and Object Relational Mapper 
> >> > 
> >> > http://www.sqlalchemy.org/ 
> >> > 
> >> > To post example code, please provide an MCVE: Minimal, Complete, and 

Re: [sqlalchemy] session.add() neglecting some of my objects to be added

[Mike Bayer]

On Fri, Jul 14, 2017 at 1:24 AM, David Laredo Razo
 wrote:


this code is the problem:

>
> new_object = copy.copy(reading)

copy() will copy the _sa_instance_state and prevent the session from
tracking the object correctly.

Correct pattern should be:

new_object = ThermafuserReading(None, componentId)

Only when you call the constructor (e.g. ThermafuserReading.__init__)
do you get a new InstanceState object dedicated to that object. So
don't use copy().

There *are* ways to use copy() here instead but they are non-obvious
and not necessary for a simple case like this.




> new_object.timestamp = timestamp
>
> readings.append(new_object)
>
> #print(new_object, mapper.identity_key_from_instance(new_object))
> #session.add(new_object)
>
> row_format = "{:>15}" * (len(header) + 1)
>
> print("Before adding to the session")
> print(row_format.format("", *header))
> for reading in readings:
> insp = inspect(reading)
> row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent,
> insp.detached, insp.deleted, reading in session]
> print(row_format.format("", *row))
>
> session.add_all(readings)
>
> print("\n#Elements in the session")
> print(session)
> for element in session:
> print(element)
>
> print("\nAfter adding to the session")
> print(row_format.format("", *header))
> for reading in readings:
> insp = inspect(reading)
> row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent,
> insp.detached, insp.deleted, reading in session]
> print(row_format.format("", *row))
>
> These are some results I obtained by comparing wheter the objects in my list
> are in the session or not
>
>
>
>
> As you can observe, according to the results above the objects are indeed
> inside the session but for some reason when I try to print whats contained
> in the session by doing
>
> for element in session:
>print(element)
>
> I just get a None, what am I doing wrong? I dont see anything wrong in my
> code, I hope you can help me clarify this. Thanks in advance.
>
> I will attach both my code and the tests data in case you want to try it by
> yourself.
>
>
>
>
>
> On Thursday, July 13, 2017 at 8:27:04 AM UTC-5, Mike Bayer wrote:
>>
>> On Thu, Jul 13, 2017 at 12:31 AM, David Laredo Razo
>>  wrote:
>> > Hello, I am using SQLAlchemy version 1.2.0b1
>> >
>> >
>> >
>> > So far so go, the problem arises when I add readings to the session via
>> > session.add_all(readings). I only get the last element in my list added,
>> > e.g.
>>
>> there's no reason at all that would happen, other than what's in
>> "readings" is not what you'd expect.
>>
>> try iterating through every element in "readings" after the add_all(),
>> and do "obj in session".
>>
>> If some of these objects were from a different session, then they may
>> be "detached" as you put them in in which case they'd go into
>> session.identity_map, not session.new.
>>
>>
>>
>>
>> >
>> > for new in session.new:
>> >print(new, mapper.identity_key_from_instance(new_object))
>> >
>> > 
>> > (, (datetime.datetime(2017, 1, 1,
>> > 0,
>> > 0), 1))
>> >
>> >
>> > Why is this behavior? I have a test code and the test data in case its
>> > needed to reproduce this behavior
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > --
>> > SQLAlchemy -
>> > The Python SQL Toolkit and Object Relational Mapper
>> >
>> > http://www.sqlalchemy.org/
>> >
>> > To post example code, please provide an MCVE: Minimal, Complete, and
>> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> > description.
>> > ---
>> > You received this message because you are subscribed to the Google
>> > Groups
>> > "sqlalchemy" group.
>> > To unsubscribe from this group and stop receiving emails from it, send
>> > an
>> > email to sqlalchemy+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  

Re: [sqlalchemy] session.add() neglecting some of my objects to be added

[David Laredo Razo]

i did as you instructed me but the error persists. 

This is the example code im talking about

Session = sessionmaker()
session = Session() 

mapper = inspect(ThermafuserReading)
readings = list()

header = ["hex(id(object))", "is transient", "is pending", "is persistent", 
"is detached", "is deleted", "is in session"]

#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

readings.append(new_object)

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

row_format = "{:>15}" * (len(header) + 1)

print("Before adding to the session")
print(row_format.format("", *header))
for reading in readings:
insp = inspect(reading)
row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent, 
insp.detached, insp.deleted, reading in session]
print(row_format.format("", *row))

session.add_all(readings)

print("\n#Elements in the session")
print(session)
for element in session:
print(element)

print("\nAfter adding to the session")
print(row_format.format("", *header))
for reading in readings:
insp = inspect(reading)
row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent, 
insp.detached, insp.deleted, reading in session]
print(row_format.format("", *row))

These are some results I obtained by comparing wheter the objects in my 
list are in the session or not







As you can observe, according to the results above the objects are indeed 
inside the session but for some reason when I try to print whats contained 
in the session by doing

for element in session:
   print(element)

I just get a None, what am I doing wrong? I dont see anything wrong in my 
code, I hope you can help me clarify this. Thanks in advance.

I will attach both my code and the tests data in case you want to try it by 
yourself.


 


On Thursday, July 13, 2017 at 8:27:04 AM UTC-5, Mike Bayer wrote:
>
> On Thu, Jul 13, 2017 at 12:31 AM, David Laredo Razo 
>  wrote: 
> > Hello, I am using SQLAlchemy version 1.2.0b1 
> > 
> > 
> > 
> > So far so go, the problem arises when I add readings to the session via 
> > session.add_all(readings). I only get the last element in my list added, 
> > e.g. 
>
> there's no reason at all that would happen, other than what's in 
> "readings" is not what you'd expect. 
>
> try iterating through every element in "readings" after the add_all(), 
> and do "obj in session". 
>
> If some of these objects were from a different session, then they may 
> be "detached" as you put them in in which case they'd go into 
> session.identity_map, not session.new. 
>
>
>
>
> > 
> > for new in session.new: 
> >print(new, mapper.identity_key_from_instance(new_object)) 
> > 
> >  
> > (, (datetime.datetime(2017, 1, 1, 
> 0, 
> > 0), 1)) 
> > 
> > 
> > Why is this behavior? I have a test code and the test data in case its 
> > needed to reproduce this behavior 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > You received this message because you are subscribed to the Google 
> Groups 
> > "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an 
> > email to sqlalchemy+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send 

Re: [sqlalchemy] session.add() neglecting some of my objects to be added

[Mike Bayer]

On Thu, Jul 13, 2017 at 12:31 AM, David Laredo Razo
 wrote:
> Hello, I am using SQLAlchemy version 1.2.0b1
>
>
>
> So far so go, the problem arises when I add readings to the session via
> session.add_all(readings). I only get the last element in my list added,
> e.g.

there's no reason at all that would happen, other than what's in
"readings" is not what you'd expect.

try iterating through every element in "readings" after the add_all(),
and do "obj in session".

If some of these objects were from a different session, then they may
be "detached" as you put them in in which case they'd go into
session.identity_map, not session.new.




>
> for new in session.new:
>print(new, mapper.identity_key_from_instance(new_object))
>
> 
> (, (datetime.datetime(2017, 1, 1, 0,
> 0), 1))
>
>
> Why is this behavior? I have a test code and the test data in case its
> needed to reproduce this behavior
>
>
>
>
>
>
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] session.add() neglecting some of my objects to be added

[David Laredo Razo]

Hello, I am using SQLAlchemy version 1.2.0b1

I created some mapped objects using the declarative style in SQLAlchemy. I 
have a mapping called ThermafuserReading which has a composed primary key 
made up of the Time_stamp column which is DateTime and ThermafuserId column 
which is an Integer and also acts as a Foreign Key to another table called 
Thermafuser. This is the definition of the class

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)


I am creating a session in the following way

sqlengine = 
sqlalchemy.create_engine("mysql+mysqldb://user:password@localhost:3306/HVAC")
Session = sessionmaker(bind=sqlengine)
session = Session()


At some point in my code I am creating a list called readings of 
Thermafuser Readings and adding such list the session via 
session.add_all(readings)

This are some example elements printed from the list readings:

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

 
(, (datetime.datetime(2016, 12, 31, 23, 
40), 1))

 
(, (datetime.datetime(2016, 12, 31, 23, 
45), 1))

 
(, (datetime.datetime(2016, 12, 31, 23, 
50), 1))

 
(, (datetime.datetime(2016, 12, 31, 23, 
55), 1))

 
(, (datetime.datetime(2017, 1, 1, 0, 0), 
1))


So far so go, the problem arises when I add readings to the session via 
session.add_all(readings). I only get the last element in my list added, 
e.g.

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

 (, 
(datetime.datetime(2017, 1, 1, 0, 0), 1))


Why is this behavior? I have a test code and the test data in case its 
needed to reproduce this behavior









-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.