[sqlalchemy] Re: Automatically filtering all queries
On 26 май, 20:50, Michael Bayer mike...@zzzcomputing.com wrote: However, its quite easy to achieve. Just use this. class LimitingQuery(Query): def get(self, ident): return Query.get(self.populate_existing(), ident) def __iter__(self): return Query.__iter__(self.private()) @_generative() def private(self): crit = (self._entities[0].mapper.class_.public == True) if self._criterion: self._criterion = crit else: self._criterion = crit full test case attached. Thanks a lot! But the test doesn't cover all use-cases. Here is one that fails: count1 = sess.query(Address).count() count2 = len(sess.query(Address).all()) assert count1==count2, '%d!=%d' % (count1, count2) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Automatically filtering all queries
On 27 май, 18:22, Michael Bayer mike...@zzzcomputing.com wrote: On May 27, 2009, at 4:25 AM, Denis S. Otkidach wrote: class LimitingQuery(Query): def get(self, ident): return Query.get(self.populate_existing(), ident) def __iter__(self): return Query.__iter__(self.private()) @_generative() def private(self): crit = (self._entities[0].mapper.class_.public == True) if self._criterion: self._criterion = crit else: self._criterion = crit full test case attached. Thanks a lot! But the test doesn't cover all use-cases. Here is one that fails: count1 = sess.query(Address).count() count2 = len(sess.query(Address).all()) assert count1==count2, '%d!=%d' % (count1, count2) you'd have to hack .count() as well in a similar fashion. Probably I have to hack something to insure proper subqueries construction. I believe this is wrong way. Hacked .get() and .from_statement() method guarantee that if missed something I'll get an exception, while in this case I have to come over hard to detect bug first (and even seeing the bug doesn't point me to yet another method I have to fix). I'll try to use my query with get() and from_statement() hacked and fallback to filtering everything manually if it won't work. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Automatically filtering all queries
On 26 май, 18:24, Michael Bayer mike...@zzzcomputing.com wrote: Denis S. Otkidach wrote: such ability in SQLAlchemy. There is a suggestion ( http://groups.google.com/group/sqlalchemy/browse_thread/thread/bcd10e... ) to provide custom query_cls. This probably worked a year ago, but doesn't work now. that should absolutely work now. doesn't work is never a very useful explanation of a problem. Sure, my report is not verbose enough. Here is a part of traceback when my QueryPublic is used: [... here is code equivalent to: for link in doc.links: link.ref_doc.events_sections[0].slug # - failes here ...] File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/attributes.py, line 158, in __get__ return self.impl.get(instance_state(instance), instance_dict (instance)) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/attributes.py, line 374, in get value = callable_() File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/strategies.py, line 559, in __call__ return q.get(ident) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 442, in get return self._get(key, ident) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 1396, in _get q.__no_criterion_condition(get) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 271, in __no_criterion_condition raise sa_exc.InvalidRequestError(Query.%s() being called on a Query with existing criterion. % meth) InvalidRequestError: Query.get() being called on a Query with existing criterion. And when I redefine get() it comes a bit farther: [... here is code equivalent to: for link in doc.links: link.ref_doc.events_sections[0].slug link.ref_doc.short_title # - failes here ...] File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/attributes.py, line 158, in __get__ return self.impl.get(instance_state(instance), instance_dict (instance)) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/attributes.py, line 374, in get value = callable_() File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/state.py, line 171, in __call__ attr.impl.key in unmodified File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/mapper.py, line 1834, in _load_scalar_attributes result = session.query(mapper).from_statement(statement)._get (None, only_load_props=attribute_names, refresh_state=state) File string, line 1, in lambda File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 51, in generate assertion(self, fn.func_name) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 281, in __no_clauseelement_condition self.__no_criterion_condition(meth) File /home/web/third-party/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ orm/query.py, line 271, in __no_criterion_condition raise sa_exc.InvalidRequestError(Query.%s() being called on a Query with existing criterion. % meth) InvalidRequestError: Query.from_statement() being called on a Query with existing criterion. Condition is: class Doc([...base declarative model...]): [...] state = Column(Integer, nullable=False, default=0, index=True) [...] PUBLISHED = 4 public_condition = (state==PUBLISHED) Here doc (retrieved from session by id) and doc_ref (via 2 relations) are instances of Doc class. Other models here don't have public_condition yet. Here is query class I pass as query_cls argument to sessionmaker: def QueryPublic(entities, session=None): query = Query(entities, session) # XXX I haven't ever seen examples with several entities, so I can test # this case. assert len(entities)==1, entities # XXX Sometimes it's not a model class or mapper, so the following fails. cls = _class_to_mapper(entities[0]).class_ public_condition = getattr(cls, 'public_condition', None) if public_condition is not None: query = query.filter(public_condition) return query This works for simple queries, but any access to relation gives an about Query.get() used for query with condition (this wasn't a case for 0.4.x). get() should never be called when there's any filtering applied. its a nonsensical operation so 0.5 likes to tell you about it. I don't call it explicitly, but it is called internally when I access property defined with relation(). OK, i've redefined get() method to be less restrictive in cost of performance: class HackedQuery(Query): def get(self, ident): # XXX This works for case when primary key is constructed of id field # only. if isinstance(ident, (tuple, list)): assert len(ident)==1 ident = ident[0] return self.filter_by(id=ident).first() This helped for some
[sqlalchemy] Inheritance and binds to several engines
I use declarative to define database scheme, and binds parameter to session constructed from several metadata tables lists. And I have a problem with inherited models, where table is represented as Join object: get_bind() method doesn't find an engine. A quick-n-dirty solution I use is: class Session(orm.session.Session): def get_bind(self, mapper, clause=None): from sqlalchemy.orm.util import _class_to_mapper if mapper is not None and clause is None: c_mapper = _class_to_mapper(mapper) if hasattr(c_mapper, 'mapped_table'): clause = mapper.mapped_table return orm.session.Session.get_bind(self, mapper, clause) Is it a bug in SQLAlchemy or I use it inapropriately? What is correct 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 For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Python 2.6 hash behavior change
On Mon, Mar 3, 2008 at 8:23 PM, Michael Bayer [EMAIL PROTECTED] wrote:
We define __eq__() all over the place so that would be a lot of
__hash__() methods to add, all of which return id(self). I wonder if
we shouldn't just make a util.Mixin called Hashable so that we can
centralize the idea.
Are you sure this is a correct way? Below is an example demonstrating
the problem with it:
class C(object):
... def __init__(self, value):
... self._value = value
... def __eq__(self, other):
... return self._value==other._value
... def __hash__(self):
... return id(self)
...
c1 = C(1)
c2 = C(1)
c1==c2
True
d = {c1: None}
c1 in d
True
c2 in d
False
I.e. although c2 is equal to c1 and thus should be found in
dictionary, it is not. The defined __hash__ method must return equal
numbers for equal object.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Jan 15, 2008 6:54 PM, Michael Bayer [EMAIL PROTECTED] wrote: The last commit fails with: sqlalchemy.exceptions.IntegrityError: (IntegrityError) Referers.objectId may not be NULL u'UPDATE Referers SET objectId=? WHERE Referers.id = ?' [None, 1] right thats because the instance doesnt exist yet. its better for you to just use the straight ahead query.get(), if None then save() approach. Do you mean obj2 (id of which should go to objectId column in this UPDATE)? If so, why it doesn't exist? It's saved (updated in fact) just several lines above, so it must be both in DB and in session. The last lines of the original test case for convenience: [...] obj2 = replace(session, ModelObject(1, u'title2')) session.commit() ref2 = ModelReferer(1, obj2) replace(session, ref2) session.commit() --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Jan 11, 2008 8:41 PM, Michael Bayer [EMAIL PROTECTED] wrote:
what that looks like to me is that you're attempting to query the
database for object ID #1 using merge().
when you merge(), its going to treat the object similarly to how it
does using session.save_or_update(). that is, it looks for an
_instance_key attribute to determine if the object represents a
transient or persisted instance.
So you could hack the way youre doing it like:
obj2 = ModelObject(1, u'title2')
obj2._instance_key = session.identity_key(instance=obj2)
session.merge(obj2)
session.commit()
we have yet to define a completely public API for the above operation,
i.e. treat this object as though its persistent. im not sure yet
how we could define one that has a straightforward use case which
wouldn't add confusion.
Sometimes this doesn't work:
---8---
import sqlalchemy as sa, logging
from sqlalchemy.orm import mapper, sessionmaker, relation
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig()
class ModelObject(object):
def __init__(self, id, title):
self.id = id
self.title = title
class ModelReferer(object):
def __init__(self, id, object):
self.id = id
self.object = object
metadata = sa.MetaData()
objectsTable = sa.Table(
'Objects', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('title', sa.String(255), nullable=False),
)
referersTable = sa.Table(
'Referers', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('objectId', sa.Integer, sa.ForeignKey('Objects.id'),
nullable=False),
)
objectsMapper = mapper(ModelObject, objectsTable)
referersMapper = mapper(ModelReferer, referersTable,
properties={'object': relation(ModelObject)})
engine = sa.create_engine('sqlite://')
metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine)()
def replace(session, obj):
identityKey = session.identity_key(instance=obj)
oldObj = session.get(*identityKey[:2])
if oldObj is None:
session.save(obj)
return obj
else:
obj._instance_key = identityKey
return session.merge(obj)
obj1 = ModelObject(1, u'title1')
replace(session, obj1)
ref1 = ModelReferer(1, obj1)
replace(session, ref1)
session.commit()
session.clear()
# Another program. We have to insure that object with id=1 exists in DB and has
# certain properties.
obj2 = replace(session, ModelObject(1, u'title2'))
session.commit()
ref2 = ModelReferer(1, obj2)
replace(session, ref2)
session.commit()
---8---
The last commit fails with:
sqlalchemy.exceptions.IntegrityError: (IntegrityError)
Referers.objectId may not be NULL u'UPDATE Referers SET objectId=?
WHERE Referers.id = ?' [None, 1]
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: doubly-linked list
On Jan 11, 2008 7:57 PM, Jonathan LaCour [EMAIL PROTECTED] wrote:
Jonathan LaCour wrote:
I am attempting to model a doubly-linked list, as follows:
... seems to do the trick. I had tried using backref's earlier,
but it was failing because I was specifying a remote_side
keyword argument to the backref(), which was making it blow up
with cycle detection exceptions for some reason.
Oops, spoke too soon! Here is a test case which shows something
quite odd. I create some elements, link them together, and then
walk the relations forward and backward, printing out the results.
All seems fine. Then, I update the order of the linked list, and
print them out forward, and they work okay, but when I print things
out in reverse order, its all screwy.
Any ideas?
I believe you need either the only next_task_id or the only
previous_task_id, but not both, since one can be calculated from
another. Something like the following:
task_table = Table('task', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode),
Column('next_task_id', Integer, unique=True, ForeignKey('task.id')),
)
Session.mapper(Task, task_table, properties={
'next_task' : relation(
Task,
uselist=False,
remote_side=task_table.c.id,
backref=backref('previous_task', uselist=False),
),
})
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Dec 28, 2007 6:25 PM, Michael Bayer [EMAIL PROTECTED] wrote:
On Dec 28, 2007, at 5:50 AM, Denis S. Otkidach wrote:
Sure, I can get an object from DB and copy data from new one. But
there is a lot of object types, so have to invent yet another meta
description for it (while it already exists in sqlalchemy). And
requirements changes often, so I have to change scheme in 2 places.
This is not good and error prone. Why I have to invent new description
when there is already one from sqlalchemy mapping? Can't I use it for
my purpose? Something like merge(objFromDB, newObj) will solve the
problem.
session.merge() does copy the attributes of one object into another.
theres some bugs with dont_load that have been fixed in trunk so try
out the trunk if you have problems.
This doesn't work: I have the same IntegrityError or FlushError
depending on whether original object exists in the session (line
session.clear() is commented in the code below). What I do wrong?
---8---
import sqlalchemy as sa, logging
from sqlalchemy.orm import mapper, sessionmaker
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig()
class ModelObject(object):
def __init__(self, id, title):
self.id = id
self.title = title
metadata = sa.MetaData()
objectTable = sa.Table(
'Objects', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('title', sa.String(255), nullable=False),
)
objectsMapper = mapper(ModelObject, objectTable)
engine = sa.create_engine('sqlite://')
metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine)()
obj1 = ModelObject(1, u'title1')
session.save(obj1)
session.commit()
session.clear()
# Another program. We have to insure that object with id=1 exists in DB and has
# certain properties.
obj2 = ModelObject(1, u'title2')
session.merge(obj2)
session.commit()
---8---
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Dec 28, 2007 1:00 AM, Rick Morrison [EMAIL PROTECTED] wrote: Here's the idiom that should work: def ensure_object(sess, id): o = sess.Query(ModelObject).get(id)# if found, o is now loaded into session if not o: o = ModelObject(1, u'title') sess.save(o) sess.flush() return o This is not what I need: in your example object is not updated with new data. Let me describe the problem. There is a data, that must exist in DB. I have a setup script that gets such data from other source as model objects and pushes it to DB. No problems to run this script ones. But a life changes and the new must-have data appear. No problems to run it several times if pushed data can't be changed. Unfortunately they can be changed, so I have to replace it. I can't delete them before inserting since there are foreign key references. Sure, I can get an object from DB and copy data from new one. But there is a lot of object types, so have to invent yet another meta description for it (while it already exists in sqlalchemy). And requirements changes often, so I have to change scheme in 2 places. This is not good and error prone. Why I have to invent new description when there is already one from sqlalchemy mapping? Can't I use it for my purpose? Something like merge(objFromDB, newObj) will solve the problem. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Dec 26, 2007 10:38 PM, Michael Bayer [EMAIL PROTECTED] wrote:
if you have an instance which you are unsure if it already exists, you
can add it to a session using session.save_or_update(instance). The
decision between INSERT and UPDATE is ultimately decided by the
presence of an attribute on the instance called _instance_key.
I'd like mapper to use UPDATE for newly constructed object (i.e.
object without _instance_key attribute) when a record with the same
primary key already exists in DB.
In most cases, this attribute is not something you need to worry about;
if an instance has been flushed or loaded from a session, it will have
the attribute, or if you've just constructed it and not yet persisted
it, the attribute will not be there. If you think you need to
manually manipulate this attribute, perhaps you can describe your
specific use case so that we can recommend the best way to accomplish
it.
OK, below is a use/test case:
---8---
import sqlalchemy as sa, logging
from sqlalchemy.orm import mapper, sessionmaker
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig()
class ModelObject(object):
def __init__(self, id, title):
self.id = id
self.title = title
metadata = sa.MetaData()
objectTable = sa.Table(
'Objects', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('title', sa.String(255), nullable=False),
)
objectsMapper = mapper(ModelObject, objectTable)
engine = sa.create_engine('sqlite://')
metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine)()
obj = ModelObject(1, u'title')
session.save(obj)
session.commit()
session.clear()
# Another program. We have to insure that object with id=1 exists in DB and has
# certain properties.
obj = ModelObject(1, u'title')
session.save_or_update(obj)
session.commit()
---8---
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Objects are stored in DB when nobody asks to do so
The following code fails on the last assert statement (SQLAlchemy
0.4.1):
---8---
from __future__ import with_statement
import sqlalchemy as sa, logging
from sqlalchemy.orm import mapper, sessionmaker
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig()
class ModelObject(object):
def __init__(self, title):
self.title = title
metadata = sa.MetaData()
objectTable = sa.Table(
'Objects', metadata,
sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
sa.Column('title', sa.String(255), nullable=False),
)
objectsMapper = mapper(ModelObject, objectTable)
engine = sa.create_engine('sqlite://')
metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine, autoflush=False,
transactional=False)()
obj1 = ModelObject(u'title-1.1')
with session.begin():
session.save(obj1)
# No session.flush() here. Is it OK? At least we did something to
save it.
obj1ID = obj1.id
session.clear()
obj1 = session.get(objectsMapper, obj1ID)
assert obj1.title==u'title-1.1'
obj1.title = u'title-1.2' # It's not intended to be saved
obj2 = ModelObject(u'title-2')
with session.begin():
session.save(obj2)
obj2ID = obj2.id
session.clear()
obj2 = session.get(objectsMapper, obj2ID)
assert obj2.title==u'title-2'
obj1 = session.get(objectsMapper, obj1ID)
assert not session.autoflush
assert obj1.title==u'title-1.1' # But we didn't save/flush it!
---8---
All modified objects are saved for each transaction and I see no way
to control this. Am I right? There is a lot of cases when such
behavior in unacceptable. Is it intended or a bug?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Objects are stored in DB when nobody asks to do so
On Dec 26, 2007 6:29 PM, Michael Bayer [EMAIL PROTECTED] wrote: yet another scenario, you want to use transactions that are independent of session flushes. To accomplish this, use engine- or connection-level transactions, as described in the second half of http://www.sqlalchemy.org/docs/04/session.html#unitofwork_sql . in this case you control the transactions independently of any session behavior, yet the session still participates (at your choosing) in the overall transaction. Hope this helps. Sure, this is what I needed. Thanks! I believe SA documentation should explicitly state that session.commit() always flushes all modified objects independent on autoflush option. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
