I'm trying to create an in-memory SQLite database off of SQLAlchemy ORMs originally designed for a Postgres database. To allow the SQLite engine to convert postgres specific datatypes, I've relied on the SQLAlchemy @compiles
decorator, which works as intended for types in the sqlalchemy.dialects.postgresql
namespace.
How can I convert the geoalchemy2.Raster
data type to a engine-agnostic data type (it could be a sqlalchemy.BLOB
type for all I care. The important thing is that I can create tables that are similar, they don't need to be exact)?
This example code converts the DOUBLE_PRECISION
type as intended, but I'm at a loss for how to modify the geoalchemy2.Raster
data type which is a UserDefinedType
. I'd like to be able to write a similar method to compile_DOUBLE_PRECISION()
that changes Raster
into a BLOB
type.
import sqlalchemy
from sqlalchemy import Column, Integer
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.sqlite.base import SQLiteTypeCompiler
from geoalchemy2 import Raster
Base = declarative_base()
class SomeModel(Base):
__tablename__ = "some_model"
id = Column(Integer, primary_key=True)
double_precision = Column(DOUBLE_PRECISION)
raster = Column(Raster)
# The following @compiles works as intended
@compiles(DOUBLE_PRECISION, 'sqlite')
def compile_DOUBLE_PRECISION(element, compiler: SQLiteTypeCompiler, **kw):
""" Handles postgres DOUBLE_PRECISION datatype as REAL in sqlite. """
return compiler.visit_real(element, **kw)
def test_throws_error_due_to_raster_data_type():
engine = sqlalchemy.create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
I'm not familiar with postgresql but maybe something like this could work