Django OperationalError near "?": Syntax error

[Adama Camara]

I am running some test, but I keep getting this error:

self = <django.db.backends.sqlite3.base.SQLiteCursorWrapper object at 0x7f619aa32d38>
query = '        SELECT cust.id, cust.name, inv.currency_id, SUM(inv.total)\n        FROM\n          v3_customer as cust\n    ...        and inv.invoice_date < ?\n        GROUP BY cust.id, inv.currency_id\n        ORDER BY cust.id, inv.currency_id'
params = [(1, 2, 3, 4), '2016-12-08']

    def execute(self, query, params=None):
        if params is None:
            return Database.Cursor.execute(self, query)
        query = self.convert_query(query)
>       return Database.Cursor.execute(self, query, params)
E       django.db.utils.OperationalError: near "?": syntax error

../../../environments/tracerenv/lib/python3.4/site-packages/django/db/backends/sqlite3/base.py:337: OperationalError

When looking at the SQL statements and trying to find the "?" symbol it does not appear:

PRE_INV_Q = """\
        SELECT cust.id, cust.name, inv.currency_id, SUM(inv.total)
        FROM
          v3_customer as cust
          JOIN v3_customerproxy ON cust.id = v3_customerproxy.original_id
          JOIN v3_invoice as inv ON v3_customerproxy.id = inv.customer_id
        WHERE
          cust.id IN %s
          and inv.type = 'i'
          and inv.invoice_date < %s
        GROUP BY cust.id, inv.currency_id
        ORDER BY cust.id, inv.currency_id"""

Could there be some incompatibilities between Postgre and SQLite?

[Tim Graham]

Please give the models and queryset to reproduce.

[Adama Camara]

Replying to Tim Graham:

Please give the models and queryset to reproduce.

The Query:

PRE_INV_Q = """\
        SELECT cust.id, cust.name, inv.currency_id, SUM(inv.total)
        FROM
          v3_customer as cust
          JOIN v3_customerproxy ON cust.id = v3_customerproxy.original_id
          JOIN v3_invoice as inv ON v3_customerproxy.id = inv.customer_id
        WHERE
          cust.id IN %s
          and inv.type = 'i'
          and inv.invoice_date < %s
        GROUP BY cust.id, inv.currency_id
        ORDER BY cust.id, inv.currency_id"""
    POST_INV_Q = """\
        SELECT cust.id, cust.name, inv.currency_id, SUM(inv.total)
        FROM
          v3_customer as cust
          JOIN v3_customerproxy ON cust.id = v3_customerproxy.original_id
          JOIN v3_invoice as inv ON v3_customerproxy.id = inv.customer_id
        WHERE
          cust.id IN %s
          and inv.type = 'i'
          and inv.invoice_date >= %s
          and inv.invoice_date <= %s
        GROUP BY cust.id, inv.currency_id
        ORDER BY cust.id, inv.currency_id"""
    PRE_PAY_Q = """\
        SELECT cust.id, cust.name, pay.currency_id, SUM(pay.amount)
        FROM
          v3_customer as cust
          JOIN v3_payment as pay ON cust.id = pay.customer_id
        WHERE
          cust.id IN %s
          and pay.date < %s
        GROUP BY cust.id, pay.currency_id
        ORDER BY cust.id, pay.currency_id"""
    POST_PAY_Q = """\
        SELECT cust.id, cust.name, pay.currency_id, SUM(pay.amount)
        FROM
          v3_customer as cust
          JOIN v3_payment as pay ON cust.id = pay.customer_id
        WHERE
          cust.id IN %s
          and pay.date >= %s
          and pay.date <= %s
        GROUP BY cust.id, pay.currency_id
        ORDER BY cust.id, pay.currency_id"""

The model class:

​https://github.com/Adama2701/Response/blob/master/models.py

[Tim Graham]

Please try to put together a more minimal example that reproduces the issue. A two thousand line models file isn't so helpful. When I asked for the queryset, I expected something like Model.objects.filter(...) which is generating a broken query. It's not clear from what you provided so far how to reproduce the issue. Are you executing raw SQL instead of using a queryset?

[Adama Camara]

Replying to Tim Graham:

Please try to put together a more minimal example that reproduces the issue. A two thousand line models file isn't so helpful. When I asked for the queryset, I expected something like Model.objects.filter(...) which is generating a broken query. It's not clear from what you provided so far how to reproduce the issue. Are you executing raw SQL instead of using a queryset?

Yes we are using RAW SQL but it works. It is only when running pytest that it wont work.
Example of the query:

PRE_INV_Q = """\
        SELECT cust.id, cust.name, inv.currency_id, SUM(inv.total)
        FROM
          v3_customer as cust
          JOIN v3_customerproxy ON cust.id = v3_customerproxy.original_id
          JOIN v3_invoice as inv ON v3_customerproxy.id = inv.customer_id
        WHERE
          cust.id IN %s
          and inv.type = 'i'
          and inv.invoice_date < %s
        GROUP BY cust.id, inv.currency_id
        ORDER BY cust.id, inv.currency_id"""

It does not function when running it in the test, but it function perfect when running it normal

[Tim Graham]

Without some minimal code that I can download and run, I cannot tell if Django is at fault or if there's some problem in your application. The latter seems more likely as you're generating invalid raw SQL as far as I see. Can you tell us why you think Django is at fault and provide a minimal project that reproduces the problem?

If you're writing raw SQL that's compatible with PostgreSQL but not SQLite, there's nothing Django can do about that.

[Adama Camara]

Replying to Tim Graham:

Without some minimal code that I can download and run, I cannot tell if Django is at fault or if there's some problem in your application. The latter seems more likely as you're generating invalid raw SQL as far as I see. Can you tell us why you think Django is at fault and provide a minimal project that reproduces the problem?

If you're writing raw SQL that's compatible with PostgreSQL but not SQLite, there's nothing Django can do about that.

This problems only happens when running the tests. When we run the code normally it works perfect.

The only thing I am trying to do is to use admin_client_get(url) so i can see the html code of the page. Nothing else.

When using admin_client.post() the code redirect to the correct page. But the content on the page is complete blank - so I am trying to use the get method of the page and it is here I get the operational error

[Tim Graham]

Feel free to reopen the ticket if you can provide a minimal project that reproduces the error. Absent that, there's no way to make any progress on this issue. You might be better off using our support channels until you can confirm a bug in Django.