Re: [sqlalchemy] SQLAlchemy 1.1 cause core dump committing multiple dirty records outside of function where mutation happens

[Konstantin Kashin]

Alright... I was able to track this down to a cPython problem in Python 
3.6.0. This reproes with SQLite as well. When moving to Python 3.6.2, this 
goes away. This fixed the culprit: 
https://github.com/serhiy-storchaka/cpython/commit/5aafff22a20715d276a23d30b7335bbecbc31754

On Tuesday, August 22, 2017 at 2:02:25 AM UTC-7, Simon King wrote:
>
> On Tue, Aug 22, 2017 at 9:26 AM, Konstantin Kashin  > wrote: 
> > # Setup 
> > Suppose I have a table with two fields: a string primary key and a 
> boolean 
> > flag. I want to query multiple rows and then update the flag across all 
> of 
> > them, then commit my changes. I have a MySQL DB with the following DBAPI 
> > (
> http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html).
>  
>
> > 
> > ## Model 
> > class TestSQLTable(Base): 
> > __tablename__ = 'test_table' 
> > 
> > 
> > some_string_id = Column(String(32), nullable=False, 
> primary_key=True) 
> > flag = Column(Boolean, nullable=False, default=True) 
> > 
> > 
> > # SQLAlchemy 1.0.12 
> > 
> > In SQLAlchemy 1.0.12, the following worked: 
> > 
> > 
> > sm = sessionmaker( 
> > autocommit=False, 
> > autoflush=True, 
> > expire_on_commit=True, 
> > bind=engine,  # MySQL engine 
> > ) 
> > session_factory = scoped_session(sm) 
> > 
> > 
> > def modify_records(session, flag=False): 
> > records = session.query(TestSQLTable).all() 
> > for r in records: 
> > r.flag = flag 
> > 
> > 
> > session = session_factory() 
> > modify_records(session) 
> > session.commit() 
> > 
> > 
> > 
> > # SQLAlchemy 1.1.13 
> > Now, this does *not* work and causes a core dump. I am unable to tell 
> what 
> > change was made in 1.1 that causes this different behavior when reading 
> the 
> > ["What's New in SQLAlchemy 
> > 1.1?"](http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html) 
>
> > guide. 
> > 
> > If I do this in a Jupyter notebook, for instance, the kernel just 
> crashes on 
> > commit without any error. 
> > 
> > However, this **does** work for just a single row: 
> > 
> > def modify_record(session, flag=False): 
> > record = session.query(TestSQLTable).first() 
> > record.flag = flag 
> > 
> > 
> > ## Ways I have gotten this to work 
> > 
> > ### Add an explicit return 
> > 
> > def modify_records(session, flag=False): 
> > records = session.query(TestSQLTable).all() 
> > for r in records: 
> > r.flag = flag 
> > return records  # ADD THIS! 
> > 
> > 
> > ### Flush after each mutation 
> > 
> > 
> > def modify_records(session, flag=False): 
> > records = session.query(TestSQLTable).all() 
> > for r in records: 
> > r.flag = flag 
> > session.flush()  # ADD THIS! 
> > 
> > 
> > ### Restructure code so that the commit happens inside `modify_records` 
> > 
> > This works, but is not the way the application is currently built and 
> would 
> > thus require a major refactor (b/c commit happens via a decorator). 
> > 
> > # Main question 
> > 
> > Why does this happen exactly for multiple records and why did this work 
> in 
> > 1.0 but does not in 1.1? 
>
> What platform are you running this on, and how have you installed 
> MySQLdb and SQLAlchemy? If you ugraded SQLAlchemy in-place, is there 
> any chance that you are running a mix of old and new code? Have you 
> reproduced the error in a completely fresh installation? 
>
> Simon 
>

-- 
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.

Re: [sqlalchemy] Set up a composite key using a foreign key and another column with Python

[Mike Bayer]

On Tue, Aug 22, 2017 at 9:42 PM, Joshua Peppeman
 wrote:
> Hello,
>
> I asked this question on Reddit and StackOverflow without any luck getting
> an answer. Here is the StackOverflow question. I'll paste it below again.
>
>
> I have two tables set up in Python with sqlalchemy using mySQL. They look
> something like this:
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> class Test(Base):
> __tablename__ = 'test'
> id = Column(Integer, primary_key=True)
> questions = relationship('Question', cascade='all,delete',
> backref='test', uselist=True)
>
> class Question(Base):
> __tablename__ = 'question'
> testID = Column(Integer, ForeignKey('test.id', ondelete='CASCADE'))
> sequence = Column(Integer, primary_key=True)
> text = Column(String(500))
>
>
>
> Right now when I run my code and get the tables set up, they look like this:
>
> testID|sequence|text
>
> 1 | 1 | text
>
> 1 | 2 | text
>
> 1 | 3 | text
>
> 1 | 4 | text
>
> 2 | 5 | text
>
> 2 | 6 | text
>
> 2 | 7 | text
>
> But I want them to look like this:
>
> testID|sequence|text
>
> 1 | 1 | text
>
> 1 | 2 | text
>
> 1 | 3 | text
>
> 1 | 4 | text
>
> 2 | 1 | text
>
> 2 | 2 | text
>
> 2 | 3 | text
>
> I know that sequence is auto-incrementing because it is the first int
> primary key, but I only want it to auto-increment until there's a new
> testID.
>
> I could leave it and just sort them later, but I'd really like to set it up
> where sequence resets with every new testID. I'm pretty new to sqlAlchemy so
> I may be missing something obvious. Any help is appreciated. Thanks.

MySQL's autoincrement feature is strictly a single integer that
increases steadily.  If you want an integer that is cycling, you'd
need to maintain that number yourself on the Python side.
Additionally, you'd want to set up a composite primary key on
Question.   The orderinglist extension is a quick way to get the
counting-per-collection effect you are looking for (works on the
Python side though and it can get tripped up if you push it too far):

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.orderinglist import ordering_list
Base = declarative_base()


class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
questions = relationship(
'Question', cascade='all,delete', backref='test', uselist=True,
collection_class=ordering_list("sequence", count_from=1))


class Question(Base):
__tablename__ = 'question'
testID = Column(Integer, ForeignKey('test.id', ondelete='CASCADE'),
primary_key=True)
sequence = Column(Integer, primary_key=True)
text = Column(String(500))

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


s.add_all([
Test(
questions=[
Question(text="t1q1"),
Question(text="t1q2"),
Question(text="t1q3"),
Question(text="t1q4"),
]
),
Test(
questions=[
Question(text="t2q1"),
Question(text="t2q2"),
Question(text="t2q3"),
Question(text="t2q4"),
]
)
])

s.commit()


for row in s.query(Question.testID, Question.sequence, Question.text).\
order_by(Question.testID, Question.sequence):
print row





>
> --
> 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.

Re: [sqlalchemy] Re: getting model's grandparent after persistent_to_deleted event has fired.

[Mike Bayer]

On Tue, Aug 22, 2017 at 6:46 PM, cecemel  wrote:
> Hello,
>
> thanks again for the answer. Perhaps my case was not clear enough. To make
> it a bit more explicit, I updated the example accordingly.
>
> from sqlalchemy import event
>
> from sqlalchemy import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker, backref, relationship
>
> Base = declarative_base()
>
>
> #
> # MODEL
> #
> class House(Base):
> __tablename__ = 'house'
> id = Column(Integer, primary_key=True)
> rooms = relationship("Room",
>  backref=backref("house", lazy="joined"),
>  cascade='all, delete-orphan')
>
>
> class Room(Base):
> __tablename__ = 'room'
> id = Column(Integer, primary_key=True)
> house_id = Column(Integer, ForeignKey('house.id'))
> beds = relationship("Bed",
> backref=backref("room", lazy="joined"),
> cascade='all, delete-orphan')
>
>
> class Bed(Base):
> __tablename__ = 'bed'
> id = Column(Integer, primary_key=True)
> room_id = Column(Integer, ForeignKey('room.id'))
>
>
> #
> # CONFIG
> #
> def setup():
> engine = create_engine("sqlite://", echo=True)
>
> Base.metadata.bind = engine
> Base.metadata.create_all(engine)
>
> SessionFactory = sessionmaker(
> bind=engine
> )
>
> event.listen(SessionFactory, 'deleted_to_detached',
> listener_bed_has_been_removed)
>
> return SessionFactory
>
>
> def listener_bed_has_been_removed(session, instance):
> if type(instance) is not Bed:
> return
>
> # so, in this example, this function should be called 3 times.
> # The first time it is called, I get no problems, I can access
> instance.room.house_id the call proceeds
> # The second bed is a problem, I get the error
> # "sqlalchemy.orm.exc.DetachedInstanceError: Parent instance  0x7f24fe14bb70> is not bound to a Session; lazy load operation of attribute
> 'room' cannot proceed"
>
> # SO, my question is: is there ANY way to keep these references to
> parents in this function?
>
> bed_id = instance.id
> house_id = instance.room.house_id
>
>
> print("execute a service call to external service here bed_id {},
> house_id {}".format(bed_id, house_id))
>
>
> if __name__ == "__main__":
> session_factory = setup()
> session = session_factory()
>
> session.add(House(id=1))
> session.add(Room(id=1, house_id=1))
> session.add(Bed(id=1, room_id=1))
> session.add(Bed(id=2, room_id=1))
> session.add(Bed(id=3, room_id=1))
> session.commit()
>
> room = session.query(Room).get(1)
> session.delete(room)
> session.commit()
> session.close()
>
>
> So, for this example, I am looking for a solution to keep the references to
> the 'house' from the 'bed' model after flush (any solution would be good).
> Is there perhaps a way to dynamically set a property in 'bed' -model (e.g
> house_id), once 'room'-backref has been loaded ?

so the way you have this set up, you have not only room->bed but you
also have delete,delete-orphan cascade.  this indicates your intent
that a Bed object should be deleted when it is both not associated
with any Room, as well as when its parent Room is deleted.   So the
behavior you are getting is what you specified.

if you *dont* want Bed to be deleted when Room is deleted, you'd want
to remove that delete-orphan casacde.  If you then want Bed to be
directly associated with House in the absense of Room, then yes you'd
add another column house_id to Bed with a corresponding
relationship(), and you'd need to make sure that is assigned as you
want as well when the objects go into the database.





>
>
> On Tuesday, August 22, 2017 at 5:36:39 PM UTC+2, Mike Bayer wrote:
>>
>> you would need to illustrate an MCVE of what's happening.  objects
>> don't "lose track" of their related objects unless yes, you deleted
>> them, in which case you should not expect that they would be there.
>> when the object is expired, it will go to reload them, and they'll be
>> gone.
>>
>> On Tue, Aug 22, 2017 at 11:05 AM, cecemel  wrote:
>> > @update:
>> >
>> > calling the flush doen't seem to make any difference. At some point, the
>> > object looses track of it's grandparents
>> >
>> >
>> > On Tuesday, August 22, 2017 at 3:57:23 PM UTC+2, cecemel wrote:
>> >>
>> >> Hi,
>> >>
>> >> so, I'm currently facing this issue, where I would like to do some
>> >> calls
>> >> to an external service, when my object has been deleted within a flush.
>> >> For this 

[sqlalchemy] Set up a composite key using a foreign key and another column with Python

[Joshua Peppeman]

Hello,

I asked this question on Reddit and StackOverflow without any luck getting 
an answer. Here is the StackOverflow question 
.
 
I'll paste it below again.


I have two tables set up in Python with sqlalchemy using mySQL. They look 
something like this:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
questions = relationship('Question', cascade='all,delete', backref=
'test', uselist=True)

class Question(Base):
__tablename__ = 'question'
testID = Column(Integer, ForeignKey('test.id', ondelete='CASCADE'))
sequence = Column(Integer, primary_key=True)
text = Column(String(500))



Right now when I run my code and get the tables set up, they look like this:

testID|sequence|text

1 | 1 | text

1 | 2 | text

1 | 3 | text

1 | 4 | text

2 | 5 | text

2 | 6 | text

2 | 7 | text

But I want them to look like this:

testID|sequence|text

1 | 1 | text

1 | 2 | text

1 | 3 | text

1 | 4 | text

2 | 1 | text

2 | 2 | text

2 | 3 | text

I know that sequence is auto-incrementing because it is the first int 
primary key, but I only want it to auto-increment until there's a new 
testID.

I *could* leave it and just sort them later, but I'd really like to set it 
up where sequence resets with every new testID. I'm pretty new to 
sqlAlchemy so I may be missing something obvious. Any help is appreciated. 
Thanks.

-- 
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.

Re: [sqlalchemy] Re: getting model's grandparent after persistent_to_deleted event has fired.

[cecemel]

Hello, 

thanks again for the answer. Perhaps my case was not clear enough. To make 
it a bit more explicit, I updated the example accordingly. 

from sqlalchemy import event

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, backref, relationship

Base = declarative_base()


#
# MODEL
#
class House(Base):
__tablename__ = 'house'
id = Column(Integer, primary_key=True)
rooms = relationship("Room",
 backref=backref("house", lazy="joined"),
 cascade='all, delete-orphan')


class Room(Base):
__tablename__ = 'room'
id = Column(Integer, primary_key=True)
house_id = Column(Integer, ForeignKey('house.id'))
beds = relationship("Bed",
backref=backref("room", lazy="joined"),
cascade='all, delete-orphan')


class Bed(Base):
__tablename__ = 'bed'
id = Column(Integer, primary_key=True)
room_id = Column(Integer, ForeignKey('room.id'))


#
# CONFIG
#
def setup():
engine = create_engine("sqlite://", echo=True)

Base.metadata.bind = engine
Base.metadata.create_all(engine)

SessionFactory = sessionmaker(
bind=engine
)

event.listen(SessionFactory, 'deleted_to_detached', 
listener_bed_has_been_removed)

return SessionFactory


def listener_bed_has_been_removed(session, instance):
if type(instance) is not Bed:
return

# so, in this example, this function should be called 3 times.
# The first time it is called, I get no problems, I can access 
instance.room.house_id the call proceeds
# The second bed is a problem, I get the error
# "sqlalchemy.orm.exc.DetachedInstanceError: Parent instance  is not bound to a Session; lazy load operation of attribute 
'room' cannot proceed"

# SO, my question is: is there ANY way to keep these references to parents 
in this function?

bed_id = instance.id
house_id = instance.room.house_id


print("execute a service call to external service here bed_id {}, house_id 
{}".format(bed_id, house_id))


if __name__ == "__main__":
session_factory = setup()
session = session_factory()

session.add(House(id=1))
session.add(Room(id=1, house_id=1))
session.add(Bed(id=1, room_id=1))
session.add(Bed(id=2, room_id=1))
session.add(Bed(id=3, room_id=1))
session.commit()

room = session.query(Room).get(1)
session.delete(room)
session.commit()
session.close()


So, for this example, I am looking for a solution to keep the references to 
the 'house' from the 'bed' model after flush (any solution would be good). 
Is there perhaps a way to dynamically set a property in 'bed' -model (e.g 
house_id), once 'room'-backref has been loaded ?


On Tuesday, August 22, 2017 at 5:36:39 PM UTC+2, Mike Bayer wrote:
>
> you would need to illustrate an MCVE of what's happening.  objects 
> don't "lose track" of their related objects unless yes, you deleted 
> them, in which case you should not expect that they would be there. 
> when the object is expired, it will go to reload them, and they'll be 
> gone. 
>
> On Tue, Aug 22, 2017 at 11:05 AM, cecemel  > wrote: 
> > @update: 
> > 
> > calling the flush doen't seem to make any difference. At some point, the 
> > object looses track of it's grandparents 
> > 
> > 
> > On Tuesday, August 22, 2017 at 3:57:23 PM UTC+2, cecemel wrote: 
> >> 
> >> Hi, 
> >> 
> >> so, I'm currently facing this issue, where I would like to do some 
> calls 
> >> to an external service, when my object has been deleted within a flush. 
> >> For this operation to occur, I need the id from my model object, but 
> also 
> >> the id from the parent of the parent object model. There are cases, 
> where I 
> >> am unable to access them (I guess, depending of the order of the 
> execution) 
> >> and I am unsure on what to do next. 
> >> 
> >> So if you're willing to give me some advice, would be awesome. 
> >> 
> >> Here is a dummy model: 
> >> 
> >> from sqlalchemy import event 
> >> 
> >> from sqlalchemy import * 
> >> from sqlalchemy.ext.declarative import declarative_base 
> >> from sqlalchemy.orm import sessionmaker, backref, relationship 
> >> 
> >> Base = declarative_base() 
> >> 
> >> 
> >> 
> >> 
> #
>  
>
> >> # MODEL 
> >> 
> >> 
> #
>  
>
> >> class House(Base): 
> >> __tablename__ = 'house' 
> >> id = Column(Integer, 

Re: [sqlalchemy] making a relationship on a column operation

[Jonathan Vanasco]

and thank you x 100

-- 
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.

Re: [sqlalchemy] making a relationship on a column operation

[Jonathan Vanasco]

On Tuesday, August 22, 2017 at 2:16:42 PM UTC-4, Mike Bayer wrote:
 

> you're looking for: 
>
>
> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#using-custom-operators-in-join-conditions
>  
>
>
I swear I searched first and spent an hour trying to figure this out.  And 
then there was literally the exact documentation I needed.

-- 
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.

Re: [sqlalchemy] making a relationship on a column operation

[Mike Bayer]

On Tue, Aug 22, 2017 at 12:19 PM, Jonathan Vanasco
 wrote:
> I can't seem to figure out how to transition a query for the Ip
> Blocks/ranges than a given IP address exists in, from a generic query into a
> relationship.  i keep getting foreign key errors, not matter what arguments
> I try.  The simplest form of what I'm trying to do is below:

you're looking for:

http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#using-custom-operators-in-join-conditions

>
>
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - -
> # Standard imports
>
> import sqlalchemy
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy import Integer, Column, ForeignKey, Unicode
> from sqlalchemy import create_engine
> import sqlalchemy.dialects.postgresql
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - -
> # You probably don't need to overwrite this
> Base = declarative_base()
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - -
> # Define some models that inherit from Base
>
> class IpAddress(Base):
> __tablename__ = 'ip_address'
> id = Column(Integer, primary_key=True)
> ip_address = Column(Unicode(16), nullable=False)
> ip_address__cidr = Column(sqlalchemy.dialects.postgresql.CIDR,
> nullable=False)
>
> class IpRange(Base):
> __tablename__ = 'ip_range'
> id = Column(Integer, primary_key=True)
> ip_range__cidr = Column(sqlalchemy.dialects.postgresql.CIDR,
> nullable=False)
>
> IpAddress.in_ip_ranges = relationship(
> IpRange,
>
> primaryjoin=IpAddress.ip_address__cidr.op('<<=')(IpRange.ip_range__cidr),
> viewonly=True,
> )
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - -
> # set the engine
>
> engine =
> create_engine('postgresql://sa_test:sa_test@localhost/sqlalchemy_test',
> echo=True)
> Base.metadata.create_all(engine)
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - -
> # do a simple query to trigger the mapper error
>
> sessionFactory = sessionmaker(bind=engine)
> s = sessionFactory()
>
> s.query(IpAddress).get(1)
>
>
>
>
> --
> 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.

Re: [sqlalchemy] Re: getting model's grandparent after persistent_to_deleted event has fired.

[Mike Bayer]

you would need to illustrate an MCVE of what's happening.  objects
don't "lose track" of their related objects unless yes, you deleted
them, in which case you should not expect that they would be there.
when the object is expired, it will go to reload them, and they'll be
gone.

On Tue, Aug 22, 2017 at 11:05 AM, cecemel  wrote:
> @update:
>
> calling the flush doen't seem to make any difference. At some point, the
> object looses track of it's grandparents
>
>
> On Tuesday, August 22, 2017 at 3:57:23 PM UTC+2, cecemel wrote:
>>
>> Hi,
>>
>> so, I'm currently facing this issue, where I would like to do some calls
>> to an external service, when my object has been deleted within a flush.
>> For this operation to occur, I need the id from my model object, but also
>> the id from the parent of the parent object model. There are cases, where I
>> am unable to access them (I guess, depending of the order of the execution)
>> and I am unsure on what to do next.
>>
>> So if you're willing to give me some advice, would be awesome.
>>
>> Here is a dummy model:
>>
>> from sqlalchemy import event
>>
>> from sqlalchemy import *
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.orm import sessionmaker, backref, relationship
>>
>> Base = declarative_base()
>>
>>
>>
>> #
>> # MODEL
>>
>> #
>> class House(Base):
>> __tablename__ = 'house'
>> id = Column(Integer, primary_key=True)
>> rooms = relationship("Room",
>>  backref=backref("house", lazy="joined"),
>>  cascade='all, delete-orphan')
>>
>>
>> class Room(Base):
>> __tablename__ = 'room'
>> id = Column(Integer, primary_key=True)
>> house_id = Column(Integer, ForeignKey('house.id'))
>> beds = relationship("Bed",
>> backref=backref("room", lazy="joined"),
>> cascade='all, delete-orphan')
>>
>>
>> class Bed(Base):
>> __tablename__ = 'bed'
>> id = Column(Integer, primary_key=True)
>> room_id = Column(Integer, ForeignKey('room.id'))
>>
>>
>>
>> #
>> # CONFIG
>>
>> #
>> def setup():
>> engine = create_engine("sqlite:///foo.db", echo=True)
>>
>> Base.metadata.bind = engine
>> Base.metadata.create_all(engine)
>>
>> SessionFactory = sessionmaker(
>> bind=engine
>> )
>>
>> event.listen(SessionFactory, 'deleted_to_detached',
>> listener_bed_has_been_removed)
>>
>> return SessionFactory
>>
>>
>> def listener_bed_has_been_removed(session, instance):
>> if type(instance) is not Bed:
>> return
>>
>> bed_id = instance.id
>> house_id = instance.room.house.id  # this is NOT ALWAYS there.
>> Depending on the order of the execution I guess
>>
>> print("execute the service call to external service here bed_id {},
>> house_id {}".format(bed_id, house_id))
>>
>>
>>
>>
>> So my question(s):
>>
>> Is there a clean way to always acces this parent's parent attribute?
>>
>> If not, would be starting a new session and query it from the event
>> handler be an option? (is it not dangerous, because it seems to work)
>>
>> Additional quirk, I am working within a transaction manager (pyramid_tm)
>> and ZopeTransactionExtension()
>>
>> Thanks!
>>
>>
>> More information about the system:
>>
>> SQLAlchemy 1.1.13
>>
>> Python 3.5
>>
>> Postgres 9.6
>
> --
> 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 

[sqlalchemy] Re: getting model's grandparent after persistent_to_deleted event has fired.

[cecemel]

@update: 

calling the flush doen't seem to make any difference. At some point, the 
object looses track of it's grandparents

On Tuesday, August 22, 2017 at 3:57:23 PM UTC+2, cecemel wrote:
>
> Hi, 
>
> so, I'm currently facing this issue, where I would like to do some calls 
> to an external service, when my object has been deleted within a flush. 
> For this operation to occur, I need the id from my model object, but also 
> the id from the parent of the parent object model. There are cases, where I 
> am unable to access them (I guess, depending of the order of the execution) 
> and I am unsure on what to do next.
>
> So if you're willing to give me some advice, would be awesome. 
>
> Here is a dummy model:
>
> from sqlalchemy import event
>
> from sqlalchemy import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker, backref, relationship
>
> Base = declarative_base()
>
>
> #
> # MODEL
> #
> class House(Base):
> __tablename__ = 'house'
> id = Column(Integer, primary_key=True)
> rooms = relationship("Room",
>  backref=backref("house", lazy="joined"),
>  cascade='all, delete-orphan')
>
>
> class Room(Base):
> __tablename__ = 'room'
> id = Column(Integer, primary_key=True)
> house_id = Column(Integer, ForeignKey('house.id'))
> beds = relationship("Bed",
> backref=backref("room", lazy="joined"),
> cascade='all, delete-orphan')
>
>
> class Bed(Base):
> __tablename__ = 'bed'
> id = Column(Integer, primary_key=True)
> room_id = Column(Integer, ForeignKey('room.id'))
>
>
> #
> # CONFIG
> #
> def setup():
> engine = create_engine("sqlite:///foo.db", echo=True)
>
> Base.metadata.bind = engine
> Base.metadata.create_all(engine)
>
> SessionFactory = sessionmaker(
> bind=engine
> )
>
> event.listen(SessionFactory, 'deleted_to_detached', 
> listener_bed_has_been_removed)
>
> return SessionFactory
>
>
> def listener_bed_has_been_removed(session, instance):
> if type(instance) is not Bed:
> return
>
> bed_id = instance.id
> house_id = instance.room.house.id  # this is NOT ALWAYS there. Depending 
> on the order of the execution I guess
>
> print("execute the service call to external service here bed_id {}, 
> house_id {}".format(bed_id, house_id))
> 
>
>  
>
> So my question(s):
>
>- Is there a clean way to always acces this parent's parent attribute?
>   - If not, would be starting a new session and query it from the 
>   event handler be an option? (is it not dangerous, because it seems to 
> work)
>  - Additional quirk, I am working within a transaction manager 
>  (pyramid_tm) and ZopeTransactionExtension()
>   
> Thanks!
>
>
> More information about the system:
>
> SQLAlchemy 1.1.13
>
> Python 3.5
>
> Postgres 9.6
>

-- 
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.

Re: [sqlalchemy] getting model's grandparent after persistent_to_deleted event has fired.

[cecemel]

Hi, 

thanks for your reply.

 I am confused, it is during a delete that I should access the grandparent 
id of my object. (would if have been a add, I wouldn't be confused)

 If I do a flush, I actually would expect the ID's to be removed and the 
object to be completely 'unbound'. (So no linked Ids to Room and House, 
since the row does not exist anymore)

Could you elaborate a bit, or point to some documentation?

thanks!

On Tuesday, August 22, 2017 at 4:33:08 PM UTC+2, Mike Bayer wrote:
>
> On Tue, Aug 22, 2017 at 9:57 AM, cecemel  > wrote: 
> > Hi, 
> > 
> > so, I'm currently facing this issue, where I would like to do some calls 
> to 
> > an external service, when my object has been deleted within a flush. 
> > For this operation to occur, I need the id from my model object, but 
> also 
> > the id from the parent of the parent object model. There are cases, 
> where I 
> > am unable to access them (I guess, depending of the order of the 
> execution) 
> > and I am unsure on what to do next. 
>
> if you need auto-generated primary key values to be present, use 
> session.flush() to ensure it's happened. 
>
> > So my question(s): 
> > 
> > Is there a clean way to always acces this parent's parent attribute? 
> > 
> > If not, would be starting a new session and query it from the event 
> handler 
> > be an option? (is it not dangerous, because it seems to work) 
> > 
> > Additional quirk, I am working within a transaction manager (pyramid_tm) 
> and 
> > ZopeTransactionExtension() 
>
> assuming things are flushed you should be able to access the object 
> graph and the various .id attributes fully.   session.flush() will get 
> you there. 
>
> > 
> > Thanks! 
> > 
> > 
> > More information about the system: 
> > 
> > SQLAlchemy 1.1.13 
> > 
> > Python 3.5 
> > 
> > Postgres 9.6 
> > 
> > -- 
> > 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.

Re: [sqlalchemy] getting model's grandparent after persistent_to_deleted event has fired.

[Mike Bayer]

On Tue, Aug 22, 2017 at 9:57 AM, cecemel  wrote:
> Hi,
>
> so, I'm currently facing this issue, where I would like to do some calls to
> an external service, when my object has been deleted within a flush.
> For this operation to occur, I need the id from my model object, but also
> the id from the parent of the parent object model. There are cases, where I
> am unable to access them (I guess, depending of the order of the execution)
> and I am unsure on what to do next.

if you need auto-generated primary key values to be present, use
session.flush() to ensure it's happened.

> So my question(s):
>
> Is there a clean way to always acces this parent's parent attribute?
>
> If not, would be starting a new session and query it from the event handler
> be an option? (is it not dangerous, because it seems to work)
>
> Additional quirk, I am working within a transaction manager (pyramid_tm) and
> ZopeTransactionExtension()

assuming things are flushed you should be able to access the object
graph and the various .id attributes fully.   session.flush() will get
you there.

>
> Thanks!
>
>
> More information about the system:
>
> SQLAlchemy 1.1.13
>
> Python 3.5
>
> Postgres 9.6
>
> --
> 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.

Re: [sqlalchemy] horizontal sharding and bulk_insert

[Mike Bayer]

On Tue, Aug 22, 2017 at 3:43 AM, Антонио Антуан  wrote:
> Hi guys
> I tried to implement horizontal sharding in my project. Everything is ok,
> except bulk_inserts.
> When I run tests, I got this error:
>   File "/home/anton/Projects/proj/core/model/messages.py", line 210, in
> create
> Session.bulk_insert_mappings(MessageEntity, to_commit)
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/scoping.py", line 157,
> in do
> return getattr(self.registry(), name)(*args, **kwargs)
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 2345,
> in bulk_insert_mappings
> mapper, mappings, False, False, return_defaults, False)
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 2416,
> in _bulk_save_mappings
> transaction.rollback(_capture_exception=True)
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py", line
> 60, in __exit__
> compat.reraise(exc_type, exc_value, exc_tb)
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 2411,
> in _bulk_save_mappings
> mapper, mappings, transaction, isstates, return_defaults)
>   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/persistence.py", line
> 35, in _bulk_insert
> "connection_callable / per-instance sharding "
> NotImplementedError: connection_callable / per-instance sharding not
> supported in bulk_insert()
>
> I do not understand what 'connection_callable' does, but I really need to
> implement it. Is there any ways to do it?

The idea of bulk_insert() is strictly one of performance with some
degree of convenience; you don't need it to actually accomplish
anything as there are many other ways to achieve what it does.   the
"connection_callable" thing is specifically so that an ORM persistence
operation can get at the appropriate database connection for a
particular INSERT/UPDATE/DELETE, however this adds complexity so is
not part of bulk_insert().

In the case of application side horizontal sharding, you'd want to
take the data you're passing to it and pre-sort it into individual
per-shard sets, then use a Session per shard to run the inserts.

Also, application side horizontal sharding is not a great way to do
sharding in any case.  If you're on Postgresql I'd strongly recommend
using table inheritance instead.


>
> If it can help: I ALWAYS perform UPDATE/INSERT/DELETE and most of SELECT
> queries in only one database. So, I wrote such subclass:
>
>
> class ShardedSessionWithDefaultBind(ShardedSession):
> def get_bind(self, mapper, shard_id=None, instance=None, clause=None,
> **kw):
> if shard_id is None:
> shard_id = default_shard_id
> return super(ShardedSessionWithDefaultBind, self).get_bind(mapper,
> shard_id, instance, clause, **kw)
>
>
> Maybe I can override "__init__" for my class and write
> "self.connnection_callable = None"?
> My research of sqlalchemy code didn't make me sure that it is safe enough.
> But I see, that "connection_callable" used only for checking into
> "_bulk_insert" and "_bulk_update" functions in sqlalchemy.orm.persistence
> module.
> So, if I 100% sure, that I ALWAYS perform INSERT/UPDATE/DELETE queries in
> only one database, can I make 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 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] getting model's grandparent after persistent_to_deleted event has fired.

[cecemel]

Hi, 

so, I'm currently facing this issue, where I would like to do some calls to 
an external service, when my object has been deleted within a flush. 
For this operation to occur, I need the id from my model object, but also 
the id from the parent of the parent object model. There are cases, where I 
am unable to access them (I guess, depending of the order of the execution) 
and I am unsure on what to do next.

So if you're willing to give me some advice, would be awesome. 

Here is a dummy model:

from sqlalchemy import event

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, backref, relationship

Base = declarative_base()


#
# MODEL
#
class House(Base):
__tablename__ = 'house'
id = Column(Integer, primary_key=True)
rooms = relationship("Room",
 backref=backref("house", lazy="joined"),
 cascade='all, delete-orphan')


class Room(Base):
__tablename__ = 'room'
id = Column(Integer, primary_key=True)
house_id = Column(Integer, ForeignKey('house.id'))
beds = relationship("Bed",
backref=backref("room", lazy="joined"),
cascade='all, delete-orphan')


class Bed(Base):
__tablename__ = 'bed'
id = Column(Integer, primary_key=True)
room_id = Column(Integer, ForeignKey('room.id'))


#
# CONFIG
#
def setup():
engine = create_engine("sqlite:///foo.db", echo=True)

Base.metadata.bind = engine
Base.metadata.create_all(engine)

SessionFactory = sessionmaker(
bind=engine
)

event.listen(SessionFactory, 'deleted_to_detached', 
listener_bed_has_been_removed)

return SessionFactory


def listener_bed_has_been_removed(session, instance):
if type(instance) is not Bed:
return

bed_id = instance.id
house_id = instance.room.house.id  # this is NOT ALWAYS there. Depending on 
the order of the execution I guess

print("execute the service call to external service here bed_id {}, 
house_id {}".format(bed_id, house_id))


 

So my question(s):

   - Is there a clean way to always acces this parent's parent attribute?
  - If not, would be starting a new session and query it from the event 
  handler be an option? (is it not dangerous, because it seems to work)
 - Additional quirk, I am working within a transaction manager 
 (pyramid_tm) and ZopeTransactionExtension()
  
Thanks!


More information about the system:

SQLAlchemy 1.1.13

Python 3.5

Postgres 9.6

-- 
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] Re: SQLAlchemy: backref and object loaded from DB not working?

[cecemel]

yep indeed, it works, I was doing something wrong. 

Thanks for all the help and sorry about late reply

On Thursday, August 17, 2017 at 9:57:39 PM UTC+2, cecemel wrote:
>
> Is the backref relationship supposed to work with objects loaded from the 
> DB?
>
>
> Here is the case: 
>
> Similar to the (doc/tutorial 
> ) 
> I have the following classes:
>
> from sqlalchemy import Integer, ForeignKey, String, Columnfrom 
> sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import 
> relationship
> Base = declarative_base()
> class User(Base):
> __tablename__ = 'user'
> id = Column(Integer, primary_key=True)
> name = Column(String)
>
> addresses = relationship("Address", back_populates="user")
> class Address(Base):
> __tablename__ = 'address'
> id = Column(Integer, primary_key=True)
> email = Column(String)
> user_id = Column(Integer, ForeignKey('user.id'))
>
> user = relationship("User", back_populates="addresses")
>
> When testing the snippet it works like expected:
>
> >>> u1 = User()>>> a1 = Address()>>> u1.addresses[]>>> print(a1.user)None
> >>> u1.addresses.append(a1)>>> u1.addresses[<__main__.Address object at 
> >>> 0x12a6ed0>]>>> a1.user<__main__.User object at 0x12a6590>
>
> But when doing the same with a user loaded to the db:
>
> >>> u1 = get_user_by_id_from_db(1)
> >>> a1 = Address()
> >>> u1.addresses # fair, the user didn't have any address.
> []
> >>> u1.addresses.append(a1)
> >>> u1.addresses # I didn't expect empty list!!
> []
>
> >>> a1.user
>
> None
>
> So my questions:
>
>- - is this supposed to work?
>- - if so, how can I make it work?
>- - if not, any workarounds proposed (and why not)?
>
> More information about the system:
>
> SQLAlchemy 1.1.13
>
> Python 3.5
>
> Postgres 9.6
>
>
>
> thanks again!
>
>
> Please let me know if any details miss.
>
>
>

-- 
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.

Re: [sqlalchemy] SQLAlchemy 1.1 cause core dump committing multiple dirty records outside of function where mutation happens

[Simon King]

On Tue, Aug 22, 2017 at 9:26 AM, Konstantin Kashin  wrote:
> # Setup
> Suppose I have a table with two fields: a string primary key and a boolean
> flag. I want to query multiple rows and then update the flag across all of
> them, then commit my changes. I have a MySQL DB with the following DBAPI
> (http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html).
>
> ## Model
> class TestSQLTable(Base):
> __tablename__ = 'test_table'
>
>
> some_string_id = Column(String(32), nullable=False, primary_key=True)
> flag = Column(Boolean, nullable=False, default=True)
>
>
> # SQLAlchemy 1.0.12
>
> In SQLAlchemy 1.0.12, the following worked:
>
>
> sm = sessionmaker(
> autocommit=False,
> autoflush=True,
> expire_on_commit=True,
> bind=engine,  # MySQL engine
> )
> session_factory = scoped_session(sm)
>
>
> def modify_records(session, flag=False):
> records = session.query(TestSQLTable).all()
> for r in records:
> r.flag = flag
>
>
> session = session_factory()
> modify_records(session)
> session.commit()
>
>
>
> # SQLAlchemy 1.1.13
> Now, this does *not* work and causes a core dump. I am unable to tell what
> change was made in 1.1 that causes this different behavior when reading the
> ["What's New in SQLAlchemy
> 1.1?"](http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html)
> guide.
>
> If I do this in a Jupyter notebook, for instance, the kernel just crashes on
> commit without any error.
>
> However, this **does** work for just a single row:
>
> def modify_record(session, flag=False):
> record = session.query(TestSQLTable).first()
> record.flag = flag
>
>
> ## Ways I have gotten this to work
>
> ### Add an explicit return
>
> def modify_records(session, flag=False):
> records = session.query(TestSQLTable).all()
> for r in records:
> r.flag = flag
> return records  # ADD THIS!
>
>
> ### Flush after each mutation
>
>
> def modify_records(session, flag=False):
> records = session.query(TestSQLTable).all()
> for r in records:
> r.flag = flag
> session.flush()  # ADD THIS!
>
>
> ### Restructure code so that the commit happens inside `modify_records`
>
> This works, but is not the way the application is currently built and would
> thus require a major refactor (b/c commit happens via a decorator).
>
> # Main question
>
> Why does this happen exactly for multiple records and why did this work in
> 1.0 but does not in 1.1?

What platform are you running this on, and how have you installed
MySQLdb and SQLAlchemy? If you ugraded SQLAlchemy in-place, is there
any chance that you are running a mix of old and new code? Have you
reproduced the error in a completely fresh installation?

Simon

-- 
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] SQLAlchemy 1.1 cause core dump committing multiple dirty records outside of function where mutation happens

[Konstantin Kashin]

# Setup
Suppose I have a table with two fields: a string primary key and a boolean 
flag. I want to query multiple rows and then update the flag across all of 
them, then commit my changes. I have a MySQL DB with the following DBAPI 
(http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html).

## Model
class TestSQLTable(Base):
__tablename__ = 'test_table'


some_string_id = Column(String(32), nullable=False, primary_key=True)
flag = Column(Boolean, nullable=False, default=True)


# SQLAlchemy 1.0.12

In SQLAlchemy 1.0.12, the following worked:


sm = sessionmaker(
autocommit=False,
autoflush=True,
expire_on_commit=True,
bind=engine,  # MySQL engine
)
session_factory = scoped_session(sm)


def modify_records(session, flag=False):
records = session.query(TestSQLTable).all()
for r in records:
r.flag = flag


session = session_factory()
modify_records(session)
session.commit()



# SQLAlchemy 1.1.13
Now, this does *not* work and causes a core dump. I am unable to tell what 
change was made in 1.1 that causes this different behavior when reading the 
["What's New in SQLAlchemy 
1.1?"](http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html) 
guide.

If I do this in a Jupyter notebook, for instance, the kernel just crashes 
on commit without any error.

However, this **does** work for just a single row:

def modify_record(session, flag=False):
record = session.query(TestSQLTable).first()
record.flag = flag


## Ways I have gotten this to work

### Add an explicit return

def modify_records(session, flag=False):
records = session.query(TestSQLTable).all()
for r in records:
r.flag = flag
return records  # ADD THIS!


### Flush after each mutation


def modify_records(session, flag=False):
records = session.query(TestSQLTable).all()
for r in records:
r.flag = flag
session.flush()  # ADD THIS!


### Restructure code so that the commit happens inside `modify_records`

This works, but is not the way the application is currently built and would 
thus require a major refactor (b/c commit happens via a decorator).

# Main question

Why does this happen exactly for multiple records and why did this work in 
1.0 but does not in 1.1?


Thanks in advance!

-- 
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] horizontal sharding and bulk_insert

[Антонио Антуан]

Hi guys
I tried to implement horizontal sharding 
 
in my project. Everything is ok, except bulk_inserts.
When I run tests, I got this error:
  File "/home/anton/Projects/proj/core/model/messages.py", line 210, in 
create
Session.bulk_insert_mappings(MessageEntity, to_commit)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/scoping.py", line 157, 
in do
return getattr(self.registry(), name)(*args, **kwargs)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 2345, 
in bulk_insert_mappings
mapper, mappings, False, False, return_defaults, False)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 2416, 
in _bulk_save_mappings
transaction.rollback(_capture_exception=True)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py", line 
60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 2411, 
in _bulk_save_mappings
mapper, mappings, transaction, isstates, return_defaults)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/persistence.py", line 
35, in _bulk_insert
"connection_callable / per-instance sharding "
NotImplementedError: connection_callable / per-instance sharding not 
supported in bulk_insert()

I do not understand what 'connection_callable' does, but I really need to 
implement it. Is there any ways to do it?

If it can help: I ALWAYS perform UPDATE/INSERT/DELETE and most of SELECT 
queries in only one database. So, I wrote such subclass:


class ShardedSessionWithDefaultBind(ShardedSession):
def get_bind(self, mapper, shard_id=None, instance=None, clause=None, **kw):
if shard_id is None:
shard_id = default_shard_id
return super(ShardedSessionWithDefaultBind, self).get_bind(mapper, 
shard_id, instance, clause, **kw)


Maybe I can override "__init__" for my class and write 
"self.connnection_callable = None"? 
My research of sqlalchemy code didn't make me sure that it is safe enough. 
But I see, that "connection_callable" used only for checking into 
"_bulk_insert" and "_bulk_update" functions in sqlalchemy.orm.persistence 
module. 
So, if I 100% sure, that I ALWAYS perform INSERT/UPDATE/DELETE queries in 
only one database, can I make 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 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.