Re: [sqlalchemy] session.add with insert-or-update

[Keith Edmunds]

Thanks Jonathan. This is a very low traffic application, so not a problem 
but I appreciate you mentioning it.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/a93a8e0c-5c5e-4d64-98d8-be3725c5e54f%40googlegroups.com.

Re: [sqlalchemy] session.add with insert-or-update

[Jonathan Vanasco]

FWIW: If your application is high-traffic/high-concurrency, depending on 
how your transactions are scoped within the code you may want to do the 
getcreate or create step that calls `.flush` within an exception block or 
savepoint, to catch duplicate inserts.

I've only had to do this on 2 (out of dozens) of projects, but it was a 
substantial improvement in performance.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/96f92929-8ff2-45b1-b93b-c26dd6fc467a%40googlegroups.com.

Re: [sqlalchemy] session.add with insert-or-update

['Michael Mulqueen' via sqlalchemy]

Hi Keith,

Small world!

You have at least 3 options depending on your requirements:

1. Handle it in your own application logic (e.g. make a get_or_create
method) - I tend to prefer this, business rules for create vs. update often
creeps in.
2. Use session.merge
https://docs.sqlalchemy.org/en/13/orm/session_state_management.html#merging
3. Upserts -
https://docs.sqlalchemy.org/en/13/dialects/mysql.html#insert-on-duplicate-key-update-upsert
(this also exists for PostgreSQL).

First option example:

# SQLAlchemy models
class Incident(Base):
incident_id = Column(Integer, primary_key=True)

@classmethod
def get_or_create(cls, session, id_=None):
if id_:
incident = session.query(cls).filter(cls.incident_id == id_).one()
else:
incident = Incident()
session.add(incident)
return incident

def populate(self, data):
for key, value in data.items():
assert hasattr(self, key)
setattr(self, key, value)



# Request handler.
def report_incident():
incident = Incident.get_or_create(session, record.get("incident_id"))
incident.populate(record)
session.commit()

session.add doesn't imply insert by the way (it's adding to the session,
not adding to the database), so calling it on an object that's been
retrieved in the same session won't cause problems.

Hope that helps.

Cheers,
Mike



On Sat, 7 Mar 2020, 18:34 Keith Edmunds,  wrote:

> I'm new to SQLAlchemy. Sorry if it shows.
>
> I'm using a MySQL backend. I've set up a declarative_base, defined a table
> class, set up a session. I defined a record as a dictionary and added it
> successfully to the db with:
>
> incident = Incidents(**record)
> session.add(incident)
> session.commit()
>
> The behaviour I'd like from the add/commit steps is to update any existing
> records that has a matching Primary Key, or insert as a new record if
> there's no match.
>
> I see posts on how to do that with core functionality, but how would I do
> that using the ORM as above?
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/9dfd3fb5-5516-4bea-8cd1-9abf3e6280fc%40googlegroups.com
> 
> .
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHxMHYUoo3bmtg%3DZjcQE%2B5u7dMcwXvy4jYCMknkawz84Gpcr9g%40mail.gmail.com.

[sqlalchemy] session.add with insert-or-update

[Keith Edmunds]

I'm new to SQLAlchemy. Sorry if it shows.

I'm using a MySQL backend. I've set up a declarative_base, defined a table 
class, set up a session. I defined a record as a dictionary and added it 
successfully to the db with:

incident = Incidents(**record)
session.add(incident)
session.commit()

The behaviour I'd like from the add/commit steps is to update any existing 
records that has a matching Primary Key, or insert as a new record if 
there's no match.

I see posts on how to do that with core functionality, but how would I do 
that using the ORM as above?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/9dfd3fb5-5516-4bea-8cd1-9abf3e6280fc%40googlegroups.com.

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.

[sqlalchemy] session.add order

[Victor Poluksht]

I've found that sometimes sqlalchemy inserts objects during 
session.commit() not in the order they have been added using session.add()

I've put my code example and the output to the github gist.

https://gist.github.com/vpol/8da4a512308ae351eaf6

My question is similar to this one: 
http://stackoverflow.com/questions/10154343/is-sqlalchemy-saves-order-in-adding-objects-to-session

Is it possible to make sqlalchemy guarantee the order of insert.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] session.add order

[Michael Bayer]

the order in which you put things into session.add() is significant only
within the scope of a certain mapped class, that is, the order that you put
a bunch of Foo() objects into session.add() will be maintained, however a
series of Bar() objects are handled separately.

