CODEWITHSUNDEEP
pythondjango
Richard
Richard

Reputation: 65560

Regex search from django.db.connection?

This works at the psql console:

SELECT * FROM chemical WHERE name ~* 'statin';

But not from inside Django:

from django.db import connection
cursor = connection.cursor()
code = 'statin'
query = 'SELECT * FROM chemical WHERE name ~* %s'
print query
cursor.execute(query, (code,))
print cursor.fetchall()

This prints like this at the Django console:

SELECT * FROM chemical WHERE name ~* %s

But it returns an empty queryset.

How can I do regex search from inside Django? I guess I need to escape or unescape something.

Unfortunately chemical is a materialized view so I can't just use the Django ORM.

Upvotes: 0

Views: 84

Answers (2)

Joey Wilhelm
Joey Wilhelm

Reputation: 5819

Actually, the Django ORM can perform regex lookups for you, using the __regex lookup, as seen here: https://docs.djangoproject.com/en/1.8/ref/models/querysets/#regex

Or, as you are doing there, a case insensitive regex, with the __iregex lookup: https://docs.djangoproject.com/en/1.8/ref/models/querysets/#iregex

Upvotes: 0

ForceBru
ForceBru

Reputation: 44888

I think, you need this:

from django.db import connection
cursor = connection.cursor()
code = 'statin'
query = 'SELECT * FROM chemical WHERE name ~* {}'
print query.format(code)
cursor.execute(query.format(code))
print cursor.fetchall()

There's nothing to do with Django, you have to format the string properly.

Upvotes: 1

Related Questions