Python-SQL queries with variables

Question

I am having problems when I run a query to return the information of the students, I get this error:

File "I:\A2\SQLite\Create Table.py", line 140, in searchStudent cur.execute("SELECT Student_ID, First_Name, Last_Name FROM School WHERE First_Name IS "+firstName) sqlite3.OperationalError: no such column: Joshua

Here is the code, Joshua was the input for the name.

firstName = input("\nWhat is the first name of the student you would like to search for?")
con = sqlite3.connect("School.sql")
cur = con.cursor()
cur.execute("SELECT Student_ID, First_Name, Last_Name FROM School WHERE First_Name IS "+firstName)
print(cur.fetchall())

The database has 30 entries, holding the Student_ID (primary key), First_Name (text), Last_Name (text), Year (integer), Form_Group(text) and House(text).

The query should return the details of all students with the same first name. This should include their Student_ID, First_Name and Last_Name.

What is causing the error?

Edit: I tried:

WHERE First_Name = "+firstName

But that brought the same error.

• Mihai: WHERE First_Name ="+firstName insted of IS
• user5387789: I tried, but it still returns the same error :(

Answer 1

String literals in SQL need to be surrounded with single quotes ('), otherwise the database interprets them as object names - and as you've seen, fails when it can't find the column.

cur.execute("SELECT Student_ID, First_Name, Last_Name FROM School WHERE First_Name = '" + firstName + "'")

However, using string concatination like this is a bad practice, and leaves your application open to SQL-injection attacks. You would be advised to use a prepared statement instead:

cur.execute("SELECT Student_ID, First_Name, Last_Name FROM School WHERE First_Name = ?", firstName)

Also, note that comparing values in SQL is done by the = operator, not the is operator.