Between different object hierarchies, the unit of work only takes into
account those dependencies established between hierarchies, which is most
commonly via relationship(). That is, if you have a series of Foo objects
which have one-to-many associations to a series of Bar objects, even if you
session.add() the Bar objects first, the unit of work can’t INSERT the Bar
objects first, as they are dependent on the Foo objects being present.

If there is no particular dependency between two classes, that is no
relationship(), then there’s no determinism to when the series of Foo or Bar
objects are inserted, they will be inserted in add() order only within the
scope of Foo and Bar. An exception to this occurs if there are
self-referential relationships involved, say if a Foo has a collection of
Foo objects related to it, or even a subclass, such as Foo-SubFoo(Foo); 
in that case, the order within Foo objects themselves needs to be based
on foreign key dependency first.

The aspect of dependencies between mappers is normally established by the
fact of a relatlonship() establishing a partial ordering between those two
mappers. However, there is semi-public API that may be used to add
additional dependencies between mappers at flush time. If you really can’t
add any kind of relationship() between the mappers in question, an approach
like the one below may be used, though this is only semi-public API. There
should ideally be some public way to add dependencies like this between
mappers.

from sqlalchemy import Column, create_engine, Integer
from sqlalchemy.orm import Session, unitofwork
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
from sqlalchemy import inspect

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)


class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)

e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)


@event.listens_for(Session, before_flush)
def set_b_dependent_on_a(session, flush_context, objects):
b_mapper = inspect(B)
a_mapper = inspect(A)
save_bs = unitofwork.SaveUpdateAll(flush_context, b_mapper)
save_as = unitofwork.SaveUpdateAll(flush_context, a_mapper)
flush_context.dependencies.add((save_as, save_bs))

s = Session(e)
s.add_all([A(), A(), B(), A(), B()])
s.commit()


Victor Poluksht vpoluk...@gmail.com wrote:

 I've found that sometimes sqlalchemy inserts objects during session.commit() 
 not in the order they have been added using session.add()
 
 I've put my code example and the output to the github gist.
 
 https://gist.github.com/vpol/8da4a512308ae351eaf6
 
 My question is similar to this one: 
 http://stackoverflow.com/questions/10154343/is-sqlalchemy-saves-order-in-adding-objects-to-session
 
 Is it possible to make sqlalchemy guarantee the order of insert.
 
 -- 
 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 http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Session.add doesn't work on Python 3.3.0?

[Tim Cooijmans]

I have a strange error using Python 3.3.0 and SQLAlchemy 0.8.0:

from sqlalchemy import create_engine, Integer, String, Text, Column, Sequence, 
DateTime, ForeignKeyfrom sqlalchemy.orm import sessionmaker, relationship, 
backreffrom sqlalchemy.ext.declarative import declarative_base

engine = create_engine(sqlite:///:memory:, echo=True)Session = 
sessionmaker(bind = engine)Base = declarative_base()
class Product(Base):
__tablename__ = products

id = Column(Integer, primary_key=True)
name = Column(String(200))
description = Column(Text)
Base.metadata.create_all(engine)

session = Session()
product = Product()
product.id = 1
product.name = Test
product.description = Test

session.add(product)print(product in session) # Expected true
session.commit()print(session.query(Product).all()) # Expected the previous item

As commented in code I expected the add method to add the product to the 
database using an INSERT. But it doesn't nor does it throw an exception. 
This is the log:

2013-04-16 18:03:14,368 INFO sqlalchemy.engine.base.Engine PRAGMA 
table_info(products)2013-04-16 18:03:14,369 INFO 
sqlalchemy.engine.base.Engine ()2013-04-16 18:03:14,371 INFO 
sqlalchemy.engine.base.Engine 
CREATE TABLE products (
id INTEGER NOT NULL, 
name VARCHAR(200), 
description TEXT, 
PRIMARY KEY (id))

2013-04-16 18:03:14,371 INFO sqlalchemy.engine.base.Engine ()2013-04-16 
18:03:14,371 INFO sqlalchemy.engine.base.Engine COMMITFalse2013-04-16 
18:04:04,706 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)2013-04-16 
18:04:04,708 INFO sqlalchemy.engine.base.Engine SELECT products.id AS 
products_id, products.name AS products_name, products.description AS 
products_description 
FROM products2013-04-16 18:04:04,709 INFO sqlalchemy.engine.base.Engine ()[]

