Using postgresql xml data type with sqlalchemy

Question

SqlAlchemy supports most database specific data types via dialects, but I could not find anything to work with the postgresql xml column type. Does somebody know a working solution. Idealy it should not require a custom column type implementation by myself.

I don't see it implemented in the postgresql dialects with sqlalchemy 0.8. You could try using reflection to load an existing psql schema with an XML column and seeing how gracefully sqlalchemy handles it. But even in the best case I don't think you'll get nice things like XPATH indexing even if reflection does do something graceful like exposing the column as a text type. Try filing an enhancement request in the sqlalchemy issue tracker. — JosefAssad, Apr 22 '13 at 18:50
Do also have a look at zzzeek's example of using adjacency list to store XML data: https://bitbucket.org/sqlalchemy/sqlalchemy/src/2a4318815eb13a8d9f58bcf7009b115f2c8a839d/examples/elementtree/optimized_al.py?at=default — JosefAssad, Apr 22 '13 at 19:00
we try to rely on psycopg2 as much as possible for these types, while they support JSON and HSTORE they don't have an XML type: http://initd.org/psycopg/docs/extras.html#additional-data-types. that said you can still get lots of functionality here using the techinques at http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-custom, and we'd certainly accept pull requests for completed code. — zzzeek, Apr 23 '13 at 23:28

score 4 · Answer 1 · edited Oct 26 '19 at 14:56

If you need to have native 'xml' data type in postgresql database, you need to write custom type which inherited from UserDefinedType not from TypeDecorator. Documentation

Here is what I used in one of the projects:

import xml.etree.ElementTree as etree
import sqlalchemy

class XMLType(sqlalchemy.types.UserDefinedType):
    def get_col_spec(self):
        return 'XML'

    def bind_processor(self, dialect):
        def process(value):
            if value is not None:
                if isinstance(value, str):
                    return value
                else:
                    return etree.tostring(value)
            else:
                return None
        return process

    def result_processor(self, dialect, coltype):
        def process(value):
            if value is not None:
                value = etree.fromstring(value)
            return value
        return process

In python 3, replace `etree.tostring(value)` with `etree.tostring(value, encoding="unicode")` — Giacomo Lacava, Oct 26 '19 at 11:25

score 1 · Answer 2 · edited May 23 '17 at 10:30

See: SQLAlchemy TypeDecorator doesn't work

Here is the same solution modified to handle XMLTYPE for oracle with any length of xml and to allow lxml etree assignment to and from the class column (no need to deparse/reparse xml from container classes)

# coding: utf-8
from sqlalchemy import Column, DateTime, Float, ForeignKey, Index, Numeric, String, Table, Text, CLOB
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.sql.functions import GenericFunction
class XMLTypeFunc(GenericFunction):
    type=CLOB
    name='XMLType'
    identifier='XMLTypeFunc'


from sqlalchemy.types import TypeDecorator
from lxml import etree #you can use built-in etree if you want
class XMLType(TypeDecorator):

    impl = CLOB
    type = 'XMLTYPE' #etree.Element

    def get_col_spec(self):
        return 'XMLTYPE'

    def bind_processor(self, dialect):
        def process(value):
            if value is not None:
                return etree.tostring(value, encoding='UTF-8', pretty_print='True')
                #return etree.dump(value)
            else:
                return None
        return process

    def process_result_value(self, value, dialect):
        if value is not None:
            value = etree.fromstring(value)
        return value

    def bind_expression(self, bindvalue):
        return XMLTypeFunc(bindvalue)

Using postgresql xml data type with sqlalchemy

2 Answers2

Linked