CODEWITHSUNDEEP
pythonsqlite
iilla
iilla

Reputation: 105

Create function with sqlite OperationalError: no such column

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?

Upvotes: 1

Views: 139

Answers (2)

ashwani
ashwani

Reputation: 702

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,))
    ...

Upvotes: 2

PRMoureu
PRMoureu

Reputation: 13317

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.

Upvotes: 1

Related Questions