[sqlalchemy] unexpected chained relations and append behaviour
Hello everyone, I have a realy simple model for you to consider: 1 car has n wheels car.wheels is a relation from cars to wheels wheel.car is a backref to cars 1 car has n parts car.parts is a relation from car to parts I just wondered why my app was really getting slow, turned on SA debug mode, and saw that my_new_doorknob = model.Part(doorknob) wheel.car.parts.append(my_new_door_knob) is downloading the entire parts table WHERE parts.car == car.id (that is around 20.000 entries) just so that it can append my new doorknob to that relation. Furthermore I noticed a similar behaviour when doing something like this: amount_of_parts = len(car.parts) Instead of sending a COUNT to the database, it populates the entire car.parts relation (around 20.000 entries) just to get the count. Of course I could avoid using relations, and just use my __init__ functions, or setting: my_new_doorknob = model.Part(doorknob) my_new_doorknob.car_id = car.id DBSession.append(my_new_doorknob) But then I could as well just write literal SQL if I cant use the R part of ORM... Has anyone observed similar behaviour or is this a feature and intended to work like this? Greetings, Tom --~--~-~--~~~---~--~~ 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: unexpected chained relations and append behaviour
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Crusty Sent: 23 September 2009 15:48 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] unexpected chained relations and append behaviour Hello everyone, I have a realy simple model for you to consider: 1 car has n wheels car.wheels is a relation from cars to wheels wheel.car is a backref to cars 1 car has n parts car.parts is a relation from car to parts I just wondered why my app was really getting slow, turned on SA debug mode, and saw that my_new_doorknob = model.Part(doorknob) wheel.car.parts.append(my_new_door_knob) is downloading the entire parts table WHERE parts.car == car.id (that is around 20.000 entries) just so that it can append my new doorknob to that relation. Furthermore I noticed a similar behaviour when doing something like this: amount_of_parts = len(car.parts) Instead of sending a COUNT to the database, it populates the entire car.parts relation (around 20.000 entries) just to get the count. Of course I could avoid using relations, and just use my __init__ functions, or setting: my_new_doorknob = model.Part(doorknob) my_new_doorknob.car_id = car.id DBSession.append(my_new_doorknob) But then I could as well just write literal SQL if I cant use the R part of ORM... Has anyone observed similar behaviour or is this a feature and intended to work like this? Greetings, Tom Yes, this is exactly how it is intended to work. You may like to read http://www.sqlalchemy.org/docs/05/mappers.html#working-with-large-collec tions for hints on how to improve performance. In particular, making your car.parts property a 'dynamic' relation rather than the default will prevent SA from loading the entire collection unless you specifically ask it to. However, the len(car.parts) line won't work. SA deliberately doesn't implement the __len__ method for Query objects because it is called implicitly by python in a number of situations, and running a potentially slow query when you aren't expecting it is a bad idea. Instead you would use car.parts.count(). Hope that helps, Simon --~--~-~--~~~---~--~~ 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: Inheritance + Pseudo-adjacency-list?
On Wed, Sep 23, 2009 at 12:27 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On Sep 22, 2009, at 11:59 AM, Kevin H wrote:
I'm having some trouble developing my model, and was hoping someone on
this list could help...
Here's what I want:
A BizEntity object
A Person and Company object (both descended from BizEntity, using
joined table inheritance)
A Company.employees attribute, which points to a list of Persons who
work for the company
A Person.company attribute, which points back to the company that
person works for
Whenever I try to combine inheritance with this sort of pseudo-
adjacency-list, I get really odd things happening when I try to query
from the tables...like getting the wrong company back when I query by
id.
Any ideas out there? Anyone done something like this?
I'm doing this.
Howdy, Michael! Knowing that I'm not trying to do something impossible is
definitely a relief.
The first thing to do is to definitely be on 0.5.6
at the least.
OK, I'm on 0.5.5, so that's the first thing to fix, I guess.
the next thing is to define the employees/company thing only once, as
a relation/backref pair on just one of your mapped classes. doing it
twice will mess things up for sure.
Good to know, thanks.
your example also mentions a table called nodes which from
everything else mentioned below would be erroneous. you don't need
remote_side when mapping between Company and Person.
Wow, that's from something _really_ old. Been commented out for a while...I
didn't even notice that.
None of this would cause the wrong Company to come back from a
simple query by id, though. If that is really the effect you're
seeing then something more fundamental might be amiss.
Looking at it again, it looks like this was caused by a problem in my
tests. I was assuming something I shouldn't have been about the order of
the data I was testing.
Thanks for the pointers, I'll post back later with results.
Kevin Horn
MODEL (so far):
(NOTE: the commented out lines are left over from some of my previous
attempts to get things working.)
class BizEntity(Base):
__tablename__ = 'biz_entities'
id = Column('bizentity_id', Integer, primary_key=True)
type = Column('bizentity_type', String(30), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
class Company(BizEntity):
__tablename__ = 'companies'
id = Column(Integer, ForeignKey('biz_entities.bizentity_id'),
primary_key=True)
name = Column('company_name', String(50))
#~ employees = relation(Person, backref=backref(company,
remote_side=[])
#~ backref('parent', remote_side=[nodes.c.id])
__mapper_args__ = {'polymorphic_identity': 'company'}
class Person(BizEntity):
__tablename__ = 'people'
id = Column('bizentity_id', Integer, ForeignKey
('biz_entities.bizentity_id'), primary_key=True)
first_name = Column('first_name', String(50))
middle_init = Column('middle_init', String(1))
last_name = Column('last_name', String(50))
#~ company = relation(Company, backref=backref('employees',
order_by=id))
__mapper_args__ = {'polymorphic_identity':'person'}
--~--~-~--~~~---~--~~
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: Inheritance + Pseudo-adjacency-list?
On Wed, Sep 23, 2009 at 10:57 AM, Kevin Horn kevin.h...@gmail.com wrote:
On Wed, Sep 23, 2009 at 12:27 AM, Michael Bayer
mike...@zzzcomputing.comwrote:
On Sep 22, 2009, at 11:59 AM, Kevin H wrote:
I'm having some trouble developing my model, and was hoping someone on
this list could help...
Here's what I want:
A BizEntity object
A Person and Company object (both descended from BizEntity, using
joined table inheritance)
A Company.employees attribute, which points to a list of Persons who
work for the company
A Person.company attribute, which points back to the company that
person works for
Whenever I try to combine inheritance with this sort of pseudo-
adjacency-list, I get really odd things happening when I try to query
from the tables...like getting the wrong company back when I query by
id.
Any ideas out there? Anyone done something like this?
I'm doing this.
Howdy, Michael! Knowing that I'm not trying to do something impossible is
definitely a relief.
The first thing to do is to definitely be on 0.5.6
at the least.
OK, I'm on 0.5.5, so that's the first thing to fix, I guess.
the next thing is to define the employees/company thing only once, as
a relation/backref pair on just one of your mapped classes. doing it
twice will mess things up for sure.
Good to know, thanks.
your example also mentions a table called nodes which from
everything else mentioned below would be erroneous. you don't need
remote_side when mapping between Company and Person.
Wow, that's from something _really_ old. Been commented out for a
while...I didn't even notice that.
None of this would cause the wrong Company to come back from a
simple query by id, though. If that is really the effect you're
seeing then something more fundamental might be amiss.
Looking at it again, it looks like this was caused by a problem in my
tests. I was assuming something I shouldn't have been about the order of
the data I was testing.
Thanks for the pointers, I'll post back later with results.
Kevin Horn
Still having problems...
Here's my new model:
# START OF MODEL
Base = declarative_base()
class BizEntity(Base):
__tablename__ = 'biz_entities'
id = Column('bizentity_id', Integer, primary_key=True)
type = Column('bizentity_type', String(30), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
class Company(BizEntity):
__tablename__ = 'companies'
id = Column(Integer, ForeignKey('biz_entities.bizentity_id'),
primary_key=True)
name = Column('company_name', String(50))
__mapper_args__ = {'polymorphic_identity': 'company'}
def __init__(self, company_name):
self.name = company_name
def __repr__(self):
return Company('%s') % (self.name)
class Person(BizEntity):
__tablename__ = 'people'
id = Column('bizentity_id', Integer,
ForeignKey('biz_entities.bizentity_id'), primary_key=True)
first_name = Column('first_name', String(50))
middle_init = Column('middle_init', String(1))
last_name = Column('last_name', String(50))
company = relation(Company, backref=backref('employees', order_by=id))
__mapper_args__ = {'polymorphic_identity':'person'}
def __init__(self, first_name, middle_init, last_name):
self.first_name = first_name
self.middle_init = middle_init
self.last_name = last_name
def __repr__(self):
return Person('%s %s. %s') % (self.first_name, self.middle_init,
self.last_name)
# END OF MODEL
now when I try to use it like this:
# START SAMPLE CODE
comp1 = Company('Test Company')
#~ self.session.add(comp1)
#~ self.session.commit()
joe = Person('Joe', 'Q', 'Public')
joe.company = comp1
self.session.add(joe)
self.session.commit()
# END SAMPLE CODE
I get a traceback like this:
Traceback (most recent call last):
File ta_sa_test.py, line 98, in testCreateCompanyWithEmployees
self.session.commit()
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py,
line 673, in commit
self.transaction.commit()
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py,
line 378, in commit
self._prepare_impl()
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py,
line 362, in _prepare_impl
self.session.flush()
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py,
line 1356, in flush
self._flush(objects)
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py,
line 1434, in _flush
flush_context.execute()
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py,
line 261, in execute
UOWExecutor().execute(self, tasks)
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py,
line 753, in execute
[sqlalchemy] ConFoo.ca call for speakers
Greetings Alchemists, I'm coordinator for the Python track at the ConFoo.ca conference and I have an announcement to make that will certainly be of interest to many of you. PHP-Québec, Montréal-Python, Ruby Montréal, W3Qc, and OWASP Montréal are organizing the first edition of the ConFoo.ca conference, which will be held in Montréal on March 10th through 12th at the Hilton Bonaventure Hotel. With over 500 expected attendees, ConFoo.ca is the largest Web development conference in North America. We are looking for the best speakers willing to share their experience and skills with programmers, managers, marketers and decision makers. The conference is divided into two parts: A technical part, encompassing different aspects of Web development: PHP, Python, Ruby, security, project management, CMSs and frameworks, databases, systems administration, Web standards, accessibility and agile methods. A decision-making part: referencing (SEO), Web marketing analysis, and social networking. Presenters can decide to present in English or French. Presentations are roughly one hour long and these may be recorded for later broadcast in digital format. All relevant details concerning the conference are available on the call for speaker website [1]. Even though Python can be used for a wide range of programming tasks, the Python track at ConFoo.ca will focus on Web development with Python. For all the other fascinating aspects of Python, do not hesitate to submit a talk to PyCon [2], which is also running its call for speakers as I write this. Share the word! [1]: http://confoo.ca/en/cfp [2]: http://us.pycon.org/2010/about/ -- Yannick Gingras http://ygingras.net signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Inheritance + Pseudo-adjacency-list?
You are missing a foreign key column in the people table that
corresponds to your Person-Company relation. As a result, SQLAlchemy
tries to use person.id as the foreign key column (because that column
happens to be a foreign key to a base table of Company) and everything
blows up.
So:
* Add a foreign key column to Person that refers to companies.id.
* Add a primaryjoin argument to your Persion.company relation, because
SQLAlchemy will now see two potential ways to get from Person to
Company (people.id - bizentities.bizentity_id and people.company_id -
companies.id) and refuse to guess which path to take. Try
primaryjoin=lambda: Person.company_id == Company.__table__.c.id. I'm
using Company.__table__.c.id instead of Company.id because Company.id
maps to the bizentities.bizentity_id column and not the
companies.id column.
-Conor
On Sep 23, 11:16 am, Kevin Horn kevin.h...@gmail.com wrote:
On Wed, Sep 23, 2009 at 10:57 AM, Kevin Horn kevin.h...@gmail.com wrote:
On Wed, Sep 23, 2009 at 12:27 AM, Michael Bayer
mike...@zzzcomputing.comwrote:
On Sep 22, 2009, at 11:59 AM, Kevin H wrote:
I'm having some trouble developing my model, and was hoping someone on
this list could help...
Here's what I want:
A BizEntity object
A Person and Company object (both descended from BizEntity, using
joined table inheritance)
A Company.employees attribute, which points to a list of Persons who
work for the company
A Person.company attribute, which points back to the company that
person works for
Whenever I try to combine inheritance with this sort of pseudo-
adjacency-list, I get really odd things happening when I try to query
from the tables...like getting the wrong company back when I query by
id.
Any ideas out there? Anyone done something like this?
I'm doing this.
Howdy, Michael! Knowing that I'm not trying to do something impossible is
definitely a relief.
The first thing to do is to definitely be on 0.5.6
at the least.
OK, I'm on 0.5.5, so that's the first thing to fix, I guess.
the next thing is to define the employees/company thing only once, as
a relation/backref pair on just one of your mapped classes. doing it
twice will mess things up for sure.
Good to know, thanks.
your example also mentions a table called nodes which from
everything else mentioned below would be erroneous. you don't need
remote_side when mapping between Company and Person.
Wow, that's from something _really_ old. Been commented out for a
while...I didn't even notice that.
None of this would cause the wrong Company to come back from a
simple query by id, though. If that is really the effect you're
seeing then something more fundamental might be amiss.
Looking at it again, it looks like this was caused by a problem in my
tests. I was assuming something I shouldn't have been about the order of
the data I was testing.
Thanks for the pointers, I'll post back later with results.
Kevin Horn
Still having problems...
Here's my new model:
# START OF MODEL
Base = declarative_base()
class BizEntity(Base):
__tablename__ = 'biz_entities'
id = Column('bizentity_id', Integer, primary_key=True)
type = Column('bizentity_type', String(30), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
class Company(BizEntity):
__tablename__ = 'companies'
id = Column(Integer, ForeignKey('biz_entities.bizentity_id'),
primary_key=True)
name = Column('company_name', String(50))
__mapper_args__ = {'polymorphic_identity': 'company'}
def __init__(self, company_name):
self.name = company_name
def __repr__(self):
return Company('%s') % (self.name)
class Person(BizEntity):
__tablename__ = 'people'
id = Column('bizentity_id', Integer,
ForeignKey('biz_entities.bizentity_id'), primary_key=True)
first_name = Column('first_name', String(50))
middle_init = Column('middle_init', String(1))
last_name = Column('last_name', String(50))
company = relation(Company, backref=backref('employees', order_by=id))
__mapper_args__ = {'polymorphic_identity':'person'}
def __init__(self, first_name, middle_init, last_name):
self.first_name = first_name
self.middle_init = middle_init
self.last_name = last_name
def __repr__(self):
return Person('%s %s. %s') % (self.first_name, self.middle_init,
self.last_name)
# END OF MODEL
now when I try to use it like this:
# START SAMPLE CODE
comp1 = Company('Test Company')
#~ self.session.add(comp1)
#~ self.session.commit()
joe = Person('Joe', 'Q', 'Public')
joe.company = comp1
self.session.add(joe)
self.session.commit()
# END SAMPLE CODE
I get a traceback like this:
Traceback (most recent call last):
File ta_sa_test.py, line 98, in testCreateCompanyWithEmployees
self.session.commit()
[sqlalchemy] Want a custom implementation of get_bind that ignores metadata bindings
I have a hidden WriterSession which I am using behind the scenes to manage a number of API entries that write data in bulk e.g. upsert (MappedClass, iterator_that_returns_dicts). I want the session to only look at its own binds and to ignore any that are in place on the metadata collection. I wrote my own get_bind that does this (horrible!) hack: if self._Session__binds: b = self._Session__binds if c_mapper: if c_mapper.base_mapper in b: return b[c_mapper.base_mapper] elif c_mapper.mapped_table in b: return b[c_mapper.mapped_table] if self.bind: return self.bind I don't really understand how the double underscore stuff works in Python. Mike, how would you feel about exposing the session bind information with an interface that is more amenable to subclassing? pjjH --~--~-~--~~~---~--~~ 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: Inheritance + Pseudo-adjacency-list?
On Wed, Sep 23, 2009 at 5:34 PM, Conor conor.edward.da...@gmail.com wrote:
You are missing a foreign key column in the people table that
corresponds to your Person-Company relation. As a result, SQLAlchemy
tries to use person.id as the foreign key column (because that column
happens to be a foreign key to a base table of Company) and everything
blows up.
Doh! I knew I was missing something basic.
So:
* Add a foreign key column to Person that refers to companies.id.
* Add a primaryjoin argument to your Persion.company relation, because
SQLAlchemy will now see two potential ways to get from Person to
Company (people.id - bizentities.bizentity_id and people.company_id -
companies.id) and refuse to guess which path to take. Try
primaryjoin=lambda: Person.company_id == Company.__table__.c.id. I'm
using Company.__table__.c.id instead of Company.id because Company.id
maps to the bizentities.bizentity_id column and not the
companies.id column.
-Conor
Ah. I wouldn't have thought of the primaryjoin bit, so it's a good thing
you mentioned it. The traceback you get if you leave it out is a little
intimidating. :)
Thanks for this, Connor! Everything works now.
For the benefit of future searchers, here was the final model:
class BizEntity(Base):
__tablename__ = 'biz_entities'
id = Column('bizentity_id', Integer, primary_key=True)
type = Column('bizentity_type', String(30), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
class Company(BizEntity):
__tablename__ = 'companies'
id = Column(Integer, ForeignKey('biz_entities.bizentity_id'),
primary_key=True)
name = Column('company_name', String(50))
__mapper_args__ = {'polymorphic_identity': 'company'}
def __init__(self, company_name):
self.name = company_name
def __repr__(self):
return Company('%s') % (self.name)
class Person(BizEntity):
__tablename__ = 'people'
id = Column('bizentity_id', Integer,
ForeignKey('biz_entities.bizentity_id'), primary_key=True)
first_name = Column('first_name', String(50))
middle_init = Column('middle_init', String(1))
last_name = Column('last_name', String(50))
company_id = Column(Integer, ForeignKey('companies.id'))
company = relation(Company,
primaryjoin=lambda: Person.company_id ==
Company.__table__.c.id,
backref=backref('employees', order_by=id))
__mapper_args__ = {'polymorphic_identity':'person'}
def __init__(self, first_name, middle_init, last_name):
self.first_name = first_name
self.middle_init = middle_init
self.last_name = last_name
def __repr__(self):
return Person('%s %s. %s') % (self.first_name, self.middle_init,
self.last_name)
Kevin Horn
--~--~-~--~~~---~--~~
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] Retrieving Unicode or utf-8 values
Hi,
I'm having trouble converting an application that uses psycopg2
directly. The Postgres database is encoded in UTF-8. As an example, it
has a column called title with the value 'Wilhelm R\xc3\xb6pke', i.e.,
'Wilhelm Röpke'. With psycopg2, the connection and retrieval is done
more or less as follows:
# -*- coding: utf-8 -*-
from psycopg2 import connect
from psycopg2.extensions import register_type, UNICODE
register_type(UNICODE)
db = connect('host=...')
curs = db.cursor()
curs.execute('select title from tablex')
row = curs.fetchone()
title = row[0]
print(row)
print(title)
The output from this test program is:
(u'Wilhelm R\xf6pke',)
Wilhelm Röpke
In other words, psycopg2 hands the app a Unicode string, which is ideal
because the templating engine (Mako) apparently expects Unicode, even
when told the input_encoding is utf-8. If the register_type() is
removed, a plain string is delivered but that doesn't help Mako.
The comparable SQLAlchemy program is as follows:
# -*- coding: utf-8 -*-
from sqlalchemy.engine import create_engine
engine = create_engine('postgres://...')
result = engine.execute('select title from tablex')
row = result.fetchone()
title = unicode(row.title, encoding='utf-8')
print(row)
print(title)
The output of the SA test program is:
('Wilhelm R\xc3\xb6pke',)
Wilhelm Röpke
In other words, SA hands the app a plain string instead of Unicode, and
if the unicode() conversion is removed, the print(title) (and Mako)
complain with:
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 9:
ordinal not in range(128)
I've tried various combinations of convert_unicode, assert_encoding and
encoding parameters to the create_engine() call and I have not been able
to get Unicode strings from SA, which I find somewhat surprising. Is it
possible, or do I have to filter every string retrieved from the
database through encoding(value, encoding='utf-8)?
For reference, this is on Debian, using Python 2.5.4 and SA 0.5.5.
Joe
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
