The result was empty after raw SQL queries in django but right with same sql in mysql db?

Question

I'm using Django to run raw query, but I just got empty result all time as I can use the generated sql by django in mysql db to get the right result .

raw query as:

            SmbFbCampaignStatDaily.objects.raw
               (
                """SELECT 
                    async.id AS id,
                    ...
                    async.name AS NAME,
                    async.status AS STATUS,
                    ...
                    async.rule_created AS rule_created,

                    FROM `smb_fb_campaign_async` async 

                    LEFT JOIN 

                    (SELECT 

                    fb_campaign_id,
                    SUM(impressions) AS impressions,
                    SUM(clicks) AS clicks
                    ...

                    FROM `smb_fb_campaign_stat_daily` WHERE  dt BETWEEN %s AND %s  
                    GROUP BY  fb_campaign_id) statistic 

                    ON statistic.fb_campaign_id = async.fb_campaign_id

                    WHERE  async.fb_account_id = %s

                    AND (async.fb_campaign_id LIKE '%%%s%%' OR async.name LIKE "%%%s%%")

                    ORDER BY %s %s""", (start_date, stop_date, account_id, search_field, search_field, order_field, order)
            )

And the generated sql like below :

# exe_ret = SmbFbCampaignStatDaily.objects.raw('''sql'')
# print(exe_ret)

sql:


                SELECT
                async.id AS id,
               ...
                async.name AS NAME,
                async.status AS STATUS,
                async.daily_budget AS daily_budget,
                statistic.spend AS spend,
                statistic.clicks AS clicks,
                statistic.impressions AS impressions,
                statistic.spend AS spend,
               ...

                FROM `smb_fb_campaign_async` async

                LEFT JOIN

                (SELECT

                fb_campaign_id,
                SUM(impressions) AS impressions,
                SUM(clicks) AS clicks,
                ...

                FROM `smb_fb_campaign_stat_daily` WHERE  dt BETWEEN "2019-04-29" AND "2019-04-29"  GROUP BY  fb_campaign_id) statistic

                ON statistic.fb_campaign_id = async.fb_campaign_id

                WHERE  async.fb_account_id = "113743809520028"

                AND (async.fb_campaign_id LIKE '%%' OR async.name LIKE "%%")

                ORDER BY id asc

I can got the right result by using the genrated sql in mysql db, but the same time my rawQueryset was always empty.

print(len(exe_ret ))
# 0

How can I make it work correctly?

Any commentary is very welcome. Great thanks.

Daniel Roseman · Accepted Answer · 2019-04-30 10:25:49Z

1

You can't use parameter substitution inside an existing string like that. You will need to preprocess your variables. For example:

like_search_field = '%{}%'.format(search_field)

SmbFbCampaignStatDaily.objects.raw
   ("""...
    AND (async.fb_campaign_id LIKE %s OR async.name LIKE %s)

    ORDER BY %s %s""", (start_date, stop_date, account_id, like_search_field, like_search_field, order_field, order)
)

answered Apr 30, 2019 at 10:25

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jia Jimmy Over a year ago

But I'm confusing why the correct sql generated, but failed to get right result?

Daniel Roseman Over a year ago

It wasn't the correct SQL that was generated, it had "(async.fb_campaign_id LIKE '%%' OR async.name LIKE "%%")" without any values.

chirag soni · Accepted Answer · 2019-04-30 10:03:27Z

0

to print the raw query use this: Let say I have Product model

q=Product.objects.raw("select.....")
print(q.query)

answered Apr 30, 2019 at 10:03

chirag soni

1,04512 silver badges21 bronze badges

Collectives™ on Stack Overflow

The result was empty after raw SQL queries in django but right with same sql in mysql db?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related