storing list/tuples in sqlite database with sqlalchemy

I have a class that holds the size and position of something I draw to the screen. I am using sqlalchemy with a sqlite database to persist these objects. However, the position is a 2D value (x and y) and I'd like to have a convienent way to access this as

MyObject.pos # preferred, simpler interface

# instead of: 
MyObject.x 
MyObject.y # inconvenient

I can use properties but this solution isn't optimal since I cannot query based on the prop开发者_StackOverflow中文版erties

session.query(MyObject).filter(MyObject.pos==some_pos).all()

Is there some way to use collections or association proxies to get the behavior I want?

If you are using PostGIS (Geometry extended version of postgres), you can take advantage of that using GeoAlchemy, which allows you to define Column types in terms of geometric primitives available in PostGIS. One such data type is Point, which is just what it sounds like.

PostGIS is a bit more difficult to set up than vanilla PostgreSQL, but if you actually intend to do queries based on actual geometric terms, it's well worth the extra (mostly one time) trouble.

Another solution, using plain SQLAlchemy is to define your own column types with the desired semantics, and translate them at compile time to more primitive types supported by your database.

Actually, you could use a property, but not with the builtin property decorator. You'd have to have to work a little harder and create your own, custom descriptor.

You probably want a point class. A decent option is actually to use a namedtuple, since you don't have to worry about proxying assignment of individual coordinates. The property gets assigned all or nothing

Point = collections.namedtuple('Point', 'x y')

This would let us at least compare point values. The next step in writing the descriptor is to work through its methods. There are two methods to think about, __get__ and __set__, and with get, two situations, when called on an instance, and you should handle actual point values, and when called on the class, and you should turn it into a column expression.

What to return in that last case is a bit tricky. What we want is something that will, when compared to a point, returns a column expression that equates the individual columns with the individual coordinates. well make one more class for that.

class PointColumnProxy(object):
    def __init__(self, x, y):
        ''' these x and y's are the actual sqlalchemy columns '''
        self.x, self.y = x, y

    def __eq__(self, pos):
        return sqlalchemy.and_(self.x == pos.x,
                               self.y == pos.y)

All that's left is to define the actual descriptor class.

class PointProperty(object):
    def __init__(self, x, y):
        ''' x and y are the names of the coordinate attributes '''
        self.x = x
        self.y = y

    def __set__(self, instance, value):
        assert type(value) == Point
        setattr(instance, self.x, value.x)
        setattr(instance, self.y, value.y)


    def __get__(self, instance, owner):
        if instance is not None:
            return Point(x=getattr(instance, self.x),
                         y=getattr(instance, self.y))
        else: # called on the Class
            return PointColumnProxy(getattr(owner, self.x),
                                    getattr(owner, self.y))

which could be used thusly:

Base = sqlalchemy.ext.declarative.declarative_base()
class MyObject(Base):
    x = Column(Float)
    y = Column(Float)

    pos = PointProperty('x', 'y')

Define your table with a PickleType column type. It will then automatically persist Python objects, as long as they are pickleable. A tuple is pickleable.

mytable = Table("mytable", metadata,
       Column('pos', PickleType(),
        ...
)