COLOR in sqlalchemy

Question

I was looking for a way to use tsvector in sqlalchemy (just like others, such as INTEGER, etc.), but so far it is not clear to me how to do this. I read that tsvector can be implemented as a type using UserDefinedType . After some attempts, I'm not leaving anywhere, does anyone have an easy way to do this? thanks

Answer 1

If you want SQLAlchemy to be able to create schemas of type tsvector and just retrieve the serialized value in the queries, this is what you need:

from sqlalchemy import types class tsvector(types.TypeDecorator): impl = types.UnicodeText @compiles(tsvector, 'postgresql') def compile_tsvector(element, compiler, **kw): return 'tsvector' 

tsvector works like a regular type, and you can use it in a table definition. (I forgot where I found the snippet, probably on the SQLAlchemy or wiki mailing list.)

If you need to actually analyze the tsvector data, this is a little trickier. The latest version of hstore support is a good example. However, you can also find the following snippet. This is old code that is known to work and is written using pyparsing:

 from pyparsing import ParserElement, QuotedString, Regex, Group, Suppress, Group, delimitedList, ZeroOrMore, StringEnd ParserElement.enablePackrat() lexeme = QuotedString("'") occurrence_marker = Regex('[1-9][0-9]*[AD]?') atom = Group(lexeme('lexeme') + Suppress(':') + Group(delimitedList(occurrence_marker))('markers'))('atom') tsvector = ZeroOrMore(atom) + StringEnd() parse_tsvector = tsvector.parseString 

Update:

To query the tsvector column, use the .op() method as follows:

 session.query(Model).filter(Model.tsvector.op('@@')(func.plainto_tsquery('search string')))