Re: [sqlalchemy] session.add() neglecting some of my objects to be added
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
> <davidl...@gmail.com > 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
> >> <davidl...@gmail.com> 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
Re: [sqlalchemy] session.add() neglecting some of my objects to be added
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
<https://lh3.googleusercontent.com/-88hJ0sjxu2M/WWhUYMhRSiI/Ag0/kXliLRlFjZU-ZYwpMhUD5w_tVgNTOf9dgCLcBGAs/s1600/session_error1.png>
<https://lh3.googleusercontent.com/-Vvuk4avT75o/WWhUj98bouI/Ag4/FoPlU2HC3lk-GOC6qD1jihZKWIC-0SqXwCLcBGAs/s1600/session_error2.png>
<https://lh3.googleusercontent.com/-mkaRplvoUes/WWhUoF74sqI/Ag8/uztkwWPac-I1WxRDdj8-mQK2mgi7Fu9mACLcBGAs/s1600/session_error3.png>
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
> <davidl...@gmail.com > 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))
> >
> > <ThermafuserReading(thermafuserId = '1', timestamp = '2017-01-01
> 00:00:00')>
> > (, (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
&g
[sqlalchemy] Re: session.add() neglecting some of my objects to be added
Sorry I made a mistake, when printing whats inside the session I do it this
way
for new_object in session.new:
print(new_object, mapper.identity_key_from_instance(new_object))
On Wednesday, July 12, 2017 at 11:31:06 PM UTC-5, David Laredo Razo wrote:
>
> 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))
>
> <ThermafuserReading(thermafuserId = '1', timestamp = '2016-12-31 23:40:00')>
> (, (datetime.datetime(2016, 12, 31, 23,
> 40), 1))
>
> <ThermafuserReading(thermafuserId = '1', timestamp = '2016-12-31 23:45:00')>
> (, (datetime.datetime(2016, 12, 31, 23,
> 45), 1))
>
> <ThermafuserReading(thermafuserId = '1', timestamp = '2016-12-31 23:50:00')>
> (, (datetime.datetime(2016, 12, 31, 23,
> 50), 1))
>
> <ThermafuserReading(thermafuserId = '1', timestamp = '2016-12-31 23:55:00')>
> (, (datetime.datetime(2016, 12, 31, 23,
> 55), 1))
>
> <ThermafuserReading(thermafuserId = '1', timestamp = '2017-01-01 00:00:00')>
> (, (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))
>
> <ThermafuserReading(thermafuserId = '1', timestamp = '2017-01-01
> 00:00:00')> (,
> (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] session.add() neglecting some of my objects to be added
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.
