2014-10-23

Sqlite array type and Python SQLAlchemy

I need to write up things just for remembering how I solved a particular issue if occurs in the future.

Sqlite (with http://sqlitebrowser.org) is great for rapid prototypes development but it lacks some useful implementations provided by Postgresql (for example the sqlalchemy.dialects.postgresql.ARRAY type).

I solved implementing a SQLAlchemy TypeDecorator with a json serialization:   
Here it is the self-explaining code:
from sqlalchemy.schema import Column
from sqlalchemy.types import (
    Integer,
    String,
    TypeDecorator,
    )
from sqlalchemy import Sequence
from pyramid_sqlalchemy import BaseObject as Base
import json

class ArrayType(TypeDecorator):
    """ Sqlite-like does not support arrays.
        Let's use a custom type decorator.

        See http://docs.sqlalchemy.org/en/latest/core/types.html#sqlalchemy.types.TypeDecorator
    """
    impl = String

    def process_bind_param(self, value, dialect):
        return json.dumps(value)

    def process_result_value(self, value, dialect):
        return json.loads(value)

    def copy(self):
        return ArrayType(self.impl.length)

class Element(Base):
    __tablename__ = 'elements'

    id = Column(Integer(),
                Sequence('element_id_seq'),
                primary_key = True)
    # ...
    myarray = Column(ArrayType())
If you are not using Pyramid just replace the pyramid_sqlalchemy's Base wrapper import with:
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

2 comments:

  1. how about a json type http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON

    ReplyDelete
    Replies
    1. postgresql dialect let you use a lot of cool things.
      Anyway this article should help you when your db backend does not support a particular data type you need (maybe because you are mocking a simple prototype with sqlite). This way you can implement any kind of data serialization (not only json) writing your own generic type decorators.

      Delete