I already tried several things:

   - Changing the engine doesn't work: I also tried postgresql.
   - I tried debugging. When I reach the body of the add method there is 
   nothing to step into.

Am I doing something wrong or is this a bug?
(Also posted this question on Stack Overflow: 
http://stackoverflow.com/questions/16041868/sqlalchemy-session-add-doesnt-work
)


-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Session.add doesn't work on Python 3.3.0?

[Mauricio de Abreu Antunes]

I think you need to commit your session data.

Em terça-feira, 16 de abril de 2013, Tim Cooijmans escreveu:

 I have a strange error using Python 3.3.0 and SQLAlchemy 0.8.0:

 from sqlalchemy import create_engine, Integer, String, Text, Column, 
 Sequence, DateTime, ForeignKeyfrom sqlalchemy.orm import sessionmaker, 
 relationship, backreffrom sqlalchemy.ext.declarative import declarative_base

 engine = create_engine(sqlite:///:memory:, echo=True)Session = 
 sessionmaker(bind = engine)Base = declarative_base()
 class Product(Base):
 __tablename__ = products

 id = Column(Integer, primary_key=True)
 name = Column(String(200))
 description = Column(Text)
 Base.metadata.create_all(engine)

 session = Session()
 product = Product()
 product.id = 1
 product.name = Test
 product.description = Test

 session.add(product)print(product in session) # Expected true
 session.commit()print(session.query(Product).all()) # Expected the previous 
 item

 As commented in code I expected the add method to add the product to the
 database using an INSERT. But it doesn't nor does it throw an exception.
 This is the log:

 2013-04-16 18:03:14,368 INFO sqlalchemy.engine.base.Engine PRAGMA 
 table_info(products)2013-04-16 18:03:14,369 INFO 
 sqlalchemy.engine.base.Engine ()2013-04-16 18:03:14,371 INFO 
 sqlalchemy.engine.base.Engine
 CREATE TABLE products (
 id INTEGER NOT NULL,
 name VARCHAR(200),
 description TEXT,
 PRIMARY KEY (id))

 2013-04-16 18:03:14,371 INFO sqlalchemy.engine.base.Engine ()2013-04-16 
 18:03:14,371 INFO sqlalchemy.engine.base.Engine COMMITFalse2013-04-16 18:04



-- 
*Mauricio de Abreu Antunes*
Mobile: (51)930-74-525
Skype: mauricio.abreua

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Session.add doesn't work on Python 3.3.0?

[Michael Bayer]

cant reproduce, running with Python 3.3.0 here against 0.8.0 I get the INSERT:

PRAGMA table_info(products)
2013-04-16 16:14:41,019 INFO sqlalchemy.engine.base.Engine ()
2013-04-16 16:14:41,019 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE products (
id INTEGER NOT NULL, 
name VARCHAR(200), 
description TEXT, 
PRIMARY KEY (id)
)


2013-04-16 16:14:41,020 INFO sqlalchemy.engine.base.Engine ()
2013-04-16 16:14:41,020 INFO sqlalchemy.engine.base.Engine COMMIT
True
2013-04-16 16:14:41,021 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-04-16 16:14:41,021 INFO sqlalchemy.engine.base.Engine INSERT INTO products 
(id, name, description) VALUES (?, ?, ?)
2013-04-16 16:14:41,021 INFO sqlalchemy.engine.base.Engine (1, 'Test', 'Test')
2013-04-16 16:14:41,022 INFO sqlalchemy.engine.base.Engine COMMIT
2013-04-16 16:14:41,022 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-04-16 16:14:41,022 INFO sqlalchemy.engine.base.Engine SELECT products.id 
AS products_id, products.name AS products_name, products.description AS 
products_description 
FROM products
2013-04-16 16:14:41,023 INFO sqlalchemy.engine.base.Engine ()
[__main__.Product object at 0x105c3dbd0]



On Apr 16, 2013, at 2:42 PM, Tim Cooijmans timcooijm...@gmail.com wrote:

 from sqlalchemy import create_engine, Integer, String, Text, Column, 
 Sequence, DateTime, ForeignKey
 from sqlalchemy.orm import sessionmaker, relationship, backref
 from sqlalchemy.ext.declarative import declarative_base
 
 engine = create_engine(sqlite:///:memory:, echo=True)
 Session = sessionmaker(bind = engine)
 Base = declarative_base()
 
 class Product(Base):
 __tablename__ = products
 
 id = Column(Integer, primary_key=True)
 name = Column(String(200))
 description = Column(Text)
 
 Base.metadata.create_all(engine)
 
 session = Session()
 product = Product()
 product.id = 1
 product.name = Test
 product.description = Test
 
 session.add(product)
 print(product in session) # Expected true
 session.commit()
 print(session.query(Product).all()) # Expected the previous item

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Session.add doesn't work on Python 3.3.0?

[Mauricio de Abreu Antunes]

I tried your same code here I got True.

Em terça-feira, 16 de abril de 2013, Michael Bayer escreveu:

 cant reproduce, running with Python 3.3.0 here against 0.8.0 I get the
 INSERT:

 PRAGMA table_info(products)
 2013-04-16 16:14:41,019 INFO sqlalchemy.engine.base.Engine ()
 2013-04-16 16:14:41,019 INFO sqlalchemy.engine.base.Engine
 CREATE TABLE products (
 id INTEGER NOT NULL,
 name VARCHAR(200),
 description TEXT,
 PRIMARY KEY (id)
 )


 2013-04-16 16:14:41,020 INFO sqlalchemy.engine.base.Engine ()
 2013-04-16 16:14:41,020 INFO sqlalchemy.engine.base.Engine COMMIT
 True
 2013-04-16 16:14:41,021 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
 2013-04-16 16:14:41,021 INFO sqlalchemy.engine.base.Engine INSERT INTO
 products (id, name, description) VALUES (?, ?, ?)
 2013-04-16 16:14:41,021 INFO sqlalchemy.engine.base.Engine (1, 'Test',
 'Test')
 2013-04-16 16:14:41,022 INFO sqlalchemy.engine.base.Engine COMMIT
 2013-04-16 16:14:41,022 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
 2013-04-16 16:14:41,022 INFO sqlalchemy.engine.base.Engine SELECT
 products.id AS products_id, products.name AS products_name,
 products.description AS products_description
 FROM products
 2013-04-16 16:14:41,023 INFO sqlalchemy.engine.base.Engine ()
 [__main__.Product object at 0x105c3dbd0]



 On Apr 16, 2013, at 2:42 PM, Tim Cooijmans timcooijm...@gmail.com wrote:

 from sqlalchemy import create_engine, Integer, String, Text, Column, 
 Sequence, DateTime, ForeignKeyfrom sqlalchemy.orm import sessionmaker, 
 relationship, backreffrom sqlalchemy.ext.declarative import declarative_base

 engine = create_engine(sqlite:///:memory:, echo=True)Session = 
 sessionmaker(bind = engine)Base = declarative_base()

  --
 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 javascript:_e({},
 'cvml', 'sqlalchemy%2bunsubscr...@googlegroups.com');.
 To post to this group, send email to 
 sqlalchemy@googlegroups.comjavascript:_e({}, 'cvml', 
 'sqlalchemy@googlegroups.com');
 .
 Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
 For more options, visit https://groups.google.com/groups/opt_out.





-- 
*Mauricio de Abreu Antunes*
Mobile: (51)930-74-525
Skype: mauricio.abreua

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Session.add performance

[Michael Bayer]

On Dec 16, 2010, at 12:39 AM, Julian Scheid wrote:

 In an application that is heavy on inserts and updates, cProfile
 output is dominated by Session.add in which about 45% of time is
 spent. Most of that time, in turn, is spent in cascade_iterator (43%).
 I can provide more detailed information if needed.
 
 The application does aggressive caching of data and has set
 expire_on_commit=False, in order to keep database load down. Is that
 the reason for Session.add slowness?
 
 Is there a way I can speed this up while keeping a similar level of
 cache aggressiveness?
 
 For example, in one test run Session.__contains__ was invoked 25m
 times over the course of only a few minutes, accounting for 27% of
 total time spent.  Could it be a good idea to try and override this
 function with one that's optimized for this specific use case?
 
 Also, so far I haven't spent any effort expunging objects from the
 session as soon as possible.  Some objects might linger for longer
 than necessary.  Would they contribute to Session.add's overhead?

A major part of development resources as of late have been focused on add() and 
cascade_iterator().   I would advise trying out the 0.7 tip from mercurial 
where we've cut out a lot of overhead out of many areas of the flush including 
add() + cascade_iterator (see 
http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-profiles/ for some 
profiling output).  

Things like inlining Session.__contains__ are good ideas if they are shown to 
be prominent in a slow profile, so if you want to send along a test script to 
me that illustrates your bottlenecks I can work on its pain points and add it 
to our suite.



 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Session.add performance

[Julian Scheid]

In an application that is heavy on inserts and updates, cProfile
output is dominated by Session.add in which about 45% of time is
spent. Most of that time, in turn, is spent in cascade_iterator (43%).
I can provide more detailed information if needed.

The application does aggressive caching of data and has set
expire_on_commit=False, in order to keep database load down. Is that
the reason for Session.add slowness?

Is there a way I can speed this up while keeping a similar level of
cache aggressiveness?

For example, in one test run Session.__contains__ was invoked 25m
times over the course of only a few minutes, accounting for 27% of
total time spent.  Could it be a good idea to try and override this
function with one that's optimized for this specific use case?

Also, so far I haven't spent any effort expunging objects from the
session as soon as possible.  Some objects might linger for longer
than necessary.  Would they contribute to Session.add's overhead?

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] session.add() vs session.merge() and delete child

[avdd]

I'm using session.add() to refresh my objects while working on them,
because I don't want to merge them with the persistent state.  But it
appears deletes aren't carrying across to child relations:


$ cat listdelete.py; python listdelete.py

import sqlalchemy as sql
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base

echo = 0
engine = sql.create_engine(sqlite:///:memory:, echo=bool(echo))
metadata = sql.MetaData(bind=engine)
DB = orm.sessionmaker(bind=engine)
T = declarative_base(metadata=metadata)

class A(T):
__tablename__ = 'a'
id = sql.Column(sql.Integer, primary_key=True)
info = sql.Column(sql.String)
cc = orm.relation('C',
  backref='a',
  cascade='all,delete-orphan')
def __repr__(self):
return   a: %s cc=%s % (self.info, len(self.cc))

class C(T):
__tablename__ = 'c'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)

def get():
return DB().query(A).first()

def change(a, s, i):
orm.object_session(a).close()
db = DB()
db.add(a)
a.info = s
del a.cc[-1]
a.cc.append(C(i=i))
db.close()

metadata.create_all()
A.__table__.delete().execute()

db = DB()
a = A(id=1, info='blah', cc=[C(i=1), C(i=2)])
db.add(a)
db.commit()
db.close()

print get()

# merge and flush
a = get()
change(a, 'change one', 3)
db = DB()
db.merge(a)
db.commit()
db.close()

print get()

# add and flush
a = get()
change(a, 'change two', 4)
db = DB()
db.add(a)
db.commit()
db.close()

print get()


  a: blah cc=2
  a: change one cc=2
  a: change two cc=3

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] session.add() vs session.merge() and delete child

[Michael Bayer]

On Jan 30, 2010, at 9:07 AM, avdd wrote:

 I'm using session.add() to refresh my objects while working on them,
 because I don't want to merge them with the persistent state.  But it
 appears deletes aren't carrying across to child relations:

this example is too compliated for me to understand without great effort, 
perhaps someone else has the time to follow it more closely - it appears to be 
creating and closing many new sessions and add()ing objects between them - an 
unusual series of events.   The policy of add() is that it puts an object in 
the session.  If its already there, nothing happens.   It doesnt invalidate any 
state or reconcile with what's currently visible in the transaction, so if the 
example is attempting to illustrate, transaction A changed a row, but 
transaction B doesn't see it!, you'd have to expire the appropriate parts of 
session B for those changes to be seen.


 
 
 $ cat listdelete.py; python listdelete.py
 
 import sqlalchemy as sql
 from sqlalchemy import orm
 from sqlalchemy.ext.declarative import declarative_base
 
 echo = 0
 engine = sql.create_engine(sqlite:///:memory:, echo=bool(echo))
 metadata = sql.MetaData(bind=engine)
 DB = orm.sessionmaker(bind=engine)
 T = declarative_base(metadata=metadata)
 
 class A(T):
__tablename__ = 'a'
id = sql.Column(sql.Integer, primary_key=True)
info = sql.Column(sql.String)
cc = orm.relation('C',
  backref='a',
  cascade='all,delete-orphan')
def __repr__(self):
return   a: %s cc=%s % (self.info, len(self.cc))
 
 class C(T):
__tablename__ = 'c'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
 primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)
 
 def get():
return DB().query(A).first()
 
 def change(a, s, i):
orm.object_session(a).close()
db = DB()
db.add(a)
a.info = s
del a.cc[-1]
a.cc.append(C(i=i))
db.close()
 
 metadata.create_all()
 A.__table__.delete().execute()
 
 db = DB()
 a = A(id=1, info='blah', cc=[C(i=1), C(i=2)])
 db.add(a)
 db.commit()
 db.close()
 
 print get()
 
 # merge and flush
 a = get()
 change(a, 'change one', 3)
 db = DB()
 db.merge(a)
 db.commit()
 db.close()
 
 print get()
 
 # add and flush
 a = get()
 change(a, 'change two', 4)
 db = DB()
 db.add(a)
 db.commit()
 db.close()
 
 print get()
 
 
 a: blah cc=2
 a: change one cc=2
 a: change two cc=3
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] session.add() vs session.merge() and delete child

