Django db raw MySQL execute

Question

I have a model and custom manager

model

class VideoDescription(models.Model):
    title_eng = models.CharField(max_length=120, unique=True)
    title_ru = models.CharField(max_length=120, unique=True)
    slug = models.SlugField(max_length=200, unique=True, blank=True)
    rating = models.IntegerField(default=0)
    pub_date_start = models.DateField()
    poster = models.ImageField(upload_to=get_poster_path)
    genre = models.CharField(validators=[validate_comma_separated_integer_list], max_length=10, default=0)
    description = models.TextField(blank=True)

objects = VideoDescriptionManager()

class VideoDescriptionManager(models.Manager):
def get_video_by_genre(self, genre):
    from django.db import connection
    with connection.cursor() as cursor:
        cursor.execute('''select m.id, m.title_eng, m.title_ru, m.slug, m.rating, m.pub_date_start,
        m.poster, m.genre, m.description, COUNT(*) from main_app_videodescription m
                        where genre like "%%%s%%";''', [genre])
        result_list = []
        for row in cursor.fetchall():
            p = self.model(id=row[0], title_eng=row[1], title_ru=row[2], slug=row[3], rating=row[4],
                           pub_date_start=row[5], poster=row[6], genre=row[7], description=row[8])
            p.num_responses = row[9]
            result_list.append(p)
        return result_list

And i've got a error:

django.db.utils.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

Google talking me change [genre] to (genre,) in a raw sql query but it doesn't help. What can i do to fix it?

Why are you using raw sql here? You can use contains for the filter on genre. I don't understand why you have a COUNT(*) when you have no join or groupby. — Alasdair
– Alasdair, Commented Mar 21, 2017 at 17:16
@Alasdair Because i don't know about contains. Thank you very much. — Max
– Max, Commented Mar 21, 2017 at 17:38

Daniel Roseman · Accepted Answer · 2017-03-21 17:09:26Z

2

sqlite does not use %s for parameters, it uses ?.

But either with sqlite or another db, you can't use the parameter substitution to insert a param in the middle of a string. You need to build up the string separately, and then insert it using the parameters. So:

param = "%{}%".format(genre)
cursor.execute('''select m.id, m.title_eng, m.title_ru, m.slug, m.rating, m.pub_date_start,
                  m.poster, m.genre, m.description, COUNT(*) from main_app_videodescription m
                        where genre like ?;''', [param])

Note that using .format removes the need to double-escape the percents.

answered Mar 21, 2017 at 17:09

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

2 Comments

Alasdair Over a year ago

It's a Django cursor -- I thought it used %s for placeholders even if you are using sqlite.

Max Over a year ago

Now i've got an error TypeError: not all arguments converted during string formatting

Collectives™ on Stack Overflow

Django db raw MySQL execute

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related