[sqlalchemy] Apparently redundant subqueryloads with single table inheritance
I am subquery loading some related properties for a polymorphic inheritance
use case similar to the script attached. SQLA seems to be issuing several
extra queries that I didn't expect and don't think it needs. In the
attached example, I expected a total of 4 queries issued for the
session.query().get(), but I there are 11 queries instead, most of them
redundant.
Any ideas?
Thanks,
Kent
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/vC69eQMhv10J.
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.
# I expected 4 queries total issued for the get() query below, but I get 11 instead.
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///')
metadata = MetaData(engine)
Session = sessionmaker(bind=engine)
class Employee(object):
pass
class Manager(Employee):
pass
class Engineer(Employee):
pass
class SupportTech(Employee):
pass
class Role(object):
pass
employees_table = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('type', String(1), nullable=False),
Column('data', String(50)),
Column('manager_id', Integer, ForeignKey('employees.employee_id')),
)
roles_table = Table('roles', metadata,
Column('employee_id', Integer, ForeignKey('employees.employee_id'), primary_key=True),
Column('role', String(50), primary_key=True),
)
mapper(Role, roles_table)
employee_mapper = mapper(Employee, employees_table,
polymorphic_on=employees_table.c.type,
polymorphic_identity='E',
properties = {
'roles': relationship(Role),
'staff': relationship(Employee,
cascade='save-update,merge,refresh-expire,delete,delete-orphan',
single_parent=True,
backref=backref('manager', remote_side=[employees_table.c.employee_id])),
}
)
manager_mapper = mapper(Manager, inherits=employee_mapper,
polymorphic_identity='M')
engineer_mapper = mapper(Engineer, inherits=employee_mapper,
polymorphic_identity='E')
supporttech_mapper = mapper(SupportTech, inherits=employee_mapper,
polymorphic_identity='S')
session = Session()
metadata.create_all()
try:
m=Manager()
m.employee_id = 1
session.add(m)
session.flush()
e=Engineer()
e.employee_id = 2
e.manager_id = 1
session.add(e)
session.flush()
s=SupportTech()
s.employee_id = 3
s.manager_id = 1
session.add(s)
session.flush()
session = Session()
engine.echo = 'debug'
e = session.query(Employee).options(
subqueryload(Employee.staff),
subqueryload(Employee.roles),
subqueryload(Employee.staff,Employee.roles)).get(1)
finally:
engine.echo = False
session.rollback()
metadata.drop_all()
Re: [sqlalchemy] Apparently redundant subqueryloads with single table inheritance
On Jun 5, 2012, at 4:21 PM, Kent wrote: I am subquery loading some related properties for a polymorphic inheritance use case similar to the script attached. SQLA seems to be issuing several extra queries that I didn't expect and don't think it needs. In the attached example, I expected a total of 4 queries issued for the session.query().get(), but I there are 11 queries instead, most of them redundant. this is ticket 2480 and has been fixed for several weeks. 0.7.8 is due as we have a memory leak issue but i wont have time for a release until perhaps late weekend. Any ideas? Thanks, Kent -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/vC69eQMhv10J. 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. subquery_polymorphic.py -- 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.
Re: [sqlalchemy] Apparently redundant subqueryloads with single table inheritance
Thank you! On 6/5/2012 4:41 PM, Michael Bayer wrote: On Jun 5, 2012, at 4:21 PM, Kent wrote: I am subquery loading some related properties for a polymorphic inheritance use case similar to the script attached. SQLA seems to be issuing several extra queries that I didn't expect and don't think it needs. In the attached example, I expected a total of 4 queries issued for the session.query().get(), but I there are 11 queries instead, most of them redundant. this is ticket 2480 and has been fixed for several weeks. 0.7.8 is due as we have a memory leak issue but i wont have time for a release until perhaps late weekend. Any ideas? Thanks, Kent -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/vC69eQMhv10J. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. subquery_polymorphic.py -- 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. -- 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] Support for hierarchyid of SQL Server 2008?
Hi, I noticed that currently hierarchyid( http://msdn.microsoft.com/en-us/library/bb677290.aspx) of SQL Server 2008 and up is not being supported. Although this type seems kind of out of the place of the general framework of SQLAlchemy, it nevertheless seems to be one potentially efficient way of representing hierarchical data in SQL Server(and SQL Server only...). I am just wondering if there had been discussions on the support of this type and if any conclusions had been made. Thank you for the great package, it'd been an awesome experience! Richard -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/bz5_5ItXsYgJ. 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.
Re: [sqlalchemy] Support for hierarchyid of SQL Server 2008?
This is just a datatype so follow the instructions at http://docs.sqlalchemy.org/en/rel_0_7/core/types.html#creating-new-types to implement your own version of this type.The trick of course is that the DBAPI supports it as well, i.e. pyodbc or pymssql. If not, you may have to petition those projects as well for support. On Jun 5, 2012, at 5:33 PM, RainG wrote: Hi, I noticed that currently hierarchyid(http://msdn.microsoft.com/en-us/library/bb677290.aspx) of SQL Server 2008 and up is not being supported. Although this type seems kind of out of the place of the general framework of SQLAlchemy, it nevertheless seems to be one potentially efficient way of representing hierarchical data in SQL Server(and SQL Server only...). I am just wondering if there had been discussions on the support of this type and if any conclusions had been made. Thank you for the great package, it'd been an awesome experience! Richard -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/bz5_5ItXsYgJ. 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. -- 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] CircularDependencyError with relationships
I have trouble configuring two relationships from one class to another. The
following code should be fairly self-explanatory:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Company(Base):
__tablename__ = 'companies'
id = Column(Integer, primary_key=True)
default_address_id = Column(Integer, ForeignKey('addresses.id',
use_alter=True, name='defaultaddress_fk'))
addresses = relationship('Address', backref='company',
primaryjoin='Address.company_id == Company.id')
default_address = relationship('Address',
primaryjoin='Company.default_address_id == Address.id')
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
company_id = Column(Integer, ForeignKey(Company.id), nullable=False)
engine = create_engine('sqlite:///', echo=True)
Base.metadata.create_all(engine)
session = Session(engine)
company = Company()
address = Address()
session.add(company)
company.default_address = address
company.addresses.append(address)
session.flush()
What I expect is SQLAlchemy to 1) create the company, 2) create the address
with the new company's id in company_id, 3) assign the ID of the new
address to company.default_address_id
Trouble is, I get this error:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected.
Cycles: set([ProcessState(ManyToOneDP(Company.default_address), Company at
0x16a7210, delete=False), ProcessState(ManyToOneDP(Address.company),
Address at 0x16ad190, delete=False), SaveUpdateState(Company at
0x16a7210), ProcessState(OneToManyDP(Company.addresses), Company at
0x16a7210, delete=False), SaveUpdateState(Address at 0x16ad190)]) all
edges: set([(ProcessState(OneToManyDP(Company.addresses), Company at
0x16a7210, delete=False), SaveUpdateState(Address at 0x16ad190)),
(SaveUpdateState(Address at 0x16ad190),
ProcessState(ManyToOneDP(Company.default_address), Company at 0x16a7210,
delete=False)), (SaveUpdateState(Company at 0x16a7210),
SaveUpdateState(Address at 0x16ad190)), (SaveUpdateState(Company at
0x16a7210), ProcessState(ManyToOneDP(Address.company), Address at
0x16ad190, delete=False)),
(ProcessState(ManyToOneDP(Company.default_address), Company at 0x16a7210,
delete=False), SaveUpdateState(Company at 0x16a7210)),
(ProcessState(ManyToOneDP(Address.company), Address at 0x16ad190,
delete=False), SaveUpdateState(Address at 0x16ad190)),
(SaveUpdateState(Company at 0x16a7210),
ProcessState(OneToManyDP(Company.addresses), Company at 0x16a7210,
delete=False))])
What am I doing wrong? I had a similar problem in my production app when
trying to delete a Company that had a default address assigned.
I'm on SQLAlchemy 0.7.7.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/fqFKTLBdTYwJ.
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.