[Michael Bayer]

On Jan 30, 2010, at 9:07 AM, avdd wrote:

 I'm using session.add() to refresh my objects while working on them,
 because I don't want to merge them with the persistent state.  But it
 appears deletes aren't carrying across to child relations:

in r6711 the save-update cascade operation will cascade related objects present 
in the full history of a scalar- or collection- holding relation() attribute 
into the new session, thus allowing objects marked as deleted or disassociated 
from the parent to be present in the new session where they will particpate in 
the next flush().





 
 
 $ cat listdelete.py; python listdelete.py
 
 import sqlalchemy as sql
 from sqlalchemy import orm
 from sqlalchemy.ext.declarative import declarative_base
 
 echo = 0
 engine = sql.create_engine(sqlite:///:memory:, echo=bool(echo))
 metadata = sql.MetaData(bind=engine)
 DB = orm.sessionmaker(bind=engine)
 T = declarative_base(metadata=metadata)
 
 class A(T):
__tablename__ = 'a'
id = sql.Column(sql.Integer, primary_key=True)
info = sql.Column(sql.String)
cc = orm.relation('C',
  backref='a',
  cascade='all,delete-orphan')
def __repr__(self):
return   a: %s cc=%s % (self.info, len(self.cc))
 
 class C(T):
__tablename__ = 'c'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
 primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)
 
 def get():
