On Feb 7, 2012, at 12:34 PM, yannack wrote:
> Hello list,
> I have the following problem. I wish to query my database to look for
> columns with names included in, or including, a given string.
> is there a way to do the opposite of "contains", ie, a "contained"
> method for string columns? Right now, I am doing the following:
>
> session.query(Parcours).filter(
> or_(
>
> Parcours.parcours_name.ilike('%'+custom_string+'%'),
> literal_column("'"+custom_string
> +"'").ilike('%'+Parcours.parcours_name+'%'))
>
> this works "ok" for most stuff, but breaks when custom_strings has
> quotes and such. Therefore, I need to improve on this:
> - either by escaping the custom_string ?
anytime you think you need to escape a string, you really need to send it as a
parameter:
('%' + literal(custom_string) + '%').ilike('%' + MyClass.column + '%')
> - or by taking another approach than the "ilike" route (I use ilike
> instead of contains simply because I don't know how to do case-
> insensitive "contains" in PGSQL and my code will have to run both on
> sqlite and PGSQL)
you might make life easier by building a custom construct, so that you can do
something different for SQLite vs. Postgresql:
from sqlalchemy.sql.expression import ColumnElement
from sqlalchemy.sql import column, literal
from sqlalchemy.ext.compiler import compiles
class IContains(ColumnElement):
def __init__(self, a, b):
self.a = a
self.b = b
@compiles(IContains)
@compiles(IContains, 'postgresql')
def compile(element, compiler, **kw):
return "'%%' || %s || '%%' ILIKE '%%' || %s || '%%'" % (
compiler.process(element.a),
compiler.process(element.b)
)
print IContains(column("hi"), literal("there"))
>
> also, is there a case-insensitive version of the "contains" method
> (which would generate ilike statements instead of like in postgresql
> while properly escaping quotes and other stuff) ?
seems like there isn't at the moment. wouldn't be hard to add in
istartswith(), iendswith(), icontains()....
>
> Finally, I am also open to any thoughts and pointers people may have
> concerning partial/approaching string matching. At the moment, I just
> do this contains/contained, case-insensitive search, and finish with
> some manual matching, but if anyone has opinions on this, I am of
> course very interested!
Postgresql has many flavors of text matching including POSIX regular
expressions:
http://www.postgresql.org/docs/8.3/static/functions-matching.html
In SQLite, you can register a regular expression function into pysqlite using
create_function():
http://docs.python.org/library/sqlite3.html#sqlite3.Connection.create_function
SQLAlchemy gives you a hook to do this using the "connect" event:
from sqlalchemy import event
from sqlalchemy.pool import Pool
@event.listens_for(Pool, "connect")
def register_function(dbapi_conn, conn_record):
dbapi_conn.create_function("regexp", 2, my_regexp_function)
there's a regexp example on stackoverflow:
http://stackoverflow.com/a/5365533/34549
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
