使用SQLAlchemy在SQLite数据库中存储列表/元组

Question

我有一个类，用来保存我在屏幕上绘制的东西的大小和位置。我正在使用sqlalchemy和sqlite数据库来保存这些对象。不过，位置是一个二维的值（x和y），我希望能方便地访问这些值。

MyObject.pos # preferred, simpler interface

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

我可以使用属性来实现这个功能，但这种方法并不是最优的，因为我无法根据这些属性进行查询。

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

有没有什么办法可以使用集合或关联代理来实现我想要的效果呢？

Answer 1

定义你的表时，使用一种叫做 PickleType 的列类型。这样一来，只要你的 Python 对象可以被序列化（也就是可以被“腌制”），它们就会自动保存。比如，元组就是可以被序列化的。

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

Answer 2

如果你在使用 PostGIS（这是一个扩展版的 PostgreSQL，专门处理几何数据），那么你可以利用 GeoAlchemy。这个工具可以让你在数据库中定义几何类型的列。比如说，有一种数据类型叫做 Point，顾名思义，它就是表示一个点。

设置 PostGIS 比普通的 PostgreSQL 要复杂一些，但如果你打算进行基于几何的查询，这些额外的设置（大部分情况下只需要做一次）是非常值得的。

另外一种解决方案是使用普通的 SQLAlchemy，你可以 定义自己的列类型，并在编译时将它们转换为数据库支持的基本类型。

---

其实，你可以使用属性，但不能用内置的 property 装饰器。你需要稍微费点劲，自己创建一个自定义的描述符。

你可能想要一个点的类。一个不错的选择是使用命名元组（namedtuple），因为这样你就不用担心单独坐标的赋值问题。这个属性要么全部赋值，要么什么都不赋。

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

这样我们至少可以比较点的值。接下来，编写描述符时需要考虑它的方法。有两个方法需要关注，__get__ 和 __set__。在获取值时，有两种情况：一种是在实例上调用，你应该处理实际的点值；另一种是在 类 上调用，你应该将其转换为列表达式。

在最后一种情况下，返回什么有点棘手。我们想要的是，当与一个点进行比较时，返回一个列表达式，使得每个列与每个坐标相等。我们还需要再创建一个类来实现这个功能。

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)

剩下的就是定义实际的描述符类了。

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))

这个类可以这样使用：

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

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