return DB().query(A).first()
 
 def change(a, s, i):
orm.object_session(a).close()
db = DB()
db.add(a)
a.info = s
del a.cc[-1]
a.cc.append(C(i=i))
db.close()
 
 metadata.create_all()
 A.__table__.delete().execute()
 
 db = DB()
 a = A(id=1, info='blah', cc=[C(i=1), C(i=2)])
 db.add(a)
 db.commit()
 db.close()
 
 print get()
 
 # merge and flush
 a = get()
 change(a, 'change one', 3)
 db = DB()
 db.merge(a)
 db.commit()
 db.close()
 
 print get()
 
 # add and flush
 a = get()
 change(a, 'change two', 4)
 db = DB()
 db.add(a)
 db.commit()
 db.close()
 
 print get()
 
 
 a: blah cc=2
 a: change one cc=2
 a: change two cc=3
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] session.add() or session.update() ? (NEWBIE)

[Marcin Krol]

Hello everyone,

I have tested that session.add(changed_sqla_object) in at least one 
context (when the object with id of changed_sqla_object already exists 
in the db) does issue UPDATE sql query and updates the object in the 
database.

However, there's also session.update() method. Help on this says:

 update(self, instance) method of sqlalchemy.orm.session.Session instance
 Bring a detached (saved) instance into this ``Session``.

Meaning this updates session with the saved object data, and it's not 
that the *changed* object's data that is updated in database?

 
 Use session.add()

To do what?

 
 If there is a persistent instance with the same instance key, but
 different identity already associated with this ``Session``, an
 InvalidRequestError exception is thrown.
 
 This operation cascades the `save_or_update` method to associated
 instances if the relation is mapped with ``cascade=save-update``.

In general, the question: is it safe to use session.add(changed_object) 
in *all of the circumstances*?

Regards,
mk

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] session.add

[n00b]

hello,

i need to generate a unique number from a table based on the primary
key. i used to lock
the table (with write) and got what i needed. working with the ORM,
though, it seems that
session.add(object) functions just as well. at the time of the
session.add(), the primary key assigned to the object, which is still
in the session, is unique. calling the 'live' object.id get's me what
i need - a unique primary key. somehow this seems too good to be
true ... what am i missing here?

thx
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---