On 03/07/2016 01:03 PM, Frazer McLean wrote:
Hi,
This should demonstrate the problem I'm having. I think I should be
doing something to turn 'other' into an expression before
BinaryExpression will work with it?
|
importsqlalchemy.types astypes
fromsqlalchemy importColumn,Integer,create_engine
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm importSession
fromsqlalchemy.sql importoperators
fromsqlalchemy.sql.expression importBinaryExpression
Base=declarative_base()
classTEXT(types.Text):
classComparator(types.Text.Comparator):
defregexp(self,other):
returnBinaryExpression(
self.expr,other,operator=operators.custom_op('~'),type_=TEXT,
negate=operators.custom_op('!~'))
the string you're passing needs to be coerced into a "literal" element
or other ColumnElement of some kind. The easiest way to tap into the
existing mechanics for that is just to use the generic "compare()"
method; as for the negation, the hooks don't let that pass through so
you can just add it on after the fact:
class TEXT(types.Text):
class Comparator(types.Text.Comparator):
def regexp(self, other):
expr = self.operate(
operators.custom_op('~'),
other)
expr.negate = operators.custom_op('!~')
return expr
comparator_factory = Comparator
querying as follows:
session.query(TextTest).filter(TextTest.name.regexp('o')).all()
session.query(TextTest).filter(~TextTest.name.regexp('o')).all()
output:
SELECT text_test.id AS text_test_id, text_test.name AS text_test_name
FROM text_test
WHERE text_test.name ~ %(name_1)s
2016-03-07 16:40:03,132 INFO sqlalchemy.engine.base.Engine SELECT
text_test.id AS text_test_id, text_test.name AS text_test_name
FROM text_test
WHERE text_test.name !~ %(name_1)s
comparator_factory =Comparator
classTextTest(Base):
__tablename__ ='text_test'
id =Column(Integer,primary_key=True)
name =Column(TEXT)
engine =create_engine("postgresql://scott:tiger@localhost/test",echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session =Session(engine)
session.add(TextTest(name='Bob'))
session.commit()
session.query(TextTest).filter(TextTest.name.regexp('o'))
"""
Traceback (most recent call last):
File "test_regexp.py", line 35, in <module>
session.query(TextTest).filter(TextTest.name.regexp('o'))
File "test_regexp.py", line 15, in regexp
negate=operators.custom_op('!~'))
File "C:\...\lib\site-packages\sqlalchemy\sql\elements.py", line
2731, in __init__
self.right = right.self_group(against=operator)
AttributeError: 'str' object has no attribute 'self_group'
"""
|
Thanks!
On Monday, 7 March 2016 18:36:11 UTC+1, Mike Bayer wrote:
On 03/07/2016 09:11 AM, Frazer McLean wrote:
> ||I am trying to add regexp and iregexp operators to a subclass
of the
> Text type. I'm using SQLAlchemy v1.0.12.
>
> I am able to correctly use '~' and '!~' operators if I use a
notregexp
> method like this:
>
> |
> importsqlalchemy.types astypes
>
> classTEXT(types.Text):
> classComparator(types.Text.Comparator):
> defregexp(self,other):
> returnself.op('~')(other)
>
> defnotregexp(self,other):
> returnself.op('!~')(other)
>
> comparator_factory =Comparator
> |
>
> However, I would like to correctly emit the '!~' operator if I
use not_
> or ~ in SQLAlchemy, so I tried to do the following:
>
> |
> importsqlalchemy.types astypes
> fromsqlalchemy.sql.expression importBinaryExpression
> fromsqlalchemy.sql importoperators
>
> classTEXT(types.Text):
> classComparator(types.Text.Comparator):
> defregexp(self,other):
> returnBinaryExpression(
> self,other,operator=operators.custom_op('~'),type_=TEXT,
> negate=operators.custom_op('!~'))
>
> comparator_factory =Comparator
that's the correct approach.
> |
>
> But I can't seem to get that to work.
can you provide a complete http://stackoverflow.com/help/mcve
<http://stackoverflow.com/help/mcve> and I'll
make it work ? thanks
I was attempting to copy the
> UnaryExpression example here
>
<http://docs.sqlalchemy.org/en/latest/core/custom_types.html?highlight=userdefinedtype#redefining-and-creating-new-operators
<http://docs.sqlalchemy.org/en/latest/core/custom_types.html?highlight=userdefinedtype#redefining-and-creating-new-operators>>.
>
> I see the patch on BitBucket
>
<https://bitbucket.org/zzzeek/sqlalchemy/issues/1390/postgresql-regular-expression-operators
<https://bitbucket.org/zzzeek/sqlalchemy/issues/1390/postgresql-regular-expression-operators>>
> for adding these operators, but I'm trying to do the same with the
> public API. Is it possible?
>
> Thanks,
>
> Frazer McLean
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it,
send
> an email to sqlalchemy+...@googlegroups.com <javascript:>
> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> To post to this group, send email to sqlal...@googlegroups.com
<javascript:>
> <mailto:sqlal...@googlegroups.com <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
