CODEWITHSUNDEEP
pythonregexpostgresqlsqlalchemy
jisaacstone
jisaacstone

Reputation: 4284

Sqlalchemy: use PSQL's `~` operator when string is on left and column on right

I know that 

WHERE column ~ 'regexp'

is this is sqlalchemy:

where(column.op('~')('regexp'))

but how could I create this?

WHERE 'string' ~ column

(the regex is stored in the database)

Upvotes: 2

Views: 1514

Answers (2)

Martijn Pieters
Martijn Pieters

Reputation: 1124170

You need to create either a literal() or a bindparam():

from sqlalchemy.sql import expression

expression.literal('string').op('~')(column)
# or
expression.bindparam('text', 'string').op('~')(column)

A literal() is basically a bindparam() with an implicit name; in the above example 'text' is the name (key) of the parameter as used in the SQL statement, and 'string' the text you want to appear on the left-hand side of the operator.

A bindparam() gives you a bit more control, including the ability to pass in a calleble instead of a concrete value:

def value_function():
    return some_calculation_result()

expression.bindparam('calculated_value', callable_=value_function).op('~')(column)

and SQLAlchemy will call value_function() when sending the statement to the database server. You'd also use bindparam() if you have a sequence of values, and SQLAlchemy will take care of adjusting the expression to apply to each value in the sequence:

strings = ['string1', 'string2', ...]
expression.bindparam('one_or_more', strings).op('~')(column)
# executes `('string1' ~ column OR 'string2' ~ column OR ...)`

Upvotes: 4

Peter Eisentraut
Peter Eisentraut

Reputation: 36749

An non-Sqlalchemy-specific solution would be to create your own operator in the PostgreSQL backend that has the operands reversed. That would also allow you to do things like

string ### ANY(ARRAY[regexp1, regexp2, ...])

where ### is your operator. (Finding a good name might be a challenge.)

Upvotes: 0

Related Questions