How see raw SQL code produced by django?

[Thomas Nyberg]

Hello,

I have a question about regexes and sqlite3. I am executing a certain
line of code:

>>> regex = 'NET | INCOME'
>>> Table.objects.filter(name__iregex=regex).

When I have it print out the SQL sent, I get the following (this is a
simplified, but equivalent version):

SELECT *
FROM "table"
WHERE "table"."name"
REGEXP '(?i)' || 'NET | INCOME'
LIMIT 21

However if I execute that directly against the sqlite database i get the
following error:

sqlite3.OperationalError: no such function: REGEXP

I thought that maybe this magic was handled in python's sqlite3 module,
but if i do it there i get the same error. So basically my question is,
can anyone tell me where this is transformed into regular sql? it
obviously happens somehwere (I get no error when running in django after
all), but I can't for the life of me figure out where.

Thanks a lot!

Thomas

PS: I have tried using the shell_plus --print-sql extension, but that
shows me the same thing.

PPS: Obviously not django-related, but if anyone knows how to get a
sqlite database to log all queries issued against it, that would also
obviously solve this issue.

[Tim Graham]

Here's where Django defines REGEXP: https://github.com/django/django/blob/2f10216f84b55920de25422842a66260219e393f/django/db/backends/sqlite3/base.py#L173

From http://stackoverflow.com/a/8244670:

SQLite does not contain regular expression functionality by default.

It defines a REGEXP operator, but this will fail with an error message unless you or your framework define a user function called regexp().

You can execute raw SQL on a Django connection using the technique documented at https://docs.djangoproject.com/en/stable/topics/db/sql/#connections-and-cursors.

[Thomas Nyberg]

On 02/13/2017 05:24 PM, Tim Graham wrote:
>
>
> Here's where Django defines REGEXP:
> https://github.com/django/django/blob/2f10216f84b55920de25422842a66260219e393f/django/db/backends/sqlite3/base.py#L173
>
> From http://stackoverflow.com/a/8244670:
>
> SQLite does not contain regular expression functionality by default.
>
> It defines a REGEXP operator, but this will fail with an error message
> unless you or your framework define a user function

> <http://www.sqlite.org/c3ref/create_function.html> called regexp().

>
> You can execute raw SQL on a Django connection using the technique
> documented at
> https://docs.djangoproject.com/en/stable/topics/db/sql/#connections-and-cursors.
>

Excellent thanks so much this clarifies a lot and is great info!

Cheers,
Thomas