Django .raw query fails (error 1064) -- but works in SQL

Question

(using django 1.11.2, python 2.7.10, mysql 5.7.18)

The following SQL query:

SELECT
    transaction_transaction.id,
    sec_to_time(avg(time_to_sec(extract(HOUR_SECOND from transaction_transaction.transaction_datetime))))
    AS average_time_of_day
FROM transaction_transaction
INNER JOIN store_store ON (transaction_transaction.store_id=store_store.id)
INNER JOIN payment_method_card ON (transaction_transaction.card_id=payment_method_card.id)
WHERE (
    transaction_transaction.transaction_datetime BETWEEN '2017-08-31 00:00:00' AND '2017-08-31 23:59:59'
    AND store_store.company_id=2
    AND payment_method_card.profile_id=8
);

Runs and returns the following result (as expected):

+== id ==+== average_time_of_day ==+
|= 9422 =|===== 20:42:22.8695 =====|

(This is run from HeidiSQL)

Doing something similar (I think! but something is obviously wrong) via Django:

average_time_of_day = Transaction.objects.raw(
    '''
    SELECT 
        transaction_transaction.id, 
        sec_to_time(avg(time_to_sec(extract(HOUR_SECOND from transaction_transaction.transaction_datetime)))) 
        AS average_time_of_day
    FROM transaction_transaction
    INNER JOIN store_store ON (transaction_transaction.store_id=store_store.id) 
    %s
    WHERE (
        transaction_transaction.transaction_datetime BETWEEN %s AND %s 
        AND store_store.company_id=%s
        %s
    );
    ''',
    [
        'INNER JOIN payment_method_card ON (transaction_transaction.card_id=payment_method_card.id) ' if profile_pk else '',
        start_datetime,
        end_datetime,
        company_pk,
        'AND payment_method_card.profile_id=' + str(profile_pk) if profile_pk else '',
    ]
)

Doing

print average_time_of_day.query

Outputs:

SELECT
    transaction_transaction.id,
    sec_to_time(avg(time_to_sec(extract(HOUR_SECOND from transaction_transaction.transaction_datetime))))
    AS average_time_of_day
FROM transaction_transaction
INNER JOIN store_store ON (transaction_transaction.store_id=store_store.id)
INNER JOIN payment_method_card ON (transaction_transaction.card_id=payment_method_card.id)
WHERE (
    transaction_transaction.transaction_datetime BETWEEN 2017-08-31 00:00:00 AND 2017-08-31 00:00:00
    AND store_store.company_id=2
    AND payment_method_card.profile_id=8
);

And Django returns with the following error:

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''INNER JOIN payment_method_card ON (transaction_transaction.card_id=payment_meth' at line 7")

Any idea what I am doing wrong?

Answer 1

Right.

This one will teach me not to try and:

1. be too clever
2. use one-liners too much :-(

This was not working:

average_time_of_day = Transaction.objects.raw(
    '''
    SELECT 
        transaction_transaction.id, 
        sec_to_time(avg(time_to_sec(extract(HOUR_SECOND from transaction_transaction.transaction_datetime)))) 
        AS average_time_of_day
    FROM transaction_transaction
    INNER JOIN store_store ON (transaction_transaction.store_id=store_store.id) 
    %s
    WHERE (
        transaction_transaction.transaction_datetime BETWEEN %s AND %s 
        AND store_store.company_id=%s
        %s
    );
    ''',
    [
        'INNER JOIN payment_method_card ON (transaction_transaction.card_id=payment_method_card.id) ' if profile_pk else '',
        start_datetime,
        end_datetime,
        company_pk,
        'AND payment_method_card.profile_id=' + str(profile_pk) if profile_pk else ''
    ]
)

Notice the 'INNER JOIN payment_method_card ON (transaction_transaction.card_id=payment_method_card.id) ' if profile_pk else '' and 'AND payment_method_card.profile_id=' + str(profile_pk) if profile_pk else '' in the params sent to the raw() method. Kind of dynamic?

... But this works:

if profile_pk:
    average_time_of_day = Transaction.objects.raw(
        '''
        SELECT
            transaction_transaction.id,
            sec_to_time(avg(time_to_sec(extract(HOUR_SECOND from transaction_transaction.transaction_datetime))))
            AS average_time_of_day
        FROM transaction_transaction
        INNER JOIN store_store ON (transaction_transaction.store_id=store_store.id)
        INNER JOIN payment_method_card ON (transaction_transaction.card_id=payment_method_card.id)
        WHERE (
            transaction_transaction.transaction_datetime BETWEEN %s AND %s
            AND store_store.company_id=%s
            AND payment_method_card.profile_id=%s
        );
        ''',
        [
            start_datetime,
            end_datetime,
            company_pk,
            profile_pk
        ]
    )
else:
    average_time_of_day = Transaction.objects.raw(
        '''
        SELECT
            transaction_transaction.id,
            sec_to_time(avg(time_to_sec(extract(HOUR_SECOND from transaction_transaction.transaction_datetime))))
            AS average_time_of_day
        FROM transaction_transaction
        INNER JOIN store_store ON (transaction_transaction.store_id=store_store.id)
        WHERE (
            transaction_transaction.transaction_datetime BETWEEN %s AND %s
            AND store_store.company_id=%s
        );
        ''',
        [
            start_datetime,
            end_datetime,
            company_pk
        ]
    )

So could Django not like at all the dynamic building of params? I'd be very glad to know the answer... I still suspect I did something wrong, though ... Could this have anything to do with SQL injection protection?

Apparently Django was wrapping the INNER JOIN expressions in single quotes - hence causing MySQL to reject the query. Which actually looked like this, even though print average_time_of_day.query was not saying anything about it:

SELECT
    transaction_transaction.id,
    sec_to_time(avg(time_to_sec(extract(HOUR_SECOND from transaction_transaction.transaction_datetime))))
    AS average_time_of_day
FROM transaction_transaction
INNER JOIN store_store ON (transaction_transaction.store_id=store_store.id)
'INNER JOIN payment_method_card ON (transaction_transaction.card_id=payment_method_card.id)'
WHERE (
    transaction_transaction.transaction_datetime BETWEEN 2017-08-31 00:00:00 AND 2017-08-31 23:59:59
    AND store_store.company_id=2
    'AND payment_method_card.profile_id=8'
);

Answer 2

Have you tried using Django's ORM to build that query? It could make debugging it a lot easier. It might look something like this:

Transaction.objects.filter(
    store__company_id=company_pk,
    payment_method_card__profile_id=profile_pk,
    transaction_datetime__range(start_datetime, end_datetime)
)

You can optimize the query using Django's select_related and prefetch_related on your query, and you can use a Q() object if you need finer-grained control.

I've got no love for writing out SQL statements manually, so this is a bit of a cop-out, but I like using the tools that Django gives me.

Answer 3

In SQL string some variable must be string. for example datetime must be in "" or '' i can't remember but queryset.query show you formated sql string not valid string. So just try to add ''.