Take string user input from Python to SQL query

Question

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)
    print('\n')
    row = cursor.fetchall()
    if not row:
        raise ValueError('Cannot find a book called ' + book_name)
    else:
        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.

Its probably raising the error as you have the bind variable in the middle of the string and need to use string concatenation and have the bind variable separate from the string like this: `WHERE b.name LIKE '%' || :b_name || '%'`. However, I can't test it. — MT0, Apr 21 '21 at 12:13
This is the right hint @MT0. I tested it posted as an answer, I a hope you don't mind. The OP is probably still confused by some other stuff. — Marmite Bomber, Apr 22 '21 at 19:52

score 1 · Accepted Answer · answered Apr 22 '21 at 19:47

As stated here the error ORA-01036 is issued (also in case) if you pass a bind variable to a statement that is never used.

This is exact you case as your predicate LIKE '%:b_name%' is a simple string compare (the variable name in a quoted string is ignored).

You must perform a concatenation of the bind variable with the percent characters so it works:

like '%' || :b_name || '%'

The following Python script that uses first the right solution and that fails with your approach illustrated the issue

book_name = 'A'

sql = """
select b.name, b.firstname|| ' ' || b.lastname 
from books b 
where b.name like '%' || :b_name || '%'"""

cursor.execute(sql, b_name = book_name )

row = cursor.fetchall()

print(row)


sql2 = """select b.name, b.firstname|| ' ' || b.lastname
from books b 
where b.name like '%:b_name%'
"""

cursor.execute(sql2, b_name = book_name )

this is the output

[('A', 'B C')]
Traceback (most recent call last):
  File "test_like.py", line 38, in <module>
    cursor.execute(sql2, b_name = book_name )
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

this is the test setup

create table books (
id int,
name varchar2(10),
firstname varchar2(10),
lastname varchar2(10));

insert into books(id,name,firstname,lastname) values (1,'A','B','C');
commit;

score -1 · Answer 2 · answered Apr 21 '21 at 12:18

-1

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'])

answered Apr 21 '21 at 12:18

James.guo

1

Take string user input from Python to SQL query

2 Answers2