Django db.connection.cursor.fetchone() returns emty result, when raw SQL doesn't

Question

I have method in a cusom QuerySet that performs raw SQL query to database.

class QuerySet(models.query.QuerySet):
    def get_short(self, language_code='en'):
        """Returns shortest name for given language"""
        cursor = connection.cursor()
        cursor.execute('''SELECT t.name FROM translation t, name n
                            WHERE n.id IN (%s)
                            AND t.link_id = n.id
                            AND t.lang_id = %s
                            ORDER BY CHAR_LENGTH(t.name)
                            LIMIT 1''', [','.join(["'%s'" % obj.pk for obj in self]), get_language(language_code).pk])
        name = cursor.fetchone()
        if name:
            return name[0]

But it returns empty result. cursor.fetchone() return None instead of value. When I execute raw SQL on the same database:

SELECT t.name FROM translation t, name n
                              WHERE n.id IN ('166','167')
                              AND t.link_id = n.id
                              AND t.lang_id = 40
                              ORDER BY  CHAR_LENGTH(t.name)
                              LIMIT 1

It returns properly value.

Where I wrong? Please, help!

I'd let django print the exact sql statement before executing it, so that you can compare it to your explicit statement. — Johannes Charra
– Johannes Charra, Commented Sep 14, 2011 at 10:51
SQL statement from django logs is exactly similiar to that I explained in post. — J. Random Geek
– J. Random Geek, Commented Sep 14, 2011 at 11:24

mouad · Accepted Answer · 2011-09-14 10:54:56Z

3

Try without the ", ".join to format your first parameter.

cursor.execute('''SELECT t.name FROM translation t, name n
                            WHERE n.id IN %s
                            AND t.link_id = n.id
                            AND t.lang_id = %s
                            ORDER BY CHAR_LENGTH(t.name)
                            LIMIT 1''', [tuple(obj.pk for obj in self), get_language(language_code).pk])

answered Sep 14, 2011 at 10:54

mouad

70.5k18 gold badges117 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

Django db.connection.cursor.fetchone() returns emty result, when raw SQL doesn't

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related