Django raw query returns no rows, when SQL does

Question

I am about to smash my head against the wall.

Consider the following piece of code:

def get_cardnumbers_from_timestamp(request, since_time=0):
    if request.method == 'GET':
        if request.user:
            if not request.user.is_authenticated():
                logger.debug('User is not authorized')
                return auth_required('Authentication required')

        logger.debug('User has been authorized')
        user = request.user
        logger.debug('since_time: {0}'.format(since_time))
        if int(since_time) != 0:
            utc_time = time.mktime(time.gmtime(int(since_time)))
        else:
            utc_time = 0

        logger.debug('since_time UTC: {0}'.format(utc_time))

        devices_data = dict()
        from django.db import connection

        cursor = connection.cursor()
        query = 'SELECT c.name,c.id,d.locale,d.card_number,d.dev_uniqid,d.id FROM device_channel dc JOIN channels c on dc.channel_id = c.id JOIN devices d on dc.device_id=d.id WHERE d.card_number IS NOT NULL AND updated >= FROM_UNIXTIME({time}) ORDER BY d.id'.format(time=int(utc_time))
        logger.debug(query)
        cursor.execute(query)
        answers = cursor.fetchall()
        cursor.close()
        logger.debug(answers)

Formed query, when fired against MySQL with since_time either explicitly set to 0 or not set at all returns about 250 rows, so does Django.

However, when I set the timestamp to a certain value, doesn't matter which one, same formed query (which I log and then copy-paste from the log) returns, say, 5 rows in MySQL console. Django gives me a result set of whopping 0.

I might be overlooking something very obvious, but I've already tried numerous approaches at this, to no avail.

Any ideas would be most welcome.

@dhana: That's exactly what my question states - MySQL gives me results, Django doesn't :) — favoretti
– favoretti, Commented Jun 17, 2014 at 15:39
Im having the same issue :(. my query is working in SQL gui but exact same query not working in django raw query — Sagar Shah
– Sagar Shah, Commented Apr 18, 2017 at 11:19

Serafeim · Accepted Answer · 2014-06-17 15:32:27Z

3

You should not pass parameters to django raw queries with string format but instead pass them to execute (also check the docs). For instance, in your case do this:

query = """
 SELECT c.name,c.id,d.locale,d.card_number,d.dev_uniqid,d.id 
 FROM device_channel dc JOIN channels c on dc.channel_id = c.id 
 JOIN devices d on dc.device_id=d.id 
 WHERE d.card_number IS NOT NULL AND updated >= FROM_UNIXTIME(%s) 
 ORDER BY d.id
"""
cursor.execute(query, [int(utc_time)])

answered Jun 17, 2014 at 15:32

Serafeim

15.1k15 gold badges96 silver badges136 bronze badges

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

2 Comments

favoretti Over a year ago

Tried that as well. Same effect. No results :(

favoretti Over a year ago

Hmm, although.. I probably was a bit off the rails the time I tried. I just re-tried your approach now and it does work.. Thanks man!

Maurice Elagu · Accepted Answer · 2015-07-03 07:50:51Z

0

By default, the Python DB API will return results without their field names, which means you end up with a list of values, rather than a dict. At a small performance cost, you can return results as a dict by using something like this:

def dictfetchall(cursor):
    "Returns all rows from a cursor as a dict"
    desc = cursor.description
    return [
        dict(zip([col[0] for col in desc], row))
        for row in cursor.fetchall()
    ]

answered Jul 3, 2015 at 7:50

Maurice Elagu

7121 gold badge10 silver badges20 bronze badges

Collectives™ on Stack Overflow

Django raw query returns no rows, when SQL does

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