Create function with sqlite OperationalError: no such column

Question

I want to create a function which I could input the genre of movies and the number of top rows I want to see.

I have already built the database with sqlite. But I don't know how to use the function input in sqlite.

My code:


def get_top_genre_actors(genre, k):

    c.execute('''SELECT ma.actor, m.rating FROM movie_actor ma
    LEFT JOIN movie m ON ma.imdb_id = m.imdb_id
    ORDER BY m.rating DESC, ma.actor
    LIMIT int(k)
    ''') 
    conn.commit()
    get_top = c.fetchall()

    print("Top 10 actors who played in highest rated ", genre, " movies:")
    print("Actor, Average ", genre, " movie rating")
    for x in get_top:
        print(x[0],', ', x[1],sep="")

get_top_genre_actors("Drama", 5)

The result I got:

OperationalError: no such column: k

I know the wrong line is

LIMIT int(k)

How to make it right?

ashwani · Accepted Answer · 2019-09-15 18:15:16Z

2

You should use placeholders.

def get_top_genre_actors(genre, k):
    c.execute('''SELECT ma.actor, m.rating FROM movie_actor ma
    LEFT JOIN movie m ON ma.imdb_id = m.imdb_id
    ORDER BY m.rating DESC, ma.actor
    LIMIT ?
    ''', (k,))
    ...

edited Sep 15, 2019 at 18:15

answered Sep 15, 2019 at 17:49

ashwani

7024 silver badges16 bronze badges

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

Comments

PRMoureu · Accepted Answer · 2019-09-15 18:00:37Z

1

The more recommended way is to use a parameterized query :

c.execute('''SELECT ma.actor, m.rating FROM movie_actor ma
    LEFT JOIN movie m ON ma.imdb_id = m.imdb_id
    ORDER BY m.rating DESC, ma.actor
    LIMIT ?
    ''', (k,))

This allows more security than directly formatting the values inside the query.

Note that the second parameter needs to be an iterable, that's why we use the notation for a tuple with one member (k,). It could also work with a list.

BTW, you don't need the line conn.commit() after a SELECT expression.

edited Sep 15, 2019 at 18:00

answered Sep 15, 2019 at 17:47

PRMoureu

13.4k6 gold badges46 silver badges52 bronze badges

Collectives™ on Stack Overflow

Create function with sqlite OperationalError: no such column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related