Re: [sqlalchemy] Memory leaks in DecimalResultProcessor
On Nov 19, 2010, at 11:19 PM, Taavi Burns wrote: > We recently ported our app to 0.6.4, and enabled cextensions. Our > app's memory usage ballooned, and HTTP response times with it due to > the extra load on the garbage collector. Turns out the > DecimalResultProcessor cextension code is a bit leaky! :) > > I've opened ticket http://www.sqlalchemy.org/trac/ticket/1978 > including a patch that plugs those leaks and includes tests to verify > that they're plugged. The changeset is also available at > https://bitbucket.org/taavi_burns/sqlalchemy/changeset/c90e0cfdf9cc > for review. > > I was considering marking the new tests with > @testing.requires.cextensions since nobody should expect the Python > version to leak like this. Thoughts? oh i like that test, yeah just run the decimal processor 50 times, sure. Yeah you can mark that with @requires.cextensions and you can push that to tip on hg.sqlalchemy.org, thanks ! > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Memory leaks in DecimalResultProcessor
We recently ported our app to 0.6.4, and enabled cextensions. Our app's memory usage ballooned, and HTTP response times with it due to the extra load on the garbage collector. Turns out the DecimalResultProcessor cextension code is a bit leaky! :) I've opened ticket http://www.sqlalchemy.org/trac/ticket/1978 including a patch that plugs those leaks and includes tests to verify that they're plugged. The changeset is also available at https://bitbucket.org/taavi_burns/sqlalchemy/changeset/c90e0cfdf9cc for review. I was considering marking the new tests with @testing.requires.cextensions since nobody should expect the Python version to leak like this. Thoughts? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Longer tuple passed to query.get doesn't throw exception
On Nov 19, 2010, at 1:06 PM, Eoghan Murray wrote:
> With a class with 3 primary key columns, e.g.
>
>class MyClass(Entity):
> a = Field(Unicode(64), primary_key=True)
> b = Field(Unicode(64), primary_key=True)
> c = Field(Unicode(64), primary_key=True)
> ...
>
> If you pass 4 values to query.get;
>
>MyClass.get(('a_val', 'b_val', 'c_val', 'd_val'))
>
> it silently ignores 'd_val' and constructs a query with just a, b and
> c columns.
> I don't think this behaviour is desirable! It currently throws an
> exception if a 2-tuple is passed to it.
>
> Fix against trunk attached.
sure thing this is ticket #1977, should be good once tests are added.
>
> In my case I had inadvertently masked the 'd' column:
>
>class MyClass(Entity):
> a = Field(Unicode(64), primary_key=True)
> b = Field(Unicode(64), primary_key=True)
> c = Field(Unicode(64), primary_key=True)
> d = Field(Unicode(64), primary_key=True)
> ...
> d = Field(Unicode(64))
>
> So that MyClass.table.primary_key.columns only had a, b and c. Having
> the MyClass.get only accepting 3-tuples would have caught this error.
> Side issue - this example uses elixir - should elixir have caught the
> redeclaration of 'd'?
There's no way elixir could catch that, Python gives you access to the
just-created class with its final list of attributes. The original "d" is
gone. Unless Elixir kept track of every Field() object created, kept a strong
reference to it, and then raised "hey there's an extra Field here !", seems
pretty intrusive overkill to me...possible recipe for memory leaks too.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Weird error when adding a new object
Got it. I just change session.add(screenGroup) to
session.merge(screenGroup) and it works.
Thank you for you help, it's always really useful.
On Nov 19, 3:10 pm, Michael Bayer wrote:
> The most suspicious thing is self.__dict__['foo'] = list() as well as the
> direct access to self.__dict__['foo']. If "self" is a mapped object, then
> thats your issue.
>
> Also note this is still an out of context cut-and-paste, if you really want
> me to figure it out it has to be an isolated test script.
>
> On Nov 19, 2010, at 2:49 PM, Alvaro Reinoso wrote:
>
> > Ok, This is the code:
>
> > �...@staticmethod
> > def insert(string, mode):
> > """Insert a new item given a string"""
> > screenGroup = ScreenGroup()
> > session = rdb.Session()
>
> > try:
> > if mode == "XML":
> > screenGroup.fromXML(string)
> > elif mode == "JSON":
> > screenGroup.fromJSON(string)
> > if screenGroup.id == None:
> > session.add(screenGroup)
> > else:
> > screenGroup = session.merge(screenGroup)
>
> > session.flush()
> > result = "Successful:" + str(screenGroup.id) + "," +
> > screenGroup.title
> > return result
> > except Exception, e:
> > log.warn("::ScreenGroupManager, insert > The error is "
> > + str(e))
> > return "Error connecting to the database. Please
> > try again
> > later."
>
> > return "Error connecting to the database. Please try again
> > later."
>
> > This is the function where it gets the error. When I already have the
> > item and I merge It works out. However, if it's a new item, breaks.
> > This anothe fromXML function:
>
> > def fromXML(self, data):
> > """Unserializes this class from an XML formatted file or
> > string."""
> > root = ""
> > try:
> > if(data.endswith(".xml")):
> > doc = etree.parse(data)
> > root = doc.getroot()
> > else:
> > root = etree.XML(data)
> > except etree.XMLSyntaxError, e:
> > print e
> > pass
>
> > if not type(root) == type(etree.XML("test")):
> > return
>
> > if root.tag == "screenGroup":
> > for child in root.iterchildren():
> > if child.tag == "screens" or child.tag ==
> > "screenGroups":
> > if child.text:
> > items = child.text.split(",")
> > self.__dict__[child.tag] = list()
> > for itemId in items:
> > if child.tag ==
> > "screens":
> > item =
> > ScreenManager.getScreenById(itemId)
> > else:
> > item =
> > ScreenGroupManager.getGroupScreenById(itemId)
> >
> > self.__dict__[child.tag].append(item)
> > else:
> > self.setAttr(child.tag, child.text)
>
> > ScreenManager.getScreenById(itemId) and
> > ScreenGroupManager.getGroupScreenById(itemId) just get the item from
> > the database (session.query(Screen).get(int(itemId))).
>
> > This is the XML string : Group 10 > title>4,3 > screenGroup>
>
> > Thanks!
>
> > On Nov 18, 7:07 pm, Michael Bayer wrote:
> >> looks fine to me, would need full stack trace + single script file of
> >> runnable, reproducing code, thanks.
>
> >> On Nov 18, 2010, at 6:37 PM, Alvaro Reinoso wrote:
>
> >>> Hello,
>
> >>> When trying to add a new item doing:
>
> >>> session = session.add(mediaGroup)
>
> >>> I got this error:
>
> >>> Module sqlalchemy.orm.attributes:834 in get_collection
> >>> return getattr(user_data, '_sa_adapter')
> >>> class GenericBackrefExtension(interfaces.AttributeExtension):>>
> >>> return getattr(user_data, '_sa_adapter')
>
> >>> "AttributeError: 'list' object has no attribute '_sa_adapter'"
>
> >>> This object's class:
>
> >>> class ScreenGroup(rdb.Model):
> >>> """The ScreenGroup is a class derived from ScreenGroup, it is used to
> >>> control users within a group"""
> >>> rdb.metadata(metadata)
> >>> rdb.tablename("screen_groups")
>
> >>> id = Column("id", Integer, primary_key=True)
> >>> title = Column("title", String(100))
> >>> parents = Column("parents", String(512))
>
> >>> screens = relationship("Screen", secondary=group_screens,
> >>> order_by="Screen.title", backref="screen_g
Re: [sqlalchemy] Longer tuple passed to query.get doesn't throw exception
On Nov 19, 2010, at 1:06 PM, Eoghan Murray wrote:
> With a class with 3 primary key columns, e.g.
>
>class MyClass(Entity):
> a = Field(Unicode(64), primary_key=True)
> b = Field(Unicode(64), primary_key=True)
> c = Field(Unicode(64), primary_key=True)
> ...
>
> If you pass 4 values to query.get;
>
>MyClass.get(('a_val', 'b_val', 'c_val', 'd_val'))
>
> it silently ignores 'd_val' and constructs a query with just a, b and
> c columns.
> I don't think this behaviour is desirable! It currently throws an
> exception if a 2-tuple is passed to it.
>
> Fix against trunk attached.
sure thing this is ticket #1977, should be good once tests are added.
>
> In my case I had inadvertently masked the 'd' column:
>
>class MyClass(Entity):
> a = Field(Unicode(64), primary_key=True)
> b = Field(Unicode(64), primary_key=True)
> c = Field(Unicode(64), primary_key=True)
> d = Field(Unicode(64), primary_key=True)
> ...
> d = Field(Unicode(64))
>
> So that MyClass.table.primary_key.columns only had a, b and c. Having
> the MyClass.get only accepting 3-tuples would have caught this error.
> Side issue - this example uses elixir - should elixir have caught the
> redeclaration of 'd'?
There's no way elixir could catch that, Python gives you access to the
just-created class with its final list of attributes. The original "d" is
gone. Unless Elixir kept track of every Field() object created, kept a strong
reference to it, and then raised "hey there's an extra Field here !", seems
pretty intrusive overkill to me and a recipe for memory leaks.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Assigning to a deferred column
On Nov 19, 2010, at 1:41 PM, Eoghan Murray wrote:
> I guess I can't disable the mutability flag?
not if you're changing the value "inline". If you assign a new value, then you
don't need "mutability".
>
> From reading up on mutability:
> http://www.sqlalchemy.org/docs/core/types.html#sqlalchemy.types.MutableType
> I think I understand why it's required to lookup the previous value;
> so that the orm can decide whether to construct an update query for
> the instance when a flush event occurs (is that correct?).
The ORM uses attribute set events to flag changes.It then uses the
"previous" value, if present, to determine if a change has taken place at flush
time, and if so issues an UPDATE. If "previous" is not present, in most cases
we don't need to load it, and the UPDATE is unconditional.
With a mutable structure where the user expects inline changes to take place,
the task is more complex, since there is no "set" event. We have no choice but
to immediately copy the structure on load so that we can detect this change.
An eventual solution to this would be that we instrument the mutable structure
itself to flag change events on occurrence, but this is a very involved
enhancement.
> Should there be a shortcut for column assignment, so that rather than
> looking up the previous value, the column is flagged as 'mutated'?
If by "column assignment" you mean direct assignment of the attribute, i.e.
myobject.foo = {newvalue}, that is universally considered to be an event. We
take the previous value and store it in the object's state, if we have it. If
we don't have it, in most cases we don't go back to the DB to load it - the
column receives an UPDATE unconditionally in that case.In the case of
mutable, its assumed that we already have it.
> If that was the case you'd get a spurious update if the old_value
> happens to be equal to the new_value, but shouldn't it be up to the
> app programmer to do this check?
> It seems to me that there's an unnecessary lookup of the old value
> when it is being replaced wholesale,
like i said, if we don't have the "old_value", in most cases we don't bother
loading it, the UPDATE is unconditional. If we do have the "old value" we use
it.We are required to load the "old value" on change if the target is a
collection, its a scalar attribute that is part of the object's primary key
(I'll leave it as an exercise why that is), or a many-to-one with a non-simple
join condition or which is using "orphan" detection, otherwise its not loaded.
> which I cannot stop even though
> I've marked the column as deferred.
so here is the magic word, "deferred". mutable + deferred has never been
considered.Ticket #1976 is added to evaluate if we can skip the "load" when
the col is deferred and the value wasn't loaded. Seems to work so far but
needs more tests and this would probably be in 0.7.
Note however that this "SELECT" is nowheres near the central inefficiency of a
mutable type. The central inefficiency is that we need to scan the whole
session on every flush and compare every mutable value to its old one. Your
usage of "deferred" doesn't help that too much since if you are modifying the
item in place, we still have to store it twice and compare on every flush.
"mutable" is not a flag you'd ever see within 100 yards of any of my
applications until we work out a way to instrument the values.
>
> All the best,
>
> Eoghan
>
> On Nov 4, 1:47 pm, Michael Bayer wrote:
>> is this a mutable type like PickleType? the previous value should not be
>> loaded otherwise (assuming at least a recent 0.5 or 0.6 version). If so,
>> the mutability flag should be disabled.
>>
>> On Nov 4, 2010, at 7:05 AM, Eoghan Murray wrote:
>>
>>
>>
>>> I have the following:
>>
>>>objs =
>>> MyTable.query.options(defer(Table.potentially_very_long_str_column)).all()
>>>for obj in objs:
>>> obj.potentially_very_long_str_column = ''
>>
>>> At the moment, I'm seeing the assignment in the loop issue the `SELECT
>>> potentially_very_long_str_column`, even though that column will be
>>> overwritten.
>>
>>> I pdb'd this down to orm/state.py::InstanceState.modified_event()
>>> where the previous value is stored in self.committed_state
>>> I'm ignorant as to what self.committed_state is used for, but maybe
>>> rather than evaluating 'previous' value, it could be further deferred
>>> until it is needed? This is assuming that self.committed_state will
>>> only be used up until the session is flushed (at which time the
>>> previous value would be lost)..
>>
>>> At the moment, is there any other way of writing to a column without
>>> first reading it using the orm?
>>> Haven't checked, but I assume sql.expression.update doesn't have
>>> this behaviour..
>>
>>> Cheers!
>>
>>> Eoghan
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send ema
Re: [sqlalchemy] Re: Weird error when adding a new object
The most suspicious thing is self.__dict__['foo'] = list() as well as the
direct access to self.__dict__['foo']. If "self" is a mapped object, then
thats your issue.
Also note this is still an out of context cut-and-paste, if you really want me
to figure it out it has to be an isolated test script.
On Nov 19, 2010, at 2:49 PM, Alvaro Reinoso wrote:
> Ok, This is the code:
>
> @staticmethod
> def insert(string, mode):
> """Insert a new item given a string"""
> screenGroup = ScreenGroup()
> session = rdb.Session()
>
> try:
> if mode == "XML":
> screenGroup.fromXML(string)
> elif mode == "JSON":
> screenGroup.fromJSON(string)
> if screenGroup.id == None:
> session.add(screenGroup)
> else:
> screenGroup = session.merge(screenGroup)
>
> session.flush()
> result = "Successful:" + str(screenGroup.id) + "," +
> screenGroup.title
> return result
> except Exception, e:
> log.warn("::ScreenGroupManager, insert > The error is "
> + str(e))
> return "Error connecting to the database. Please
> try again
> later."
>
> return "Error connecting to the database. Please try again
> later."
>
> This is the function where it gets the error. When I already have the
> item and I merge It works out. However, if it's a new item, breaks.
> This anothe fromXML function:
>
> def fromXML(self, data):
> """Unserializes this class from an XML formatted file or
> string."""
> root = ""
> try:
> if(data.endswith(".xml")):
> doc = etree.parse(data)
> root = doc.getroot()
> else:
> root = etree.XML(data)
> except etree.XMLSyntaxError, e:
> print e
> pass
>
> if not type(root) == type(etree.XML("test")):
> return
>
> if root.tag == "screenGroup":
> for child in root.iterchildren():
> if child.tag == "screens" or child.tag ==
> "screenGroups":
> if child.text:
> items = child.text.split(",")
> self.__dict__[child.tag] =
> list()
> for itemId in items:
> if child.tag ==
> "screens":
> item =
> ScreenManager.getScreenById(itemId)
> else:
> item =
> ScreenGroupManager.getGroupScreenById(itemId)
>
> self.__dict__[child.tag].append(item)
> else:
> self.setAttr(child.tag, child.text)
>
> ScreenManager.getScreenById(itemId) and
> ScreenGroupManager.getGroupScreenById(itemId) just get the item from
> the database (session.query(Screen).get(int(itemId))).
>
> This is the XML string : Group 10 title>4,3 screenGroup>
>
> Thanks!
>
> On Nov 18, 7:07 pm, Michael Bayer wrote:
>> looks fine to me, would need full stack trace + single script file of
>> runnable, reproducing code, thanks.
>>
>> On Nov 18, 2010, at 6:37 PM, Alvaro Reinoso wrote:
>>
>>> Hello,
>>
>>> When trying to add a new item doing:
>>
>>> session = session.add(mediaGroup)
>>
>>> I got this error:
>>
>>> Module sqlalchemy.orm.attributes:834 in get_collection
>>> return getattr(user_data, '_sa_adapter')
>>> class GenericBackrefExtension(interfaces.AttributeExtension):>>
>>> return getattr(user_data, '_sa_adapter')
>>
>>> "AttributeError: 'list' object has no attribute '_sa_adapter'"
>>
>>> This object's class:
>>
>>> class ScreenGroup(rdb.Model):
>>>"""The ScreenGroup is a class derived from ScreenGroup, it is used to
>>> control users within a group"""
>>>rdb.metadata(metadata)
>>>rdb.tablename("screen_groups")
>>
>>>id = Column("id", Integer, primary_key=True)
>>>title = Column("title", String(100))
>>>parents = Column("parents", String(512))
>>
>>>screens = relationship("Screen", secondary=group_screens,
>>> order_by="Screen.title", backref="screen_groups")
>>>screenGroups = relationship("ScreenGroup",
>>> secondary=screen_group_groups, order_by="ScreenGroup.title",
>>>primaryjoin=lambda: ScreenGroup.id ==
>>> screen_group_groups.c.screen_
[sqlalchemy] Re: Weird error when adding a new object
Ok, This is the code:
@staticmethod
def insert(string, mode):
"""Insert a new item given a string"""
screenGroup = ScreenGroup()
session = rdb.Session()
try:
if mode == "XML":
screenGroup.fromXML(string)
elif mode == "JSON":
screenGroup.fromJSON(string)
if screenGroup.id == None:
session.add(screenGroup)
else:
screenGroup = session.merge(screenGroup)
session.flush()
result = "Successful:" + str(screenGroup.id) + "," +
screenGroup.title
return result
except Exception, e:
log.warn("::ScreenGroupManager, insert > The error is "
+ str(e))
return "Error connecting to the database. Please
try again
later."
return "Error connecting to the database. Please try again
later."
This is the function where it gets the error. When I already have the
item and I merge It works out. However, if it's a new item, breaks.
This anothe fromXML function:
def fromXML(self, data):
"""Unserializes this class from an XML formatted file or
string."""
root = ""
try:
if(data.endswith(".xml")):
doc = etree.parse(data)
root = doc.getroot()
else:
root = etree.XML(data)
except etree.XMLSyntaxError, e:
print e
pass
if not type(root) == type(etree.XML("test")):
return
if root.tag == "screenGroup":
for child in root.iterchildren():
if child.tag == "screens" or child.tag ==
"screenGroups":
if child.text:
items = child.text.split(",")
self.__dict__[child.tag] =
list()
for itemId in items:
if child.tag ==
"screens":
item =
ScreenManager.getScreenById(itemId)
else:
item =
ScreenGroupManager.getGroupScreenById(itemId)
self.__dict__[child.tag].append(item)
else:
self.setAttr(child.tag, child.text)
ScreenManager.getScreenById(itemId) and
ScreenGroupManager.getGroupScreenById(itemId) just get the item from
the database (session.query(Screen).get(int(itemId))).
This is the XML string : Group 104,3
Thanks!
On Nov 18, 7:07 pm, Michael Bayer wrote:
> looks fine to me, would need full stack trace + single script file of
> runnable, reproducing code, thanks.
>
> On Nov 18, 2010, at 6:37 PM, Alvaro Reinoso wrote:
>
> > Hello,
>
> > When trying to add a new item doing:
>
> > session = session.add(mediaGroup)
>
> > I got this error:
>
> > Module sqlalchemy.orm.attributes:834 in get_collection
> > return getattr(user_data, '_sa_adapter')
> > class GenericBackrefExtension(interfaces.AttributeExtension):>>
> > return getattr(user_data, '_sa_adapter')
>
> > "AttributeError: 'list' object has no attribute '_sa_adapter'"
>
> > This object's class:
>
> > class ScreenGroup(rdb.Model):
> > """The ScreenGroup is a class derived from ScreenGroup, it is used to
> > control users within a group"""
> > rdb.metadata(metadata)
> > rdb.tablename("screen_groups")
>
> > id = Column("id", Integer, primary_key=True)
> > title = Column("title", String(100))
> > parents = Column("parents", String(512))
>
> > screens = relationship("Screen", secondary=group_screens,
> > order_by="Screen.title", backref="screen_groups")
> > screenGroups = relationship("ScreenGroup",
> > secondary=screen_group_groups, order_by="ScreenGroup.title",
> > primaryjoin=lambda: ScreenGroup.id ==
> > screen_group_groups.c.screen_groupA_id,
> > secondaryjoin=lambda: ScreenGroup.id ==
> > screen_group_groups.c.screen_groupB_id,
> > backref="screen_groups")
>
> > Thanks in advance!
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more option
[sqlalchemy] Re: Assigning to a deferred column
Thanks for the reply, yes it is a mutable type:
import jsonpickle
class JsonType(types.MutableType, types.TypeDecorator):
impl = types.Unicode
def process_bind_param(self, value, engine):
return unicode(jsonpickle.encode(value))
def process_result_value(self, value, engine):
if value:
return jsonpickle.decode(value)
else:
return {}
I guess I can't disable the mutability flag?
>From reading up on mutability:
>http://www.sqlalchemy.org/docs/core/types.html#sqlalchemy.types.MutableType
I think I understand why it's required to lookup the previous value;
so that the orm can decide whether to construct an update query for
the instance when a flush event occurs (is that correct?).
Should there be a shortcut for column assignment, so that rather than
looking up the previous value, the column is flagged as 'mutated'?
If that was the case you'd get a spurious update if the old_value
happens to be equal to the new_value, but shouldn't it be up to the
app programmer to do this check?
It seems to me that there's an unnecessary lookup of the old value
when it is being replaced wholesale, which I cannot stop even though
I've marked the column as deferred.
All the best,
Eoghan
On Nov 4, 1:47 pm, Michael Bayer wrote:
> is this a mutable type like PickleType? the previous value should not be
> loaded otherwise (assuming at least a recent 0.5 or 0.6 version). If so, the
> mutability flag should be disabled.
>
> On Nov 4, 2010, at 7:05 AM, Eoghan Murray wrote:
>
>
>
> > I have the following:
>
> > objs =
> > MyTable.query.options(defer(Table.potentially_very_long_str_column)).all()
> > for obj in objs:
> > obj.potentially_very_long_str_column = ''
>
> > At the moment, I'm seeing the assignment in the loop issue the `SELECT
> > potentially_very_long_str_column`, even though that column will be
> > overwritten.
>
> > I pdb'd this down to orm/state.py::InstanceState.modified_event()
> > where the previous value is stored in self.committed_state
> > I'm ignorant as to what self.committed_state is used for, but maybe
> > rather than evaluating 'previous' value, it could be further deferred
> > until it is needed? This is assuming that self.committed_state will
> > only be used up until the session is flushed (at which time the
> > previous value would be lost)..
>
> > At the moment, is there any other way of writing to a column without
> > first reading it using the orm?
> > Haven't checked, but I assume sql.expression.update doesn't have
> > this behaviour..
>
> > Cheers!
>
> > Eoghan
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Longer tuple passed to query.get doesn't throw exception
With a class with 3 primary key columns, e.g.
class MyClass(Entity):
a = Field(Unicode(64), primary_key=True)
b = Field(Unicode(64), primary_key=True)
c = Field(Unicode(64), primary_key=True)
...
If you pass 4 values to query.get;
MyClass.get(('a_val', 'b_val', 'c_val', 'd_val'))
it silently ignores 'd_val' and constructs a query with just a, b and
c columns.
I don't think this behaviour is desirable! It currently throws an
exception if a 2-tuple is passed to it.
Fix against trunk attached.
In my case I had inadvertently masked the 'd' column:
class MyClass(Entity):
a = Field(Unicode(64), primary_key=True)
b = Field(Unicode(64), primary_key=True)
c = Field(Unicode(64), primary_key=True)
d = Field(Unicode(64), primary_key=True)
...
d = Field(Unicode(64))
So that MyClass.table.primary_key.columns only had a, b and c. Having
the MyClass.get only accepting 3-tuples would have caught this error.
Side issue - this example uses elixir - should elixir have caught the
redeclaration of 'd'?
Cheers!
Eoghan
diff -r 0d9a1a57caac lib/sqlalchemy/orm/query.py
--- a/lib/sqlalchemy/orm/query.py Thu Nov 18 20:12:24 2010 -0500
+++ b/lib/sqlalchemy/orm/query.py Fri Nov 19 18:04:59 2010 +
@@ -1919,6 +1919,12 @@
q = self._clone()
if ident is not None:
+if len(ident) != len(mapper.primary_key):
+raise sa_exc.InvalidRequestError(
+"Incorrect number of values in identifier to
formulate "
+"primary key for query.get(); primary key columns are
%s" %
+','.join("'%s'" % c for c in
mapper.primary_key))
+
(_get_clause, _get_params) = mapper._get_clause
# None present in ident - turn those comparisons
@@ -1939,12 +1945,6 @@
for id_val, primary_key in zip(ident,
mapper.primary_key)
])
-if len(params) != len(mapper.primary_key):
-raise sa_exc.InvalidRequestError(
-"Incorrect number of values in identifier to
formulate "
-"primary key for query.get(); primary key columns are
%s" %
-','.join("'%s'" % c for c in mapper.primary_key))
-
q._params = params
if lockmode is not None:
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Mapping via multiple joined tables
On Nov 19, 2010, at 6:37 AM, Mene wrote:
> Hi,
>
> I want an voting application, for a huge building. Each user has one
> room at each floor of the building. The room is shared with other
> users and may change over time. Each user has one vote for each room
> he is in (so also one per level) to vote for things like the color the
> room shall be painted in, his favorite canvas, preferred music etc.
> The number of rooms and the number of users is expected to be giant,
> but the number of votes is probably relatively low, also in the future
> there will be other voting options.
This schema doesn't illustrate the association of a user/photo combination with
a particular room. I see table names "tile_user" and "photo" which are not
illustrated here. So I can't answer how user.photo_votes[room_name] would be
accessed. Also there's some terminology breakdown here, are you using the
term "photo" to describe things like "the color the room shall be painted in",
"his favorite canvas" ?
I don't see the need for room_user.room_user_id as far as allowing particular
mutations - natural primary keys don't prohibit anything as long as you have ON
UPDATE CASCADE available. If the reason is that you just want surrogate
primary keys, then you should have one for "room" as well otherwise it's
inconsistent.
Its fine to have tables which associate users with all their possible rooms,
and all possible photos with a room. Storing "user x voted for photo y in room
z" would need a "fact" table, however, along the lines of:
user_votes = Table('user_votes', metadata,
Column('user_id', Integer, ForeignKey('user.id', ondelete='CASCADE'),
primary_key=True),
Column('photo_id', Integer, ForeignKey('photo.id',ondelete='CASCADE'),
primary_key=True)
Column('room_name', Unicode(8), ForeignKey('room.room_name',
onupdate="CASCADE"), primary_key=True)
)
>
>
> I want the following database layout
>
> // Simple user table
> user_table = Table('user', metadata,
>Column('user_id', Integer, primary_key=True)
> )
>
> // Simple room table
> room_table = Table('room', metadata,
>Column('room_name', Unicode(8), primary_key=True)
> )
>
> // Table associating a user with a room.
> // The surrogate key is needed, because users may move to other rooms
> and I want the database to be
> // referential integer
> room_user_table = Table('room_user', metadata,
>Column('room_user_id', Integer, primary_key=True),
>Column('room_name', Unicode(8), ForeignKey('room.room_name')),
>Column('user_id', Integer, ForeignKey('user.user_id',
> ondelete='CASCADE'))
> )
>
> // Defines which photos are available for this room
> room_photo_table = Table('room_photo', metadata,
>Column('room_name', Unicode(8), ForeignKey('room.room_name'),
> primary_key=True),
>Column('photo', Integer, ForeignKey('photo.id',
> ondelete='CASCADE'), primary_key=True)
> )
>
> // Stores the votes of each user
> photo_vote_table = Table('photo_vote', metadata,
>Column('tile_user', Integer, ForeignKey('tile_user.id',
> ondelete='CASCADE'), primary_key=True),
>Column('photo_id', Integer, ForeignKey('photo.id',
> ondelete='CASCADE'))
> )
>
>
> I need to access the votes from python like this:
> user.photo_votes[room_name] = photo
> This should work for read and write access.
>
> I'd get it working if I'd drop the photo_vote_table and store the
> photo_id in the room_user_table, however, this would lead to a very
> sparse database and I'll be in need to add more and more fields to
> room_user_table, as more voting options become available.
>
> The mapping for this looked like:
>
> user_mapper = mapper(User, user_table,
>'_photos_votes_dict':relation(RoomUser, lazy=True, cascade="all,
> delete" \
> ,
> collection_class=column_mapped_collection(room_user_table.c.room_name)
> )
> })
>
> class User(object):
>photos_votes = association_proxy('_photos_votes_dict', 'photo')
>
> (the rest is trivial)
>
> I hope I mentioned everything and you understood me ;D
>
> Greetings and thanks for your time,
> Mene
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Mapping via multiple joined tables
Hi,
I want an voting application, for a huge building. Each user has one
room at each floor of the building. The room is shared with other
users and may change over time. Each user has one vote for each room
he is in (so also one per level) to vote for things like the color the
room shall be painted in, his favorite canvas, preferred music etc.
The number of rooms and the number of users is expected to be giant,
but the number of votes is probably relatively low, also in the future
there will be other voting options.
I want the following database layout
// Simple user table
user_table = Table('user', metadata,
Column('user_id', Integer, primary_key=True)
)
// Simple room table
room_table = Table('room', metadata,
Column('room_name', Unicode(8), primary_key=True)
)
// Table associating a user with a room.
// The surrogate key is needed, because users may move to other rooms
and I want the database to be
// referential integer
room_user_table = Table('room_user', metadata,
Column('room_user_id', Integer, primary_key=True),
Column('room_name', Unicode(8), ForeignKey('room.room_name')),
Column('user_id', Integer, ForeignKey('user.user_id',
ondelete='CASCADE'))
)
// Defines which photos are available for this room
room_photo_table = Table('room_photo', metadata,
Column('room_name', Unicode(8), ForeignKey('room.room_name'),
primary_key=True),
Column('photo', Integer, ForeignKey('photo.id',
ondelete='CASCADE'), primary_key=True)
)
// Stores the votes of each user
photo_vote_table = Table('photo_vote', metadata,
Column('tile_user', Integer, ForeignKey('tile_user.id',
ondelete='CASCADE'), primary_key=True),
Column('photo_id', Integer, ForeignKey('photo.id',
ondelete='CASCADE'))
)
I need to access the votes from python like this:
user.photo_votes[room_name] = photo
This should work for read and write access.
I'd get it working if I'd drop the photo_vote_table and store the
photo_id in the room_user_table, however, this would lead to a very
sparse database and I'll be in need to add more and more fields to
room_user_table, as more voting options become available.
The mapping for this looked like:
user_mapper = mapper(User, user_table,
'_photos_votes_dict':relation(RoomUser, lazy=True, cascade="all,
delete" \
,
collection_class=column_mapped_collection(room_user_table.c.room_name)
)
})
class User(object):
photos_votes = association_proxy('_photos_votes_dict', 'photo')
(the rest is trivial)
I hope I mentioned everything and you understood me ;D
Greetings and thanks for your time,
Mene
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
