Re: [sqlalchemy] Validators not called for unhashable items when replacing the collection
Yes, I noticed the collection.decorator would imply doing the same
validation in two different places. That will have to work for now,
but I'll keep an eye on the issue for 1.2.
Thanks Mike.
On Wed, Jan 25, 2017 at 1:36 PM, mike bayer <mike...@zzzcomputing.com> wrote:
>
>
> On 01/24/2017 09:55 PM, Pedro Werneck wrote:
>>
>>
>> I have a relationship with a validator to automatically convert dicts
>> appended to the collection, so I can do something like this:
>>
>> my_obj.my_collection.append({"rel_type_id": x})
>>
>> Instead of this:
>>
>> my_obj.my_collection.append(RelType(rel_type_id=x))
>>
>> That works exactly as expected, but when I try to replace the whole
>> collection at once:
>>
>> my_obj.my_collection = [{"rel_type_id": x}]
>>
>> That results in a TypeError: unhashable type: 'dict', and the validator
>> method is never called. Apparently that happens when the
>> orm.collection.bulk_replace function uses sets to find the difference
>> between the old and the new collection. I don't see an straightforward
>> fix for that, it feels more like a limitation of the current
>> implementation than a bug.
>
>
> that's kind of beyond bug and more a design flaw. The bulk replace wants
> to hit the event listener only for "new" items, but we can't decide on the
> "new" items without running the event handler. The whole bulk replace idea
> would need to be changed to run the event listeners up front which suggests
> new events and whatnot.
>
>
> So here you'd need to use the "converter" implementation as well
> (http://docs.sqlalchemy.org/en/latest/orm/collections.html#sqlalchemy.orm.collections.collection.converter),
> here's a demo, unfortunately we need to mix both styles for complete
> coverage:
>
> class MyCollection(list):
>
> @collection.converter
> def convert(self, value):
> return [B(data=v['data']) for v in value]
>
>
> class A(Base):
> __tablename__ = 'a'
> id = Column(Integer, primary_key=True)
>
> bs = relationship("B", collection_class=MyCollection)
>
> @validates('bs')
> def _go(self, key, value):
> if not isinstance(value, B):
> value = B(data=value['data'])
> return value
>
>
> class B(Base):
> __tablename__ = 'b'
> id = Column(Integer, primary_key=True)
> a_id = Column(ForeignKey('a.id'))
> data = Column(String)
>
>
> I think in the future, what might be nice here would be a new attribute
> event so that "converter" doesn't need to be used, and then @validates can
> include @validates.collection_validate or similar to handle this case. The
> collection hooks are generally assuming that they are dealing with how an
> incoming object should be represented within the collection, not how to
> coerce an incoming value (e.g. I tried to use @collection.appender here for
> the individual appends, no go), so "converter" being where it is, and not at
> value reception time, is inconsistent. The amount of collection hooks
> present compared to how not possible this use case is is kind of a disaster.
>
> I've added
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3896/bulk_replace-assumes-incoming-values-are.
>
>
>
>
>
>
>>
>> It looks like I could do what I want with a custom collection and the
>> collection.converter decorator. Any other ideas?
>>
>>
>> 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
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto: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
[sqlalchemy] Validators not called for unhashable items when replacing the collection
I have a relationship with a validator to automatically convert dicts
appended to the collection, so I can do something like this:
my_obj.my_collection.append({"rel_type_id": x})
Instead of this:
my_obj.my_collection.append(RelType(rel_type_id=x))
That works exactly as expected, but when I try to replace the whole
collection at once:
my_obj.my_collection = [{"rel_type_id": x}]
That results in a TypeError: unhashable type: 'dict', and the validator
method is never called. Apparently that happens when the
orm.collection.bulk_replace function uses sets to find the difference
between the old and the new collection. I don't see an straightforward fix
for that, it feels more like a limitation of the current implementation
than a bug.
It looks like I could do what I want with a custom collection and the
collection.converter decorator. Any other ideas?
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] One-to-one relationship as a boolean flag
I thought maybe there was a simpler way to do that, but the
hybrid_property works. Thanks.
On Wed, Jul 8, 2015 at 11:19 AM, Mike Bayer mike...@zzzcomputing.com wrote:
On 7/8/15 12:15 AM, Pedro Werneck wrote:
Let's say I have a table 'user', and for backwards compatibility reasons I
have a single-column table named 'user_active' which is basically just a
foreign key used as a boolean flag. I need my User model to have the
'active' field as a boolean mapped to that one-to-one relationship. So, I
have something like this:
class User(db.Model):
__tablename__ = 'user'
user_id = db.Column(db.Integer, primary_key=True)
# ... other fields
active = relationship('UserActive', backref='user', uselist=False)
class UserActive(db.Model)
__tablename__ = 'user_active'
user_id = db.Column(db.Integer, db.ForeignKey('user.user_id')
Using the simple relationship like that returns either the UserActive
instance, or None, which isn't ideal as True or False but works fine in any
boolean context. My problem is assignment and querying. How can can I get it
to work in a way that setting User.active = True creates the new UserActive
instance, and querying for User.active == True works as a join, not a
subquery?
why not make a @hybrid_property called active, call the relationship
_active, and then customize access completely?
--
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 a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/7azas9khOx0/unsubscribe.
To unsubscribe from this group and all its topics, 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.
--
---
Pedro Werneck
--
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] One-to-one relationship as a boolean flag
Let's say I have a table 'user', and for backwards compatibility reasons I
have a single-column table named 'user_active' which is basically just a
foreign key used as a boolean flag. I need my User model to have the
'active' field as a boolean mapped to that one-to-one relationship. So, I
have something like this:
class User(db.Model):
__tablename__ = 'user'
user_id = db.Column(db.Integer, primary_key=True)
# ... other fields
active = relationship('UserActive', backref='user', uselist=False)
class UserActive(db.Model)
__tablename__ = 'user_active'
user_id = db.Column(db.Integer, db.ForeignKey('user.user_id')
Using the simple relationship like that returns either the UserActive
instance, or None, which isn't ideal as True or False but works fine in any
boolean context. My problem is assignment and querying. How can can I get
it to work in a way that setting User.active = True creates the new
UserActive instance, and querying for User.active == True works as a join,
not a subquery?
--
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] Single class, multiple identical tables and non-primary mapper
I have one time with currently active data, and several other tables with
archive data, that are eventually moved to another database. Now I have to
a demand to make those archive tables available read-only through our API.
I'm using Flask and Flask-SQLAlchemy.
I tried to do it by using polymorphic identity with subclasses generated
reflexively, and querying with the base class. It works in principle, but
it generates UNION queries with a filtering condition that performs poorly
in MySQL, and selecting exactly which tables to query from is a pain.
Searching the web and the group, I found a message[1] from 2008 with
several ideas on how to do that. I tried option 2, using a non-primary
mapper, since the caveats mentioned there aren't a problem for me. For
testing, I did something like this:
class User(Model):
__tablename__ = 'user_active'
user_id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(32))
user_1 = Table('user_1', db.metadata,
db.Column('user_id', db.Integer, primary_key=True),
db.Column('username', db.String(32)))
user_2 = Table('user_2', db.metadata,
db.Column('user_id', db.Integer, primary_key=True),
db.Column('username', db.String(32)))
user_3 = Table('user_3', db.metadata,
db.Column('user_id', db.Integer, primary_key=True),
db.Column('username', db.String(32)))
And apparently that works for a plain select query, with no filtering
criterion:
print db.session.query(mapper(User, user_1, non_primary=True))
SELECT user_1.user_id AS user_1_user_id, user_1.username AS user_1_username
FROM user_1
However, as soon as I add filtering, this is what I get:
print db.session.query(mapper(User, user_1,
non_primary=True)).filter_by(username='pedro')
SELECT user_1.user_id AS user_1_user_id, user_1.username AS user_1_username
FROM user_1, user_active
WHERE user_active.username = :username_1
And I was expecting no mention of the table user_active at all.
So, two questions:
1. Is there a better approach to do what I'm trying to do?
2. If the above is a good approach, what am I doing wrong?
Thanks
[1]
https://groups.google.com/forum/#!msg/sqlalchemy/FTUo-bMJuYc/NClEROL8n_4J
--
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] Single class, multiple identical tables and non-primary mapper
On Wednesday, October 22, 2014 2:05:32 PM UTC-2, Michael Bayer wrote: this doesn’t sound like a use case for inheritance or multiple mappings for a single class. I'm aware it's not a real use case, but in principle it works for my needs. Inheritance is used just as a trick to get SQLAlchemy to query across all tables and UNION automatically. The problem is that the resulting query is not practical for big tables: For instance, if I do something like UserBase.query.filter_by(status='ACTIVE'), and I have subclasses User2013 and User2012, the query generated by sqlalchemy is something like: SELECT pjoin.* FROM (SELECT * from user_2013 UNION ALL SELECT * from user_2012) AS pjoin WHERE pjoin.status = 'ACTIVE'; And MySQL builds a temporary table with everything inside the UNION, and apply the WHERE clause to it, as a subclass. Typically the approach is just to create copies of your mappings to a new series of classes. An example of automating this is the “versioned objects” example at http://docs.sqlalchemy.org/en/rel_0_9/orm/examples.html#module-examples.versioned_history. Another way you might look into, if you’re attempting to avoid explicit mappings, is the automap system: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html. My problem isn't really automating the creation of mappings, but automating the queries on the secondary tables. For instance, today I have some code that does something like this: users = User.query.filter(User.x==1, , User.y==2, User.z==3).all() And now I need to include rows stored in other tables in that result set. I would like to be able to do something like: users = User.query.include_archive_since(date(2012, 1, 1)).filter(User.x==1, User.y==2, User.z==3).all() And the include_archive_since method would be able to figure out that it has to include User2012 and User2013 too. In other words, I'm trying to avoid the need for the code responsible for building the queries to have the knowledge of the archival structure behind everything. I already implemented this by having a proxy class that's used as User.query_class and it records all method calls. When the query is evaluated, it figures out which subclasses of User to use, builds the whole query for each one and returns the resulting union of everything. It works well, but it isn't very robust because I have to inspect the binary expressions like User.x==1, and build the equivalent User2012.x == 1, User2012.y == 2, etc, I have to reimplement a lot of stuff from Query, and so on. So, what I need is a magic function that takes the query object from: User.query.filter(User.x==1, User.y==2, User.z==3) And returns something equivalent to: User2012.query.filter(User2012.x==1, User2012.y==2, User2012.z==3) In simple terms, I need to do a search/replace on the query generated by sqlalchemy before it runs. -- 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] Single class, multiple identical tables and non-primary mapper
On Wed, Oct 22, 2014 at 3:27 PM, Michael Bayer mike...@zzzcomputing.com wrote: why not try something like: ualias = aliased(User, table_2012) query(ualias).filter(ualias.x == 1) I tried that before, but I always end up with a query to the two tables, and the filtering applied to the User table: u1 = aliased(User, user_1) print db.session.query(u1).filter(u1.username=='lero') SELECT user_active.user_id AS user_active_user_id, user_active.username AS user_active_username FROM user_active, user_1 WHERE user_active.username = :username_1 If I try aliased with adapt_on_names=True it gets reversed: u1 = aliased(User, user_1, adapt_on_names=True) print db.session.query(u1).filter(u1.username=='lero') SELECT user_active.user_id AS user_active_user_id, user_active.username AS user_active_username FROM user_active, user_1 WHERE user_1.username = :username_1 The WHERE clause is correct, but it's still selecting from user_active, not user_1, so I exactly the results I don't want. If I could get it to select from user_1, I could get that to work by making the user_active part an empty result set. query(User).select_from(table_2012) something like that. That was the first thing I tried before the non_primary mapper thing. It has the same problem: print db.session.query(User).select_from(user_1).filter(User.username=='pedro') SELECT user_active.user_id AS user_active_user_id, user_active.username AS user_active_username FROM user_active, user_1 WHERE user_active.username = :username_1 it can’t be 100% transparent, the query needs to be told that its selecting from something different. This inner implementation doesn't really need to be 100% transparent, as long as it gets correct results. -- 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] Single class, multiple identical tables and non-primary mapper
On Wed, Oct 22, 2014 at 6:10 PM, Michael Bayer mike...@zzzcomputing.com wrote: Yeah, that's because the alternate tables have no correspondence to the original one, the way a SELECT would.So without building some new kind of core selectable that acts this way, these approaches won't work here. Based on your case that you do want a UNION that populates a collection that is entirely straight User objects, that does imply these would be concrete inheriting subclasses. Yes, as I said before, that was my first attempt and it works, but generates queries with a WHERE clause is applied to the whole subquery formed by the UNIONs, and MySQL doesn't optimize that. Thanks for the insights anyway. I need this done soon, so I guess I'll keep working on my Query proxy and find a way to introspect and rebuild the binary expressions. As a last resort I can enforce the use of filter_by. -- 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] Single class, multiple identical tables and non-primary mapper
On Wed, Oct 22, 2014 at 7:02 PM, Michael Bayer mike...@zzzcomputing.com wrote: Yeah i think that is what you have to do, you want UNION but you want all the criteria generated on the inside. There’s two approaches to take here, either start with the UNION that polymorphic gives you, the rewrite it: SELECT u.* FROM (SELECT * FROM a UNION SELECT * FROM b) AS u WHERE u.x = y becomes - SELECT * FROM a WHERE a.x=y UNION SELECT * FROM b WHERE b.x=y or you can catch each SELECT as they are created earlier, before you build the UNION. The first approach might be a little harder to implement but it might be more robust. That's a great idea, but it looks like more work than I have time available now. I'll definitely try to do something like that later. SQLAlchemy does things like this all the time but they are not trivial to implement as you need to think about the whole expression. The visitor system in sqlalchemy.sql.visitors forms the basis for how operations like this are usually done, providing an interface used to scan and rebuild expressions. Whole-query rebuilds like that can be tricky, we for example do one against SQLite when we have a SELECT that has nested JOINs (see http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1, the code that does this is at https://bitbucket.org/zzzeek/sqlalchemy/src/56d5732fbdf09508784df6dc4c04e5b39ac6be85/lib/sqlalchemy/sql/compiler.py?at=master#cl-1330. That's really helpful for me right now. I'm using the cloning visitor to rebuild the expressions from the recorded filter() calls for the secondary models and it works like a charm. Thanks for the help. -- --- Pedro Werneck -- 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] SQLAlchemy + MySQLdb + Eventlets, proper way to do it?
On Fri, Apr 26, 2013 at 1:52 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Apr 25, 2013, at 9:26 PM, Pedro Werneck pjwern...@gmail.com wrote: So, basically it's just passing the eventlet pool as the creator for the create_engine call? Good. Right, it isn't critical at all. My system is working perfectly fine and much faster than without eventlets most of the time, but since I went over the trouble of changing it to using eventlets, I'd like to try getting the database connection right too and see if there's any significant improvemente. I did have the thought that if eventlet needs a special connection pool, you might want to disable SQLAlchemy's own pooling within create_engine() by passing poolclass=NullPool. Otherwise it will hold onto a small set of connections persistently, not sure if that's what eventlet's pool is trying to implement in a different way. That definitely makes sense, but when I set the NullPool while using the eventlet db_pool, I get the whole application stuck after a while, then I get a connection timeout on every thread. I just removed it and using a small SA pool instead, with no problems. Anyway, the eventlet db_pool works beautifully, and increased my throughput significantly. --- Pedro Werneck -- 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.
[sqlalchemy] SQLAlchemy + MySQLdb + Eventlets, proper way to do it?
I'm using SQLAlchemy with MySQLdb for processing dozen million daily tasks with Celery. Most of my queries are very quick and the tasks don't wait for I/O for too long, so I had great results using the eventlet pool for Celery. However, whenever I hit a chunk of data which is expected to lead to slower queries, the overall performance suffers a lot, so I guess I can't just let it block like that. I found the eventlet.db_pool, and there's this Nova example using it with SQLAlchemy and MySQLdb: http://bazaar.launchpad.net/~rackspace-titan/nova/sqlalchemy-eventlet/view/head:/nova/db/sqlalchemy/session.py However, in an old topic here asking the same about psycopg someone mentions that this isn't a good example and the guys probably don't know SA very well. https://groups.google.com/d/msg/sqlalchemy/CU_kknlnksQ/wI1Xw21vcLgJ So, any pointers on the proper way to do it? -- 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] SQLAlchemy + MySQLdb + Eventlets, proper way to do it?
On Thursday, April 25, 2013 7:06:31 PM UTC-3, Michael Bayer wrote: On Apr 25, 2013, at 2:15 PM, Pedro Werneck pjwe...@gmail.comjavascript: wrote: I'm using SQLAlchemy with MySQLdb for processing dozen million daily tasks with Celery. Most of my queries are very quick and the tasks don't wait for I/O for too long, so I had great results using the eventlet pool for Celery. However, whenever I hit a chunk of data which is expected to lead to slower queries, the overall performance suffers a lot, so I guess I can't just let it block like that. I found the eventlet.db_pool, and there's this Nova example using it with SQLAlchemy and MySQLdb: http://bazaar.launchpad.net/~rackspace-titan/nova/sqlalchemy-eventlet/view/head:/nova/db/sqlalchemy/session.py However, in an old topic here asking the same about psycopg someone mentions that this isn't a good example and the guys probably don't know SA very well. https://groups.google.com/d/msg/sqlalchemy/CU_kknlnksQ/wI1Xw21vcLgJ the recipe seems to make use of a connection pool provided by eventlet, the rest of what's there isn't very interesting (or necessary). I haven't used eventlet but seems fine to me ?I wouldn't say connection pooling is even very critical. If slow queries are the issue, eventlet would just need to make sure that network requests don't block. It's possible that gevent is more popular, I've had more experience playing with that. So, basically it's just passing the eventlet pool as the creator for the create_engine call? Good. Right, it isn't critical at all. My system is working perfectly fine and much faster than without eventlets most of the time, but since I went over the trouble of changing it to using eventlets, I'd like to try getting the database connection right too and see if there's any significant improvemente. Thanks! -- 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] Refresh session: rollback() or commit()?
hmm, is that because your model objects themselves are controlling the scope of the transaction ?That's another pattern I don't really recommend... As I mentioned, I'm using Flask-SQLAlchemy, where I have a global db instance of the SQLAlchemy class, which holds the engine, current session, session factory, etc. All models have high-level methods for save, delete, revert, etc, which use the global db.session. This isn't an issue for the web part of the application, because Flask creates a new session for each request context, but the workers are outside the request context and the tasks use the db.session directly, either through the models or by themselves. The session created by Flask on the request is a subclass of the scoped session with some signal handling extras. So, your solution works, but to implement that without changing everything, I have to replicate whatever the high-level methods do using that local session created when the task is called. I think it might be possible to create a request context on each task call, so everyone will have a fresh session on the global db.session, as if it were a web request, but I'll have to go into Flask internals to figure how to do that. Would this all be solved if I just use READ COMMITTED transaction isolation? maybe? If the problem is really just exactly those rows needing to be visible. But the long running dormant transaction thing is still kind of an antipattern that will generally have negative effects. Well... I do realize that, but unfortunately it's an application with many bad design decisions, but it has to work somehow until we can afford fixing everything. Thanks a lot! --- Pedro Werneck -- 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] Refresh session: rollback() or commit()?
Well... the solution really is to use Flask context... Instead of: def receive_some_request(args): session = Session(some_engine) # connect to the database (in reality, pulls a connection from a pool as soon as the Session is used to emit SQL) try: .. do things with session ... session.commit()# if you have data to commit finally: session.close() # close what was opened above. It just needs: def receive_some_request(args): with app.app_context() .. do things with session ... And it manages the session on the background, like it does for a web request. Problem solved. Thanks a lot for the help. On Sat, Jan 26, 2013 at 11:16 AM, Pedro Werneck pjwern...@gmail.com wrote: hmm, is that because your model objects themselves are controlling the scope of the transaction ?That's another pattern I don't really recommend... As I mentioned, I'm using Flask-SQLAlchemy, where I have a global db instance of the SQLAlchemy class, which holds the engine, current session, session factory, etc. All models have high-level methods for save, delete, revert, etc, which use the global db.session. This isn't an issue for the web part of the application, because Flask creates a new session for each request context, but the workers are outside the request context and the tasks use the db.session directly, either through the models or by themselves. The session created by Flask on the request is a subclass of the scoped session with some signal handling extras. So, your solution works, but to implement that without changing everything, I have to replicate whatever the high-level methods do using that local session created when the task is called. I think it might be possible to create a request context on each task call, so everyone will have a fresh session on the global db.session, as if it were a web request, but I'll have to go into Flask internals to figure how to do that. Would this all be solved if I just use READ COMMITTED transaction isolation? maybe? If the problem is really just exactly those rows needing to be visible. But the long running dormant transaction thing is still kind of an antipattern that will generally have negative effects. Well... I do realize that, but unfortunately it's an application with many bad design decisions, but it has to work somehow until we can afford fixing everything. Thanks a lot! --- Pedro Werneck -- --- Pedro Werneck -- 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. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Refresh session: rollback() or commit()?
I'm having a problem with many concurrent scripts, workers and uwsgi instances writing and reading the same tables and rows almost simultaneously, and sometimes one of them seems to get an older state, even from an object it never touched in the first place and I'm querying for the first time. I find that weird, but I assume it has to do with the database isolation level. The problem is, how to adequately deal with that and make sure it never happens? I added a session.commit() before doing anything and it works, I assume rollback would work too. Is there any better solution? -- 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. 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] Refresh session: rollback() or commit()?
Well... I'm afraid it's not as simple as that. I'll give an example: I have a webservice A, which triggers a callback and calls webservice B, creating a new row in the database with status = 0 and commiting the transaction. Then I have a script which finds all rows with status = 0, and sends their id, one by one, to a worker, who is supposed to get lots of data from many sources and then send that to another webservice C. Now, sometimes, especially when things happen too fast, the query the worker does for the row with that id returns empty, even though that isn't in an uncommited transaction, and the script who called the worker itself found it. In principle, if things are running smoothly, that isn't supposed to happen. Get the problem? The worker doesn't have uncommitted changes, actually it never does any changes at all. It got the id from a script who got the row, so it exists for someone who just started a new session. So, how can I be sure the worker will see that new row? I'm doing a commit with the empty transaction the worker has, as soon as it's called, and it seems to be working, but is there any better way? On Fri, Jan 25, 2013 at 7:42 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 25, 2013, at 4:33 PM, Pedro Werneck wrote: I'm having a problem with many concurrent scripts, workers and uwsgi instances writing and reading the same tables and rows almost simultaneously, and sometimes one of them seems to get an older state, even from an object it never touched in the first place and I'm querying for the first time. I find that weird, but I assume it has to do with the database isolation level. sure, if the updates to that row are still pending in an uncommitted transaction, the outside world would still see the old data. The problem is, how to adequately deal with that and make sure it never happens? I added a session.commit() before doing anything and it works, I assume rollback would work too. Is there any better solution? You should be committing *after* you've done some work. Then when a new request comes in, it should start out with a brand new Session which will make a new database connection as soon as the database is accessed. When the request completes, the Session should be closed out. The documentation at http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questionsdiscusses this, and continues the discussion at http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications. -- 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. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- --- Pedro Werneck -- 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. 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] Refresh session: rollback() or commit()?
That works, but now I'll have to change how my models use the session. Would this all be solved if I just use READ COMMITTED transaction isolation? On Fri, Jan 25, 2013 at 8:45 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 25, 2013, at 5:35 PM, Pedro Werneck wrote: If the script that is searching for status=0 is finding rows that are committed, then the worker that is querying for those rows should be able to see them, unless the worker has been holding open a long running transaction. Exactly. Long running transactions here are more of the antipattern. The worker should ensure it responds to new messages from the status=0 script with a brand new transaction to read the message in question. That's the point. What's the best way to do that, considering the worker is never updating anything, only reading? Should I commit in the end of every task then, even without anything to commit? Should I start a new session on every call? The commit does that automatically if I'm not using autocommit=True, right? just do it like this: def receive_some_request(args): session = Session(some_engine) # connect to the database (in reality, pulls a connection from a pool as soon as the Session is used to emit SQL) try: .. do things with session ... session.commit()# if you have data to commit finally: session.close() # close what was opened above. just like it were a plain database connection. that's per request received by your worker. The worker should wait for a request from the script in a non-transactional state, without a Session. A request from the script comes in- the worker starts a new Session to respond to that request, hence new transaction. Thinking about transaction demarcation in reverse still seems to suggest that this worker is leaving a dormant connection open as it waits for new jobs. I'm pretty sure it does. I'm using Flask SQLAlchemy and Celery for the workers. The workers reach the global app for the session and are keeping the connection open, but they do have work almost all the time and never sleep for more than a few secs. --- Pedro Werneck -- 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. -- 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. -- --- Pedro Werneck -- 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. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
