CODEWITHSUNDEEP
pythonmysqldjango
Jonn Ralge Yuvallos
Jonn Ralge Yuvallos

Reputation: 71

MySQL REGEXP query via Django doesn't return anything but queries in MySQL work just fine, any ideas?

I'm currently developing a website that visualizes the amount of occurrences a certain ngram has given a date range, similar to google's ngram viewer, but for my native language. My thesis adviser said we should implement a regex query that returns all the ngrams that fit the given regex. However, querying via Django gives me an empty result set, and running the query in MySQL workbench gives me the data I want.

I've tried using both a raw query and Model.objects.filter as shown below.

def regex(request, regexPattern):
    #TO MAKE WORK
    if(request.method == 'GET'):
        print(regexPattern)
        results = Ngram.objects.filter(ngram__regex = regexPattern)
        for x in results:
            print(x)
        sql = "SELECT ngram FROM ngram n WHERE n.ngram REGEXP %s AND n_number = %s"
        val = (regexPattern, "1")
        with connection.cursor() as cur:
            cur.execute(sql, val)
            myresult = cur.fetchall()
        data = []
        #data["ngrams"] = regexPattern
        for x in myresult:
            print(x)

The MySQL query is 

SELECT ngram
FROM ngram
WHERE ngram REGEXP 'du\w{0,5}' AND n_number = '1'

Both for loops that print x from the resultSets from Ngram.objects.filter and the raw query print nothing, but if I run this query in my database I get a lot of rows. Here is a sample

ngram
------
modumo
dulay
dumating
maduwag
dulot
dumi
...
...

Any ideas how to get it to work in Django?

EDIT: To clear it up before anyone asks, Django is connected to the database, I can get data that allows visualization of an ngram's usage over time in other functions.

EDIT 2: Running Ngram.objects.filter(ngram__regex=r'du\w{0,5') in the shell works and returns the data, however this brings me to another problem.

regexString = 'du\w{0,5}'
Ngram.objects.filter(ngram__regex=r+regexString)

is invalid, so I guess another way to answer my question would be how to use models.objects.filter(field__regex=r'regex') with a variable

Upvotes: 0

Views: 178

Answers (1)

Taek
Taek

Reputation: 1024

The correct way to make raw sql queries in django is the Model.objects.raw() method. If this still does'nt help you can try inspecting the queries to find the real issues.

For your second edit, just more the r to the string definition :

regexString = r'du\w{0,5}'
Ngram.objects.filter(ngram__regex=regexString)

Upvotes: 1

Related Questions