Re: [sqlalchemy] Objects inadvertently being added to session
Hi Mike,
Thanks so much for the reply and the pointer.
Since I never added anything to the session explicitly, I think I was
missing that loading an object implicitly adds that object to the session -
which does make sense.
Is that right ?
thanks again,
terry
On Monday, June 15, 2015 at 11:26:39 AM UTC-4, Michael Bayer wrote:
On 6/15/15 11:12 AM, T Mark wrote:
Hi there,
I have been pulling my hair out on this one.
I understood that objects make it into the session only due to an
explicit call to add().
or if they are associated with a parent object that is added to the
Session via add(), or if they are associated with an object that is already
present in a Session via add(); this also will occur for backrefs, e.g. A
is in the session, B.a is referred to A, B.a has a backref A.bs, therefore
B is now added. This is configurable.
But, I seem to be seeing objects being added without my explicitly doing
so. Is this to be expected ?
yes. please see:
http://docs.sqlalchemy.org/en/rel_1_0/orm/cascades.html?highlight=cascades
For instance, I want to establish a many-to-many relationship between
two classes: say, for the purposes here, Person and Kid.
test= create table persons (id SERIAL NOT NULL);
test= CREATE TABLE person_to_kids (person_id INT NOT NULL, kid_id INT
NOT NULL);
test= create table kids (id SERIAL NOT NULL, name TEXT NOT NULL);
test= insert into kids (name) VALUES ('Fred');
test= insert into kids (name) VALUES ('Barney');
person_to_kids = Table('person_to_kids',
Base.metadata,
Column('person_id', Integer, ForeignKey('
persons.id')),
Column('kid_id', Integer, ForeignKey('kids.id')))
class Person(Base):
__tablename__ = 'persons'
id = Column('id', Integer, primary_key = True)
def __init__(self,
kids = []):
kids = Kid.get_kids(kid_names = kids)
print(__init__ before kids assignment)
print(session.new)
Assigning to self.kids here seems to add self to session ???
self.kids=kids
print(After assignment to self.kids)
print(session.new)
class Kid(Base):
__tablename__ = 'kids'
id = Column(Integer, primary_key = True)
name = Column(String)
parents = relationship(Person,
secondary = person_to_kids,
backref=kids)
def __init__(self, name = None):
self.name = name
@staticmethod
def get_kids(kid_names = []):
kids = []
for name in kid_names:
# find first kid
target_set = session.query(Kid).filter(Kid.name ==
name).first()
kids.append(target_set)
return kids
What is puzzling me is that, if I have a collection of Kid objects, and
I assign it to the kids collection in a Person, the Person object seems to
be automatically added to the session and marked as pending, even if I have
not added it.
For instance, if the Persons table is empty:
test= select * from persons;
id
(0 rows)
and I run the following code:
print(session.new)
obj = Person(kids = ['Barney', 'Fred'])
print(obj has been created)
print(session.new)
session.commit()
The output shows that the Person object is added immediately after the
assignment to obj.kids, without any call to session.add() anywhere in the
code:
IdentitySet([])
__init__ before kids assignment
IdentitySet([])
After assignment to self.kids
IdentitySet([__main__.Person object at 0x7fb6ce447b10])
obj has been created
IdentitySet([__main__.Person object at 0x7fb6ce447b10])
And indeed, due to the commit() at the end, the person object makes it
into the database:
test= select * from persons;
id
10
(1 row)
But, I understood that objects (only) make it into a session by virtue
of being explicitly added.
So, is this the correct behavior, or am I misunderstanding something ?
If I'm not misunderstanding this all, the complete code is at
https://github.com/NuggyBuggy/sqlalchemy_question.git .
Thanks for reading,
terry
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
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.
[sqlalchemy] Re: Objects inadvertently being added to session
I forgot to mention:
I'm using:
- Python 2.7.6 on Linux,
- SQLAlchemy version 0.9.9.
- PostgreSQL 9.3
Thanks -
terry
On Monday, June 15, 2015 at 11:12:54 AM UTC-4, T Mark wrote:
Hi there,
I have been pulling my hair out on this one.
I understood that objects make it into the session only due to an explicit
call to add(). But, I seem to be seeing objects being added without my
explicitly doing so. Is this to be expected ?
For instance, I want to establish a many-to-many relationship between two
classes: say, for the purposes here, Person and Kid.
test= create table persons (id SERIAL NOT NULL);
test= CREATE TABLE person_to_kids (person_id INT NOT NULL, kid_id INT
NOT NULL);
test= create table kids (id SERIAL NOT NULL, name TEXT NOT NULL);
test= insert into kids (name) VALUES ('Fred');
test= insert into kids (name) VALUES ('Barney');
person_to_kids = Table('person_to_kids',
Base.metadata,
Column('person_id', Integer, ForeignKey('
persons.id')),
Column('kid_id', Integer, ForeignKey('kids.id')))
class Person(Base):
__tablename__ = 'persons'
id = Column('id', Integer, primary_key = True)
def __init__(self,
kids = []):
kids = Kid.get_kids(kid_names = kids)
print(__init__ before kids assignment)
print(session.new)
Assigning to self.kids here seems to add self to session ???
self.kids=kids
print(After assignment to self.kids)
print(session.new)
class Kid(Base):
__tablename__ = 'kids'
id = Column(Integer, primary_key = True)
name = Column(String)
parents = relationship(Person,
secondary = person_to_kids,
backref=kids)
def __init__(self, name = None):
self.name = name
@staticmethod
def get_kids(kid_names = []):
kids = []
for name in kid_names:
# find first kid
target_set = session.query(Kid).filter(Kid.name ==
name).first()
kids.append(target_set)
return kids
What is puzzling me is that, if I have a collection of Kid objects, and I
assign it to the kids collection in a Person, the Person object seems to be
automatically added to the session and marked as pending, even if I have
not added it.
For instance, if the Persons table is empty:
test= select * from persons;
id
(0 rows)
and I run the following code:
print(session.new)
obj = Person(kids = ['Barney', 'Fred'])
print(obj has been created)
print(session.new)
session.commit()
The output shows that the Person object is added immediately after the
assignment to obj.kids, without any call to session.add() anywhere in the
code:
IdentitySet([])
__init__ before kids assignment
IdentitySet([])
After assignment to self.kids
IdentitySet([__main__.Person object at 0x7fb6ce447b10])
obj has been created
IdentitySet([__main__.Person object at 0x7fb6ce447b10])
And indeed, due to the commit() at the end, the person object makes it
into the database:
test= select * from persons;
id
10
(1 row)
But, I understood that objects (only) make it into a session by virtue of
being explicitly added.
So, is this the correct behavior, or am I misunderstanding something ?
If I'm not misunderstanding this all, the complete code is at
https://github.com/NuggyBuggy/sqlalchemy_question.git .
Thanks for reading,
terry
--
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] Objects inadvertently being added to session
On 6/15/15 11:12 AM, T Mark wrote:
Hi there,
I have been pulling my hair out on this one.
I understood that objects make it into the session only due to an
explicit call to add().
or if they are associated with a parent object that is added to the
Session via add(), or if they are associated with an object that is
already present in a Session via add(); this also will occur for
backrefs, e.g. A is in the session, B.a is referred to A, B.a has a
backref A.bs, therefore B is now added. This is configurable.
But, I seem to be seeing objects being added without my explicitly
doing so. Is this to be expected ?
yes. please see:
http://docs.sqlalchemy.org/en/rel_1_0/orm/cascades.html?highlight=cascades
For instance, I want to establish a many-to-many relationship between
two classes: say, for the purposes here, Person and Kid.
|
test= create table persons (id SERIAL NOT NULL);
test= CREATE TABLE person_to_kids (person_id INT NOT NULL, kid_id INT
NOT NULL);
test= create table kids (id SERIAL NOT NULL, name TEXT NOT NULL);
test= insert into kids (name) VALUES ('Fred');
test= insert into kids (name) VALUES ('Barney');
person_to_kids = Table('person_to_kids',
Base.metadata,
Column('person_id', Integer, ForeignKey('persons.id')),
Column('kid_id', Integer, ForeignKey('kids.id')))
class Person(Base):
__tablename__ = 'persons'
id = Column('id', Integer, primary_key = True)
def __init__(self,
kids = []):
kids = Kid.get_kids(kid_names = kids)
print(__init__ before kids assignment)
print(session.new)
Assigning to self.kids here seems to add self to session ???
self.kids=kids
print(After assignment to self.kids)
print(session.new)
class Kid(Base):
__tablename__ = 'kids'
id = Column(Integer, primary_key = True)
name = Column(String)
parents = relationship(Person,
secondary = person_to_kids,
backref=kids)
def __init__(self, name = None):
self.name = name
@staticmethod
def get_kids(kid_names = []):
kids = []
for name in kid_names:
# find first kid
target_set = session.query(Kid).filter(Kid.name ==
name).first()
kids.append(target_set)
return kids
|
What is puzzling me is that, if I have a collection of Kid objects,
and I assign it to the kids collection in a Person, the Person object
seems to be automatically added to the session and marked as pending,
even if I have not added it.
For instance, if the Persons table is empty:
|
test= select * from persons;
id
(0 rows)
|
and I run the following code:
|
print(session.new)
obj = Person(kids = ['Barney', 'Fred'])
print(obj has been created)
print(session.new)
session.commit()
|
The output shows that the Person object is added immediately after the
assignment to obj.kids, without any call to session.add() anywhere in
the code:
|
IdentitySet([])
|
__init__ before kids assignment
IdentitySet([])
After assignment to self.kids
IdentitySet([__main__.Person object at 0x7fb6ce447b10])
obj has been created
IdentitySet([__main__.Person object at 0x7fb6ce447b10])
And indeed, due to the commit() at the end, the person object makes it
into the database:
|
test= select * from persons;
id
10
(1 row)
|
But, I understood that objects (only) make it into a session by virtue
of being explicitly added.
So, is this the correct behavior, or am I misunderstanding something ?
If I'm not misunderstanding this all, the complete code is
at https://github.com/NuggyBuggy/sqlalchemy_question.git .
Thanks for reading,
terry
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto: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] Objects inadvertently being added to session
Hi there,
I have been pulling my hair out on this one.
I understood that objects make it into the session only due to an explicit
call to add(). But, I seem to be seeing objects being added without my
explicitly doing so. Is this to be expected ?
For instance, I want to establish a many-to-many relationship between two
classes: say, for the purposes here, Person and Kid.
test= create table persons (id SERIAL NOT NULL);
test= CREATE TABLE person_to_kids (person_id INT NOT NULL, kid_id INT NOT
NULL);
test= create table kids (id SERIAL NOT NULL, name TEXT NOT NULL);
test= insert into kids (name) VALUES ('Fred');
test= insert into kids (name) VALUES ('Barney');
person_to_kids = Table('person_to_kids',
Base.metadata,
Column('person_id', Integer,
ForeignKey('persons.id')),
Column('kid_id', Integer, ForeignKey('kids.id')))
class Person(Base):
__tablename__ = 'persons'
id = Column('id', Integer, primary_key = True)
def __init__(self,
kids = []):
kids = Kid.get_kids(kid_names = kids)
print(__init__ before kids assignment)
print(session.new)
Assigning to self.kids here seems to add self to session ???
self.kids=kids
print(After assignment to self.kids)
print(session.new)
class Kid(Base):
__tablename__ = 'kids'
id = Column(Integer, primary_key = True)
name = Column(String)
parents = relationship(Person,
secondary = person_to_kids,
backref=kids)
def __init__(self, name = None):
self.name = name
@staticmethod
def get_kids(kid_names = []):
kids = []
for name in kid_names:
# find first kid
target_set = session.query(Kid).filter(Kid.name == name).first()
kids.append(target_set)
return kids
What is puzzling me is that, if I have a collection of Kid objects, and I
assign it to the kids collection in a Person, the Person object seems to be
automatically added to the session and marked as pending, even if I have
not added it.
For instance, if the Persons table is empty:
test= select * from persons;
id
(0 rows)
and I run the following code:
print(session.new)
obj = Person(kids = ['Barney', 'Fred'])
print(obj has been created)
print(session.new)
session.commit()
The output shows that the Person object is added immediately after the
assignment to obj.kids, without any call to session.add() anywhere in the
code:
IdentitySet([])
__init__ before kids assignment
IdentitySet([])
After assignment to self.kids
IdentitySet([__main__.Person object at 0x7fb6ce447b10])
obj has been created
IdentitySet([__main__.Person object at 0x7fb6ce447b10])
And indeed, due to the commit() at the end, the person object makes it into
the database:
test= select * from persons;
id
10
(1 row)
But, I understood that objects (only) make it into a session by virtue of
being explicitly added.
So, is this the correct behavior, or am I misunderstanding something ?
If I'm not misunderstanding this all, the complete code is
at https://github.com/NuggyBuggy/sqlalchemy_question.git .
Thanks for reading,
terry
--
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] Reproducible oddity in with_for_update()
I have an issue which I have boiled down to a full test case below. This
test program reproduces the problem with both sqlalchemy 0.9.9 and 1.0.5,
under python 2.7.6 and ubuntu 14.04, and PyMySQL-0.6.2.
There are a combination of circumstances:
1. After you rollback a session, touching any attribute of an object (even
just accessing its id) causes the whole object to be re-read from the
database. That's OK.
2. Reading the object again using a new query and with_for_update()
generates a fresh query with SELECT .. FOR UPDATE. This is what I expect.
It also correctly blocks if another client has the row locked.
3. However, once the query has completed, the data seen in the object
appears to be the value read from the previous query, not the SELECT .. FOR
UPDATE one.
In the test program, a database object is created with val=abc. Two
threads both read the row under a lock, append X and write it back again.
So the final answer should be abcXX, but in fact it's abcX.
Points to note:
- this has to be run on a proper database (I am using mysql). sqlite
doesn't support SELECT .. FOR UPDATE.
- I have some workarounds. If instead of reading a new object I do
db.refresh(v,
lockmode=update) then all is fine. However I understood that the
lockmode=string interface is being deprecated.
Similarly, if I discard the object using db.expire(v) before reading it
again then it also works correctly. But in any case, I'd like to understand
why it doesn't work to fetch the new object in the way I am, and I suspect
a bug. Surely if SQLAlchemy has just issued a SELECT .. FOR UPDATE then the
object should be updated with the values of that SELECT?
Regards,
Brian.
-
from __future__ import absolute_import, division, print_function,
unicode_literals
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from contextlib import contextmanager
from six.moves.queue import Queue, Empty
from threading import Thread
DEFAULT_DB_URI = 'mysql+pymysql://root@localhost/testdb'
Base = declarative_base()
class Foo(Base):
__tablename__ = foo
id = Column(Integer, primary_key=True)
val = Column(String(255))
engine = create_engine(DEFAULT_DB_URI, echo=True)
try: Base.metadata.drop_all(engine)
except: pass
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
@contextmanager
def private_session():
s = Session()
try:
yield s
finally:
s.rollback()
s.close()
def runner(ref, omsg, imsg):
with private_session() as db:
print( Read object)
v = db.query(Foo).filter_by(id=ref).one()
print( Discard session)
db.rollback()
print( Get object's id)
id = v.id
print( Reload object with FOR UPDATE)
# db.expire(v)
v = db.query(Foo).filter_by(id=id).with_for_update().one()
# Alt: db.refresh(v, lockmode='update')
print( v.val=%r % v.val)
omsg.put(started)
imsg.get()
v.val += X
db.commit()
with private_session() as db:
f = Foo(id=1, val=abc)
db.add(f)
db.commit()
o1 = Queue()
i1 = Queue()
o2 = Queue()
i2 = Queue()
t1 = Thread(target=runner, kwargs={ref:1, omsg: o1, imsg: i1})
t2 = Thread(target=runner, kwargs={ref:1, omsg: o2, imsg: i2})
t1.start()
assert o1.get(True, 1) == started
# Next thread should block on SELECT FOR UPDATE
t2.start()
try:
o2.get(True, 1)
raise RuntimeError(This thread should be blocked on SELECT FOR
UPDATE)
except Empty:
pass
# Let first thread complete
i1.put(go)
# Now second thread is unblocked
assert o2.get(True, 1) == started
i2.put(go)
t1.join(2)
assert not t1.isAlive()
t2.join(2)
assert not t2.isAlive()
# Check final state
print(*** FINISHED ***)
id = f.id
print(*** RESULTS ***)
print(id=%d % f.id)
print(val=%r % f.val)
Base.metadata.drop_all(engine)
--
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] Objects inadvertently being added to session
On 6/15/15 11:48 AM, T Mark wrote:
Hi Mike,
Thanks so much for the reply and the pointer.
Since I never added anything to the session explicitly, I think I was
missing that loading an object implicitly adds that object to the
session - which does make sense.
Is that right ?
All the objects we deal with when using the ORM represent a row in the
database, in terms of a transaction. So when we load rows, the objects
which proxy those rows stay associated with the session. The ORM uses
the lazy loader pattern so that when unloaded attributes are accessed,
the associated Session is used as a source of transactional context.
Objects that are used in this way are known as persistent objects.
There are several states an object might have, including states that are
unassociated with a Session as well. These states are discussed at
http://docs.sqlalchemy.org/en/rel_1_0/orm/session_state_management.html.
thanks again,
terry
On Monday, June 15, 2015 at 11:26:39 AM UTC-4, Michael Bayer wrote:
On 6/15/15 11:12 AM, T Mark wrote:
Hi there,
I have been pulling my hair out on this one.
I understood that objects make it into the session only due to an
explicit call to add().
or if they are associated with a parent object that is added to
the Session via add(), or if they are associated with an object
that is already present in a Session via add(); this also will
occur for backrefs, e.g. A is in the session, B.a is referred to
A, B.a has a backref A.bs, therefore B is now added. This is
configurable.
But, I seem to be seeing objects being added without my
explicitly doing so. Is this to be expected ?
yes. please see:
http://docs.sqlalchemy.org/en/rel_1_0/orm/cascades.html?highlight=cascades
http://docs.sqlalchemy.org/en/rel_1_0/orm/cascades.html?highlight=cascades
For instance, I want to establish a many-to-many relationship
between two classes: say, for the purposes here, Person and Kid.
|
test= create table persons (id SERIAL NOT NULL);
test= CREATE TABLE person_to_kids (person_id INT NOT
NULL, kid_id INT NOT NULL);
test= create table kids (id SERIAL NOT NULL, name TEXT NOT NULL);
test= insert into kids (name) VALUES ('Fred');
test= insert into kids (name) VALUES ('Barney');
person_to_kids = Table('person_to_kids',
Base.metadata,
Column('person_id', Integer, ForeignKey('persons.id
http://persons.id')),
Column('kid_id', Integer, ForeignKey('kids.id http://kids.id')))
class Person(Base):
__tablename__ = 'persons'
id = Column('id', Integer, primary_key = True)
def __init__(self,
kids = []):
kids = Kid.get_kids(kid_names = kids)
print(__init__ before kids assignment)
print(session.new)
Assigning to self.kids here seems to add self to session ???
self.kids=kids
print(After assignment to self.kids)
print(session.new)
class Kid(Base):
__tablename__ = 'kids'
id = Column(Integer, primary_key = True)
name = Column(String)
parents = relationship(Person,
secondary = person_to_kids,
backref=kids)
def __init__(self, name = None):
self.name http://self.name = name
@staticmethod
def get_kids(kid_names = []):
kids = []
for name in kid_names:
# find first kid
target_set = session.query(Kid).filter(Kid.name ==
name).first()
kids.append(target_set)
return kids
|
What is puzzling me is that, if I have a collection of Kid
objects, and I assign it to the kids collection in a Person, the
Person object seems to be automatically added to the session and
marked as pending, even if I have not added it.
For instance, if the Persons table is empty:
|
test= select * from persons;
id
(0 rows)
|
and I run the following code:
|
print(session.new)
obj = Person(kids = ['Barney', 'Fred'])
print(obj has been created)
print(session.new)
session.commit()
|
The output shows that the Person object is added immediately
after the assignment to obj.kids, without any call to
session.add() anywhere in the code:
|
IdentitySet([])
|
__init__ before kids assignment
IdentitySet([])
After assignment to self.kids
IdentitySet([__main__.Person object at 0x7fb6ce447b10])
obj has been created
IdentitySet([__main__.Person object at 0x7fb6ce447b10])
And indeed, due to the commit() at the end, the person object
makes it into the database:
|
test= select * from persons;
id
10
(1 row)
|
But, I understood that objects (only) make it into a session by
virtue of being explicitly added.
Re: [sqlalchemy] Reproducible oddity in with_for_update()
On 6/15/15 3:01 PM, Brian Candler wrote:
I have an issue which I have boiled down to a full test case below.
This test program reproduces the problem with both sqlalchemy 0.9.9
and 1.0.5, under python 2.7.6 and ubuntu 14.04, and PyMySQL-0.6.2.
There are a combination of circumstances:
1. After you rollback a session, touching any attribute of an object
(even just accessing its id) causes the whole object to be re-read
from the database. That's OK.
2. Reading the object again using a new query and with_for_update()
generates a fresh query with SELECT .. FOR UPDATE. This is what I
expect. It also correctly blocks if another client has the row locked.
3. However, once the query has completed, the data seen in the object
appears to be the value read from the previous query, not the SELECT
.. FOR UPDATE one.
either run session.expire_all() / session.expire(some_object) ahead of
time, or run the query including the populate_existing() method:
http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=populate_existing#sqlalchemy.orm.query.Query.populate_existing
In the test program, a database object is created with val=abc. Two
threads both read the row under a lock, append X and write it back
again. So the final answer should be abcXX, but in fact it's abcX.
Points to note:
- this has to be run on a proper database (I am using mysql). sqlite
doesn't support SELECT .. FOR UPDATE.
- I have some workarounds. If instead of reading a new object I do
db.refresh(v, lockmode=update) then all is fine. However I
understood that the lockmode=string interface is being deprecated.
Similarly, if I discard the object using db.expire(v) before reading
it again then it also works correctly. But in any case, I'd like to
understand why it doesn't work to fetch the new object in the way I
am, and I suspect a bug. Surely if SQLAlchemy has just issued a SELECT
.. FOR UPDATE then the object should be updated with the values of
that SELECT?
Regards,
Brian.
-
from __future__ import absolute_import, division, print_function,
unicode_literals
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from contextlib import contextmanager
from six.moves.queue import Queue, Empty
from threading import Thread
DEFAULT_DB_URI = 'mysql+pymysql://root@localhost/testdb'
Base = declarative_base()
class Foo(Base):
__tablename__ = foo
id = Column(Integer, primary_key=True)
val = Column(String(255))
engine = create_engine(DEFAULT_DB_URI, echo=True)
try: Base.metadata.drop_all(engine)
except: pass
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
@contextmanager
def private_session():
s = Session()
try:
yield s
finally:
s.rollback()
s.close()
def runner(ref, omsg, imsg):
with private_session() as db:
print( Read object)
v = db.query(Foo).filter_by(id=ref).one()
print( Discard session)
db.rollback()
print( Get object's id)
id = v.id
print( Reload object with FOR UPDATE)
# db.expire(v)
v = db.query(Foo).filter_by(id=id).with_for_update().one()
# Alt: db.refresh(v, lockmode='update')
print( v.val=%r % v.val)
omsg.put(started)
imsg.get()
v.val += X
db.commit()
with private_session() as db:
f = Foo(id=1, val=abc)
db.add(f)
db.commit()
o1 = Queue()
i1 = Queue()
o2 = Queue()
i2 = Queue()
t1 = Thread(target=runner, kwargs={ref:1, omsg: o1, imsg: i1})
t2 = Thread(target=runner, kwargs={ref:1, omsg: o2, imsg: i2})
t1.start()
assert o1.get(True, 1) == started
# Next thread should block on SELECT FOR UPDATE
t2.start()
try:
o2.get(True, 1)
raise RuntimeError(This thread should be blocked on SELECT
FOR UPDATE)
except Empty:
pass
# Let first thread complete
i1.put(go)
# Now second thread is unblocked
assert o2.get(True, 1) == started
i2.put(go)
t1.join(2)
assert not t1.isAlive()
t2.join(2)
assert not t2.isAlive()
# Check final state
print(*** FINISHED ***)
id = f.id
print(*** RESULTS ***)
print(id=%d % f.id)
print(val=%r % f.val)
Base.metadata.drop_all(engine)
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto: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
Re: [sqlalchemy] Custom Flush
Thanks Michael.
before_flush looks like the way to go. From what I can see so far, it will
alleviate the need to call my add_or_remove function, increasing
reliability.
On Monday, 15 June 2015 10:27:06 UTC+12, Michael Bayer wrote:
On 6/14/15 5:49 PM, Richard Collins wrote:
I have an association object:
class FolderUserAccess(db.Model):
__tablename__ = folderuseraccess
folder_id = db.Column(db.Integer, db.ForeignKey('node.id'),
primary_key=True)
folder = db.relationship('Folder', back_populates='access')
user_id = db.Column(db.Integer, db.ForeignKey('user.id'),
primary_key=True, index=True)
user = db.relationship('User')
access = db.Column(TINYINT, nullable=False)
walk = db.Column(db.Boolean, nullable=False)
When the object is persisted to the database, I would like to delete the
corresponding row when access==0 and walk = False.
Here is my attempt to make this happen:
def add_or_remove(self):
Add or remove object depending on access and walk values
ensuring no empty records are stored in database
if self.access or self.walk:
if self in db.session.deleted:
db.make_transient(self)
else:
db.session.add(self)
else:
if inspect(self).persistent:
db.session.delete(self)
I call this on the object after updating it. It is complicated as it has
to deal with the fact that somewhere else in the same transaction the
object may have already been updated. It does not work (I will figure it
out eventually) and strikes me as the complicated way of doing things.
What I would really like to do is be able to write a custom
implementation for when the object gets flushed. Something like:
if self.access or self.walk:
#insert or update the row
else:
# delete the row
Is this possible? Or does anyone have any great ideas on how to achieve
the same result in a more straightforward way than my current approach.
event hooks like before_flush() or after_flush() are the best place to do
things like this. In before_flush(), you can scan through the list of
work to do and add new things to the Session for add() or delete(); in
after_flush(), you can look at what's happened and then emit specific SQL
on the session.connection() to make other changes after the fact.Other
popular hooks include the mapper hooks before_insert(), before_update(),
before_delete(), with these it's best to emit SQL on the given connection
as things happen within the flush process.
Thanks,
Richard
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
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.
