I'm having issues with certain types of queries in my application that
involve has(), or_(), and composite joins. I've created the most
simple example I could that still produces the error and included it
below. The classes involved in this are fairly contrived, but mirror
my application's real requirements.
I was wondering if someone could take a look and let me know if I've
discovered an issue or attempted something unsupported. I'd also
appreciate suggestions as to simpler ways to accomplish the same
thing, if there are any.
Relevant software versions:
Python 2.7.2+
SQLAlchemy==0.7.5
sqlite 3.7.7 2011-06-23 19:49:22
4374b7e83ea0a3fbc3691f9c0c936272862f32f2
Ubuntu 11.10 (oneiric)
Output on my system:
$ python compile_errors.py
query with or_() causes CompileError. Error was:
Bind parameter '%(3070933068 flag)s' conflicts with unique bind
parameter of the same name
query with union() causes CompileError. Error was:
Bind parameter '%(3070933068 flag)s' conflicts with unique bind
parameter of the same name
Things required to produce the error:
- query involves a has() of a has(), or_()'d with another has() of a
has().
- class we are query()'ing on must have a composite join to the class
we are using with the first has()
The code (apologies for length):
from sqlalchemy.engine import engine_from_config
from sqlalchemy.exc import CompileError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column
from sqlalchemy.sql.expression import and_, or_
from sqlalchemy.types import Boolean, Integer
settings = {'sqlalchemy.url': 'sqlite://'}
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession = sessionmaker()
Base = declarative_base()
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
class Coordinate(Base):
__tablename__ = 'coordinate'
id = Column(Integer, primary_key=True)
x = Column(Integer)
class Point(Base):
__tablename__ = 'point'
id = Column(Integer, primary_key=True)
flag = Column(Boolean, default=True)
coordinate_id = Column(Integer, nullable=True)
coordinate = relationship(
'Coordinate',
primaryjoin='Point.coordinate_id == Coordinate.id',
foreign_keys=[coordinate_id])
class Line(Base):
__tablename__ = 'line'
id = Column(Integer, primary_key=True)
start_point_id = Column(Integer, nullable=True)
start_point = relationship(
'Point',
primaryjoin='and_('
'(Line.start_point_id == Point.id),'
'(Point.flag == True))',
foreign_keys=[start_point_id])
end_point_id = Column(Integer, nullable=True)
end_point = relationship(
'Point',
primaryjoin='and_('
'(Line.end_point_id == Point.id),'
'(Point.flag == True))',
foreign_keys=[end_point_id])
Base.metadata.create_all(engine)
session = DBSession()
starts_one = Line.start_point.has(Point.coordinate.has(Coordinate.x ==
1))
ends_one = Line.end_point.has(Point.coordinate.has(Coordinate.x == 1))
starts_other = Line.start_point.has(Point.coordinate.has(Coordinate.x !
= 1))
ends_other = Line.end_point.has(Point.coordinate.has(Coordinate.x !=
1))
one_to_other = and_(starts_one, ends_other)
other_to_one = and_(starts_other, ends_one)
try:
or_results = session.query(Line).filter(or_(
one_to_other, other_to_one
)).all()
except CompileError as e:
print "query with or_() causes CompileError. Error was:"
print str(e)
q1 = session.query(Line).filter(one_to_other)
q2 = session.query(Line).filter(other_to_one)
q3 = q1.union(q2)
try:
union_results = q3.all()
except CompileError as e:
print "query with union() causes CompileError. Error was:"
print str(e)
--
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.
