[sqlalchemy] sqlite string concats and datetime arithmetics
I'm trying to get a series of datetimes using func.datetime. The format of
input is func.datetime(basetime, '+ NNN seconds'), which works nicely if
the shift applied is constant. However I need to add 10, 20, 30 seconds,
etc to this base time. So I want something like func.datetime(basetime,
concat('+', seconds.c.n, ' seconds')), but concat doesn't work for sqlite,
which concatenates using the '||' operator. Is there working method to
concat in sqlite?
Failing that, is there another way to get at what I want with datetime
arithmetics?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] unregister mapper event
Is it possible to remove mapper events? Specifically I want to call event.remove(mapper, 'mapper_configured', fn) but I get an error back saying Mapper is not iterable. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] kivy and sqlalchemy
I'm writing an application that uses kivy for GUI and sqlalchemy for ORM. Each has its own instrumentation system, and I initially planned to connect them by 1) mirroring relevant SA attributes in kivy at init, 2) work with only the kivy versions of the attributes for the duration of the app, 3) on write events in kivy, propagate the writes to SA. This works well except when inside SA, changes are propagated through relationships and backrefs. Then I need to reload kivy attributes from their SA mirror parts when such things happen. But I'm having a hard time trying to figure out a way to do this without getting an infinite feedback loop. Particularly, I looked at the AttributeEvents of SA, and the initiator argument seems hopeful, but I'm not familiar with the internals of SA. Does the attribute implementation object have attributes/methods that allows me to prevent the loop? Otherwise, is there an easy way to handle this? Or do I need to get dirty and do something like constructing a child class of both kivy and SA properties? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Strange behavior with a class inheriting from AssociationProxy
I see now, thanks! It's fairly difficult to plug in the events as you
suggested in my code, but I subclassed MappedCollection to discard setitem
calls with a None key, and used this as the collection class. This seems to
work, and if there's no other events I need to worry about during this
append process, should be a complete solution.
On Monday, July 8, 2013 9:14:02 PM UTC-5, Michael Bayer wrote:
here's how you debug that:
@event.listens_for(A.ab, append)
def append(target, value, initiator):
import pdb
pdb.set_trace()
@event.listens_for(B.ab, append)
def append(target, value, initiator):
import pdb
pdb.set_trace()
and if you're concerned about attribute-access side effects, in your pdb
you look at an object like:
ab.__dict__
no side effects that way (though there aren't any getter side effects in
this test).
the sequence is:
1. create AB(a=a, b=b)
2. the AB has a set first, which then fires off the backref A.ab
3. AB is assigned to the A.ab dictionary, with key of None because AB.b is
None
4. AB.b is then assigned to b
5. AB.b fires off backref B.ab
6. the association proxy now gets involved, and appends your AB to the
A.ab collection again, this time with the correct key of b
So if you just did the assignment without the association proxy (which is
a good idea when understanding this), you just get the key of None in aa.ab
and nothing else. If you were to assign .b or .a to the AB first, you get
the same problem here in one direction or the other, because both AB.a and
AB.b will both try to assign it to a dictionary that requires the other
reference be present, it's a mutual referencing issue.
It's an awkward mapping, one way to make it work is to just not use
backrefs and make your own event, though to make it work in both directions
without going into an endless loop would require a more intricate approach
(using internal appenders that pass along the initiator so you can stop
an endless setter loop). Below is just the one direction:
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
ab = relationship('AB',
collection_class=attribute_mapped_collection('b'))
abnum = correlated_proxy('ab', 'num', correlator=corr)
class AB(Base):
__tablename__ = 'table_ab'
num = Column(Integer)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
b_id = Column(Integer, ForeignKey('table_b.id'), primary_key=True)
a = relationship(A)
b = relationship(B)
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
ab = relationship('AB',
collection_class=attribute_mapped_collection('a')
)
@event.listens_for(A.ab, append)
def append(target, value, initiator):
value.b.ab[value.a] = value
On Jul 8, 2013, at 9:07 PM, Greg Yang sorcer...@gmail.com javascript:
wrote:
I created a class CorrelatedProxy inheriting from AssociationProxy that
allows the creator function to depend on the owner instance of the
association proxy. Essentially it gets a attribute 'correlator' of the
something like lambda x: lambda y, z: Constructor(x, y, z), and then
intercepts the __get__ of AssociationProxy to create self.creator on the
fly by applying the owner instance to the correlator. Now consider the code
below.
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer
from sqlalchemy.ext.associationproxy import AssociationProxy
class CorrelatedProxy(AssociationProxy):
def __init__(self, *args, **kw):
self.correlator = kw.pop('correlator', None)
AssociationProxy.__init__(self, *args, **kw)
def __get__(self, obj, class_):
if obj:
self.creator = self.correlator(obj)
return AssociationProxy.__get__(self, obj, class_)
def correlated_proxy(*args, **kw):
return CorrelatedProxy(*args, **kw)
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
ab = relationship('AB', backref = 'a',
collection_class=attribute_mapped_collection('b'))
abnum = correlated_proxy('ab', 'num', correlator=\
lambda a: lambda b, n: AB(a=a, b=b, num=n))
class AB(Base):
__tablename__ = 'table_ab'
num = Column(Integer)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
b_id = Column(Integer, ForeignKey('table_b.id'), primary_key=True)
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True
[sqlalchemy] Strange behavior with a class inheriting from AssociationProxy
I created a class CorrelatedProxy inheriting from AssociationProxy that
allows the creator function to depend on the owner instance of the
association proxy. Essentially it gets a attribute 'correlator' of the
something like lambda x: lambda y, z: Constructor(x, y, z), and then
intercepts the __get__ of AssociationProxy to create self.creator on the
fly by applying the owner instance to the correlator. Now consider the code
below.
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer
from sqlalchemy.ext.associationproxy import AssociationProxy
class CorrelatedProxy(AssociationProxy):
def __init__(self, *args, **kw):
self.correlator = kw.pop('correlator', None)
AssociationProxy.__init__(self, *args, **kw)
def __get__(self, obj, class_):
if obj:
self.creator = self.correlator(obj)
return AssociationProxy.__get__(self, obj, class_)
def correlated_proxy(*args, **kw):
return CorrelatedProxy(*args, **kw)
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
ab = relationship('AB', backref = 'a',
collection_class=attribute_mapped_collection('b'))
abnum = correlated_proxy('ab', 'num', correlator=\
lambda a: lambda b, n: AB(a=a, b=b, num=n))
class AB(Base):
__tablename__ = 'table_ab'
num = Column(Integer)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
b_id = Column(Integer, ForeignKey('table_b.id'), primary_key=True)
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
ab = relationship('AB', backref = 'b',
collection_class=attribute_mapped_collection('a'))
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
aa = A()
bb = B()
aa.abnum[bb] = 1
assert aa.abnum[bb] == aa.abnum[None] == 1
Basically, no matter, what I do, any time I assign something to the
CorrelatedProxy, everything goes normally except that 'None' always becomes
a key, assigned to the last value I assigned to the proxy. I tried
debugging and tracing, but there's some quantum effect going on where if I
inspect some value, some other value changes. I for the life of me can't
figure out why it's doing this. I'm guessing it's some Instrumentation
effect of SA, but I don't understand the in and outs of that very much. I
currently can work around this by filtering out the None, but it'd be nice
to know why this occurs and whether it will affect any other elements of my
program with whatever is going on underneath.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] self-referential one to many relationship with a kind of composite foreign key
Oh wow, haha, this is pretty awesome. Never thought I'd use mapper by
itself at all! Thanks a lot! I guess the ancient technique is still
useful for something, eh
On Wednesday, June 12, 2013 11:36:21 AM UTC-5, Michael Bayer wrote:
Well for proxies you'd need to roll part of it manually, like a custom
collection that filters, that kind of thing.
But no matter. I'll apologize up front you don't get the hear the raucous
cackle I made when I got this to work. Because it required heading down
the dusty stairs to unlock the ancient texts, calling upon a technique that
in the early days I thought would be commonplace, but now is mostly unheard
of. The non-primary mapper means you're going to make a second map of a
class - mostly obsolete for querying because you can pass any kind of
selectable into a Query anyway using aliased(). But it is still useful
when you need to get a very unusual thing into relationship(). in 0.9,
you can join to this mapper without the nested SELECT as long as you're not
on sqlite, but this all works great in 0.8 too.
from sqlalchemy import *
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import *
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('table_a.id'))
children = relationship('A', backref=backref('parent',
remote_side=[id]))
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
a_re = relationship('A', backref='b_re')
b_table = B.__table__
a_table = A.__table__
# this is easier to map to an alias like this:
#child_bs = select([b_table, a_table.c.child_id],
#
use_labels=True).select_from(b_table.join(a_table)).alias()
# but mapping straight to the JOIN we get simpler queries
# (for lazyloads in 0.8, joins/joinedloads too in 0.9)
child_bs = b_table.join(a_table)
cbm = mapper(B, child_bs, properties=dict(
# make sure attribute names line up
# with our original names...
id=child_bs.c.table_b_id,
a_id=child_bs.c.table_a_id,
_b_a_id=child_bs.c.table_b_a_id,
),
non_primary=True)
B.children = relationship(cbm,
primaryjoin=and_(
B.id == foreign(remote(child_bs.c.table_b_id)),
B.a_id ==
foreign(remote(child_bs.c.table_a_child_id))
# or can get to the cols using mapped names
# B.id == foreign(remote(cbm.c.id)),
# B.a_id == foreign(remote(cbm.c.child_id))
),
viewonly=True,
collection_class=set)
e = create_engine(sqlite://, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
a1 = A()
a2 = A(parent=a1)
a3 = A(parent=a1)
a4 = A()
b1 = B(id=1, a_re=a1)
b2 = B(id=1, a_re=a2)
b3 = B(id=1, a_re=a3)
b4 = B(id=1, a_re=a4)
b5 = B(id=2, a_re=a1)
b6 = B(id=2, a_re=a2)
b7 = B(id=2, a_re=a3)
b8 = B(id=2, a_re=a4)
s.add_all([
a1, a2, a3, a4
])
s.commit()
assert b1.children == set([b2, b3])
assert b5.children == set([b6, b7])
s.expire_all()
# oh yes
for beta in s.query(B).options(joinedload(children)):
for b in beta.children:
assert b.id == beta.id
assert b.a_re in beta.a_re.children
On Jun 11, 2013, at 6:34 PM, Greg Yang sorcer...@gmail.com javascript:
wrote:
How would you use proxies? I can get B.a_re.children.b_re, but this
includes all Bs that have different B.id than I want along with the ones I
do want. I could just use a @property that issues SQL on every call, but
I'm trying to see if there are more efficient ways of doing this.
On Tuesday, June 11, 2013 4:18:20 PM UTC-5, Michael Bayer wrote:
getting it to work with secondary or only primaryjoin as it sometimes
works out is fairly complex and might not be possible. If secondary,
you might need to make secondary an aliased SELECT statement, or in 0.9
maybe it can be a a JOIN, that represents all the intermediary rows. Might
work, might not, would have to spend a few hours with it.
Is there a reason you can't just route to the related
B.a.children.bshttp://b.a.children.bs/using proxies? Or a @property
based loader?
On Jun 11, 2013, at 4:45 PM, Greg Yang sorcer...@gmail.com wrote:
Consider these 2 mapped classes
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from
[sqlalchemy] self-referential one to many relationship with a kind of composite foreign key
Consider these 2 mapped classes
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm.util import aliased
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.sql.expression import and_
from sqlalchemy.types import Integer, String
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('table_a.id'))
children = relationship('A', backref = 'parent', remote_side=[id])
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
a_re = relationship('A', backref='b_re')
What I want to do is have a self-referential relationship in B that routes
through A's children relationship while keeping B.id the same. More
explicitly I want some relationship B.children such that for any instance
beta of B
for b in beta.children:
assert b.id == beta.id
assert b.a_re in beta.a_re.children
Now, if the condition b.id == beta.id is ignored, then it's just a plain
association table many-to-many relationship, something like
B.children = relationship('B', secondary=A.__table__,
primaryjoin=B.a_id==A.id, secondaryjoin=B.a_id==A.child_id, viewonly=True)
But with the b.id == beta.id condition I need to refer to table_b twice in
the join table_b JOIN table_a JOIN table_b, and I'm not sure how to do that
in relationship.
I've tried this
BB = aliased(B)
B.children = relationship('BB', secondary=A.__table__,
primaryjoin=B.a_id==A.id,
secondaryjoin='''and_(A.id==BB.a_id,
B.id==BB.id)''',
viewonly=True)
but it seems like BB is not recognized by the ORM in mapping.
How do I do this?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] self-referential one to many relationship with a kind of composite foreign key
How would you use proxies? I can get B.a_re.children.b_re, but this
includes all Bs that have different B.id than I want along with the ones I
do want. I could just use a @property that issues SQL on every call, but
I'm trying to see if there are more efficient ways of doing this.
On Tuesday, June 11, 2013 4:18:20 PM UTC-5, Michael Bayer wrote:
getting it to work with secondary or only primaryjoin as it sometimes
works out is fairly complex and might not be possible. If secondary,
you might need to make secondary an aliased SELECT statement, or in 0.9
maybe it can be a a JOIN, that represents all the intermediary rows. Might
work, might not, would have to spend a few hours with it.
Is there a reason you can't just route to the related B.a.children.bsusing
proxies? Or a @property based loader?
On Jun 11, 2013, at 4:45 PM, Greg Yang sorcer...@gmail.com javascript:
wrote:
Consider these 2 mapped classes
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm.util import aliased
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.sql.expression import and_
from sqlalchemy.types import Integer, String
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('table_a.id'))
children = relationship('A', backref = 'parent', remote_side=[id])
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
a_re = relationship('A', backref='b_re')
What I want to do is have a self-referential relationship in B that routes
through A's children relationship while keeping B.id the same. More
explicitly I want some relationship B.children such that for any instance
beta of B
for b in beta.children:
assert b.id == beta.id
assert b.a_re in beta.a_re.children
Now, if the condition b.id == beta.id is ignored, then it's just a plain
association table many-to-many relationship, something like
B.children = relationship('B', secondary=A.__table__,
primaryjoin=B.a_id==A.id, secondaryjoin=B.a_id==A.child_id, viewonly=True)
But with the b.id == beta.id condition I need to refer to table_b twice
in the join table_b JOIN table_a JOIN table_b, and I'm not sure how to do
that in relationship.
I've tried this
BB = aliased(B)
B.children = relationship('BB', secondary=A.__table__,
primaryjoin=B.a_id==A.id,
secondaryjoin='''and_(A.id==BB.a_id,
B.id==BB.id)''',
viewonly=True)
but it seems like BB is not recognized by the ORM in mapping.
How do I do this?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Ordering by composite column gives sqlite3 OperationalError
Right now query.order_by(composite) gives a sqlite3 operational error,
because the rendered SQL is ORDER BY (composite_val1, composite_val2,
composite_val3) instead of ORDER BY composite_val1, composite_val2,
composite_val3. (The parenthesis is causing an error)
For example, consider the code below modified from the documentation.
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship, composite
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
import itertools
Base = declarative_base()
class Point(object):
def __init__(self, x, y):
self.x = x
self.y = y
def __composite_values__(self):
return self.x, self.y
def __repr__(self):
return Point(x=%r, y=%r) % (self.x, self.y)
def __eq__(self, other):
return isinstance(other, Point) and \
other.x == self.x and \
other.y == self.y
def __ne__(self, other):
return not self.__eq__(other)
class Vertex(Base):
__tablename__ = 'vertice'
id = Column(Integer, primary_key=True)
x1 = Column(Integer)
y1 = Column(Integer)
x2 = Column(Integer)
y2 = Column(Integer)
start = composite(Point, x1, y1)
end = composite(Point, x2, y2)
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
pts = [((1, 2), (3, 4)),
((2, 3), (1, 5)),
((0, 5), (6, 3))]
session.add_all(itertools.starmap(
lambda a, b: Vertex(start=Point(*a),
end=Point(*b)),
pts))
We run the following in the console:
q = session.query(Vertex).order_by(Vertex.start)
q
Out[1]: sqlalchemy.orm.query.Query at 0x3bc1f30
str(q)
Out[1]: 'SELECT vertice.id AS vertice_id, vertice.x1 AS vertice_x1,
vertice.y1 AS vertice_y1, vertice.x2 AS vertice_x2, vertice.y2 AS
vertice_y2 \nFROM vertice ORDER BY (vertice.x1, vertice.y1)'
q.all()
Traceback (most recent call last):
File C:\Anaconda\Lib\site-packages\IPython\core\interactiveshell.py,
line 2731, in run_code
exec code_obj in self.user_global_ns, self.user_ns
File ipython-input-1-511354a8265d, line 1, in module
q.all()
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py,
line 2140, in all
return list(self)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py,
line 2252, in __iter__
return self._execute_and_instances(context)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py,
line 2267, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py,
line 664, in execute
params)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py,
line 764, in _execute_clauseelement
compiled_sql, distilled_params
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py,
line 878, in _execute_context
context)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py,
line 871, in _execute_context
context)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\default.py,
line 320, in do_execute
cursor.execute(statement, parameters)
OperationalError: (OperationalError) near ,: syntax error u'SELECT
vertice.id AS vertice_id, vertice.x1 AS vertice_x1, vertice.y1 AS
vertice_y1, vertice.x2 AS vertice_x2, vertice.y2 AS vertice_y2 \nFROM
vertice ORDER BY (vertice.x1, vertice.y1)' ()
Whereas, if we directly execute the correct SQL, without the parenthesis,
session.execute(u'SELECT vertice.id AS vertice_id, vertice.x1 AS
vertice_x1, vertice.y1 AS vertice_y1, vertice.x2 AS vertice_x2, vertice.y2
AS vertice_y2 \nFROM vertice ORDER BY vertice.x1, vertice.y1' )
Out[1]: sqlalchemy.engine.result.ResultProxy at 0x3bc1d70
_.fetchall()
Out[1]: [(3, 0, 5, 6, 3), (1, 1, 2, 3, 4), (2, 2, 3, 1, 5)]
We get the right result back, albeit not wrapped in Vertex objects
So it seems like a fairly simple bug.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] AssociationProxy's behavior with == None seems unintuitive
Awesome! The changes should be able to cover the issue.
On Saturday, June 8, 2013 12:40:51 PM UTC-5, Michael Bayer wrote:
On Jun 8, 2013, at 1:33 AM, Greg Yang sorcer...@gmail.com javascript:
wrote:
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
b1 = B()
b2 = B()
b3 = B()
b1.a_color = 'blue'
session.add_all([b1, b2, b3])
q = session.query(B).filter(B.a_color == None).all()
p = session.query(B).filter(B.a_color != None).all()
assert q == []
assert set(p) == set([b1, b2, b3])
I find it surprising that, when only b1 really has a color string
through B.a_color, filtering for B.a_color == None doesn't return b2 and
b3, and filtering for B.a_color != None returns b2 and b3 at all. The
latter is especially unintuitive.
The scalar comparison case was only rudimentally implemented. Along
these lines, the case of query(B).filter(B.a_color != 'blue') also needed
to be covered. Keeping in mind that in SQL, x != 'somevalue' does *not*
return rows where x IS NULL, this will now return those B's for which an
A with non-NULL a_color is present but not equal to 'blue'.
While the results are pretty wrong especially in the != None case, this
seems strongly like an 0.9 only change, as it is very possible that
applications are relying upon the current behavior. Particularly the x
== None case not returning records for which an association row is not
present could break an application that isn't prepared for those rows. The
x != 'somevalue' case behaves very differently as well, no longer
returning rows for which the association is missing. An illustration of
all these cases contrasted can be seen in
http://www.sqlalchemy.org/trac/ticket/2751. The changes are committed
in r20d1e9c3fa8ccc992079.
On a similar note, is there a way to get a_re.has() or equivalent
through only a_color? This can be important when a_re is supposed to be a
private variable, and only the string a_color is exposed. I originally
thought that != None would do the trick but that doesn't work, as I've
shown here.
this is like a special operation so I also added support for an empty
call to B.a_color.has(). If you put criterion in there, it raises, because
while you can do that, it doesn't really make any sense. The difference
between B.a_color.has() and B.a_color != None is that the former will
return you rows for which an A exists, regardless of whether or not A.color
is NULL.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] AssociationProxy's behavior with == None seems unintuitive
Right now a filter clause AssociationProxy == None
Consider the following code:
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
color = Column(String)
def __init__(self, color):
self.color = color
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('table_a.id'))
a_re = relationship('A', backref='b_re')
a_color = association_proxy('a_re', 'color')
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
b1 = B()
b2 = B()
b3 = B()
b1.a_color = 'blue'
session.add_all([b1, b2, b3])
q = session.query(B).filter(B.a_color == None).all()
p = session.query(B).filter(B.a_color != None).all()
assert q == []
assert set(p) == set([b1, b2, b3])
I find it surprising that, when only b1 really has a color string through
B.a_color, filtering for B.a_color == None doesn't return b2 and b3, and
filtering for B.a_color != None returns b2 and b3 at all. The latter is
especially unintuitive.
Now I know what's going on:
print B.a_color == None
EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL)
print B.a_color != None
NOT (EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL))
The == clause requires that the relationship a_re has a valid target in the
first place before checking for a_re.color IS NULL, and the != is just the
negation of that. I understand that this is the desired action when the
right side is some non-null value like blue, but in this case it should
be implemented along the lines of
print B.a_color == None
table_b.a_id IS NULL
OR
EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL)
print B.a_color != None
table_b.a_id IS NOT NULL
AND
NOT (EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL))
On a similar note, is there a way to get a_re.has() or equivalent through
only a_color? This can be important when a_re is supposed to be a private
variable, and only the string a_color is exposed. I originally thought that
!= None would do the trick but that doesn't work, as I've shown here.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
