Take string user input from Python to SQL query

I'm trying to write a function to run some oracle SQL queries on Python, this function should take one user input as the name of a book, and the SQL query should search for the book in the database and return details of the author and the genre. If nothing is found then the function should also return an error saying that the book cannot be found.

The following SQL query works fine in SQL developer but didn't work well in Python. When I run the code:

book_name = input("Enter the name of book to retrive: ")
    cursor.execute("""SELECT b.name, g.name, a.firstname|| ' ' ||a.lastname 
                      FROM books b 
                      INNER JOIN genres g ON b.genre_id = g.genre_id
                      INNER JOIN authors a ON a.author_id = b.author_id
                      WHERE b.name LIKE '%:b_name%'""", b_name = book_name)
    row = cursor.fetchall()
    if not row:
        raise ValueError('Cannot find a book called ' + book_name)
        for b_name, g_name, a_name in row:
            print("The book", b_name, "is found in", g_name, "genre, written by", a_name)

I got this error: cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

I've been researching and knew there might be something wrong with my bind variable, but I don't know how to fix it. Can anyone please help out? Thanks in advance.

1 answer

  • answered 2021-04-21 12:18 James.guo

    When you try to link sql, set cursors.DictCursor

    rows = cursor.fetchall()
    for row in rows:
      print("The book", row['b_name'], "is found in", row['g_name'], "genre, written by", row['a_name'])